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

G

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.
 
C

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

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.
 
G

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
 
H

Harlan Grove

Guntars said:
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.
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top