Calculation / Format Error

[Jim]

I'm using this formula in cell AE69

=IF(AL69="NO","",AE68*(AM69/100))

When the logic is true I want the cell value to be zero, but show as blank.
When this cell is summed up with others, a #VALUE! is returned. I am
assuming excel is seeing this cell as text and thus returning an error.

I have tried adjusting the formula to:

=IF(AL69="NO","0",AE68*(AM69/100))

I have selected the accounting formating for AE69 with the "£" symbol.
However, instead of getting the £ symbol to the left and a dash representing
zero, "0" is displayed. However this does allow this cell to be summed up
with others.

My preference is to have this cell be blank with a value of zero if the
logical test is true. If it cannot be blank and must show zero, I would like
the correct accounting formating to work.

Suggestions?

 

[T. Valko]

=IF(AL69="NO","",AE68*(AM69/100))

I am assuming excel is seeing this cell as
text and thus returning an error

Yes, that's correct

I have tried adjusting the formula to:
=IF(AL69="NO","0",AE68*(AM69/100))

You have a few options...

You can use the original formula and when summing the results use the SUM
function instead of doing something like this: =A1+A2. When you use the
A1+A2 syntax that's where you'll get the error. The SUM function will ignore
any text which is what a formula blank ("") is.

You can use the 2nd formula but remove the quotes from around the 0:

=IF(AL69="NO",0,AE68*(AM69/100))

You can use the 2nd formula (without the 0 quotes) and format the cell to
not display the 0. The cell will appear to be blank but it will actually
contain a numeric 0 that can't be seen.

Format>Cells>Number tab>Custom
General;General;
OK