I'm using an "IF" statement to return a blank if a value is less than the
absolute value of 100. Downstream, I'm doing a sum minus some of the cells
that have the IF statement in it. For cells that return a blank, the
downstream formula goes to error. I could set the IF statement to return a
zero, but that messes up my preferred formatting.

I can live with rewriting the IF statement to return a zero, not a blank, if
I can get the standard comma format to show up, but formatting the cell does
not seem to work.

Another way to handle it would be to get the formula to not return an error,
but that is also beyond my skills.

Any help?

Sample formula IF statement from my spreadsheet:

=IF((ABS('P&L Synopsis'!M57)<100),"",+'P&L Synopsis'!M57)

Sample formula that gets error:

='Corp Supp'!S117/1000-AG51-AG53-AG55-AG49

Error returned is #VALUE!

Don't set the value to blank, set it to zero. Then:

Tools Options View
and clear the zero values checkbox

This way its a real zero and can be used in calculations, it just won't be
visible.
--
Gary''s Student
gsnu200708


"Johnboy" wrote:

I'm using an "IF" statement to return a blank if a value is less than the
absolute value of 100. Downstream, I'm doing a sum minus some of the cells
that have the IF statement in it. For cells that return a blank, the
downstream formula goes to error. I could set the IF statement to return a
zero, but that messes up my preferred formatting.

I can live with rewriting the IF statement to return a zero, not a blank, if
I can get the standard comma format to show up, but formatting the cell does
not seem to work.

Another way to handle it would be to get the formula to not return an error,
but that is also beyond my skills.

Any help?

Sample formula IF statement from my spreadsheet:

=IF((ABS('P&L Synopsis'!M57)<100),"",+'P&L Synopsis'!M57)

Sample formula that gets error:

='Corp Supp'!S117/1000-AG51-AG53-AG55-AG49

Error returned is #VALUE!

Thanks Gary's Student. Interestingly enough, I tried that and it didn't
work. I'm using Excel 2003. Is this something fixed in later versions?