Test worked fine for me: there is no obvious reason why, if the cell is
formatted as currency, it will not display as currency.

There appears to be some general confusion over data being text or numeric
and I suspect this is the cause (see my previous reply)..

"SDInspector" wrote:

The false result didn't format correctly when entered as 3200. Don't know
why, so entered it as text. Thought it might have something to do w/ how
Excel sees the formula, ie, a result isn't formatted the same as hard cell
data. Logically, 3200 should pick up the accounting / curreny formatting of
cell w/2 decimal places & dollar sign, but it didn't - any ideas why?

Thanks for the isnumber() tip.

"Duke Carey" wrote:

Do you really intend to treat all these values as text rather than as
numbers? Why use "$3200.00" as the FALSE result in the formula below,
instead of 3200 that is formatted the way you want?

=IF(I36"",SUM(3200-I36),"$3200.00")

Excel treats "" as zero *sometimes* but it sure doesn't pay to assume it
does ALL the time.

If you must use "" instead of 0, check it with ISNUMBER() which correctly
returns FALSE, i.e., "" isn't a number.


"SDInspector" wrote:

payment calculations. Have 3 columns. Column A is % completion. Column B
is a formula that calcs a % payment reduction based on % data in A & yields a
corresponding dollar value. Column C is a formula that subtracts B from the
total payment & returns net payment.

Sample - Formula B:

=IF(H36="10%","$320.00",IF(H36="15%","$480.00",IF( H36="20%","$640.00","")))

Sample - Formula C:

=IF(I36"",SUM(3200-I36),"$3200.00")

When C is totaled using SUM function, a zero value is returned when B is "",
rather than totaling the full payments showing in C.

Only by changing "" in formula B to a zero and showing the zero value will
the values in C add up to give sum. If value in B is anything 0 the SUM
calculation works fine. I'm guessing this is a formatting problem in that
Excel doesn't recognize the "" result as "0" and so somehow is using null as
a value, returning a zero result in the SUM of Column C when in actuality the
total is $64K plus. Solution appreciated - or do I have to change formula in
B to show "0" instead of ""?

Why doesn't Excel read the values in C correctly when cells in Column B are
blank - ie, ""?

Thanks!