How do I eliminate #NUM! result in a formula? 4

[afbutlerandassoc]

I have a massive spreadsheet where in many cases the formula gives a #NUM! result if there is not a value in each of the referenced fields corresponding to the formula. I don't know how to eliminate them. Please help |-0

 

[ETID]

Try this:

=if(iserror([your formula]),"",[your formula])

 

[Dreamboat]

The following are "error handling" formulas. If you would like a value (like zero), replace the "" with that value. If you would like text, such as Not Found, place the exact text between the ""s.

To hide DIV0#, #NUM, etc.:
=IF(ISERROR(A1*B1),"",A1*B1)

To hide #NA errors:
=IF(ISNA(A1*B1),"",A1*B1)

To return nothing ("&quot when either calculating cell is blank:
=IF(OR(ISBLANK(A1),ISBLANK(B1),"",A1*B1)
techsupportgirl@home.com

http://www.PiersonTech.com

Brainbench MVP for Microsoft Word

 

[afbutlerandassoc]

Thank you - that helps a lot. But I still get the error in a few other formula calculations, such as:

=SMALL(C14:AC14,4)

=INDIRECT(ADDRESS(3,MATCH(SMALL(C14:AC14,4),C14:AC14,0)+2))

=((H4*V4)+(J4*W4)+(L4*X4))

=F4+(F4*T4)+S4

I don't know how to fix these - I must be missing something very simple but what? Thanks, x-)

 

[DaleWatson123321]

Hi Ashok,

UPDATE: I sent you an email with modified formulas to include the ISBLANK and ISERROR functions suggested by Dreamboat and ITID. Thanks to them also.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca