How to get rid/hide #NUM! error in Excel 2003?

[Guntars]

Gentlemen,
I created array formula in Excel 2007. It works fine, but after it runs out
of condition matching results it will display #NUM! error. I can hide that
error with new IFERROR function in Excel 2007, but my problem is the document
will be used in Excel 2003, and haven’t found the way how to hide that error.
If I do the IF(ISERROR(FORMILA),â€â€,(FORMILA)) it will mess up my formula
results.
<a href="http://www.eonstone.com/250/Attendee checklist .xls">I attached
portion of the file I am working on. </a>. The error I can’t hide is on
5S_Audit sheet, columns B,C and D.
I need some expert help here, or maybe the formula need to be put together
differently.
Any help and suggestions are highly appreciated.

 

[Conan Kelly]

Guntars,

Create your own UDF to use in XL 2003. Here is a rough example below:



Function IfIsError(pvarResult As Variant, pvarValueIfError As Variant) As
Variant
'Public Function IfIsError(pvarResult As Variant, pvarValueIfError As
Variant) As Variant
'Private Function IfIsError(pvarResult As Variant, pvarValueIfError As
Variant) As Variant

If Application.WorksheetFunction.IsErr(pvarResult) Then
' If Application.WorksheetFunction.IsError(pvarResult) Then
' If Application.WorksheetFunction.IsLogical(pvarResult) Then
' If Application.WorksheetFunction.IsNA(pvarResult) Then
' If Application.WorksheetFunction.IsNonText(pvarResult) Then
' If Application.WorksheetFunction.IsNumber(pvarResult) Then
' If Application.WorksheetFunction.IsText(pvarResult) Then
' If Not IsDate(pvarResult) Then
' If IsArray(pvarResult) Then
' If IsEmpty(pvarResult) Then
' If IsMissing(pvarResult) Then
' If IsNull(pvarResult) Then
IfIsError = pvarValueIfError
Else
IfIsError = pvarResult
End If

End Function



As you can see, I included (but commented out) several different tests so
you can tailor it to your needs. Be sure to use the "NOT" keyword when
necessary, or switch your "If...Then" statement with your "Else"
statement...like I did with "If Not IsDate..."

You would call this in XL by entering a formula like this in a cell:

=IfIsError(A5/0,"Error - Please Fix Something!!!")

HTH,

Conan Kelly

 

[T. Valko]

What do you use with the IFERROR function that would be different from what
you use with IF(ISERROR ?

Post your IFERROR formula so we can see what it does.

 

[Guntars]

This is the array formula which works in Excel 2007:
{=IFERROR(TRANSPOSE(INDEX(Employees!A2:BN4,1,SMALL(IF(Employees!F4:BN4="5S",COLUMN(Employees!F4:BN4)),COLUMN(1:1)))+37),"")}

In Excel 2003 I can get it to work if I remove IFERROR, but then I also get
that #NUM! error in a cells where results not returned. And I need to include
those extra cells because more information will be added later.

Here is that excel sample file example again:
http://www.eonstone.com/250/Attendee checklist .xls

Than you,
Guntars

 

[Harlan Grove]

This is the array formula which works in Excel 2007:

=IFERROR(TRANSPOSE(INDEX(Employees!A2:BN4,1,
SMALL(IF(Employees!F4:BN4="5S",COLUMN(Employees!F4:BN4)),
COLUMN(1:1)))+37),"")

....

I can't see any point to the TRANSPOSE call. The INDEX call inside it
returns a single number, so TRANSPOSE does nothing. Best to remove it.

The #NUM! error would occur when the SMALL call's 2nd argument exceeds
the number of entries in its 1st argument. That could be trapped more
elegantly as

=IF(COUNTIF(Employees!F4:BN4,"5S")>=COLUMN(1:1),INDEX(Employees!
A2:BN4,1,
SMALL(IF(Employees!F4:BN4="5S",COLUMN(Employees!F4:BN4)),COLUMN(1:1)))
+37,"")

which would be exactly the same in Excel 2007 and Excel 2003.
Furthermore, it'd propagate errors from Employees!F4:BN4 (if any),
which is usually a good thing.

 

[Guntars]

Harlan Grove,
I tried your formula and it works great, if results are return in single
ROW. The reason I was using TRANSPOSE function, so I can look up data in ROW,
but results I want to be in COLUMN.
Thank you
Guntars