#VALUE!

[Frederic_S]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

How do I keep a spreadsheet from showing the "#VALUE!" text when the formula relates to blank cells. I want the formula to be available for when I do fill in the blank cells.

 

[Niek Otten]

=IF(A1="","",YourFormula) where A1 is a source cell

Or:

=IF(ISERROR(YourFormula,"",YourFormula). But then you risk not to detect any
other errors in formulas or data. But it saves you having to test ALL the
source cells.

In Excel2007, the last formula can be written as:

=IFERROR(YourFormula,"")

 

[Frederic_S]

Thanks for the response but I can't get it to work when my formula refers to a cell with an =IF function
=IF($E5<1,"",(($E5*$F5)+$G5+$N5)-($M5*($L5*100))

 

[Carl Witthoft]

You need to nest your IF functions...

=IF(ISNUMBER($E5,IF($E5<1,"",(($E5*$F5)+$G5+$N5)-($M5*($L5*100)),"")
Roughly speaking -- I didn't proof that for the right number of
parentheses.

 

[Frederic_S]

I have tried your suggestion and keep getting the error message "Too many arguments were entered for this function".

Appreciate the help.

Fred S

 

[Bob Greenblatt]

I have tried your suggestion and keep getting the error message "Too many
arguments were entered for this function".

Appreciate the help.

Fred S

Then you did not follow the instructions. Please post the formula you
entered. We can't diagnose your problems when you don't show us what you
did.