SUM formula results returns 0 value

[SDInspector]

RE: 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!

 

[Duke Carey]

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.

 

[Toppers]

Remove the quotes as you want answers as numeric not text?

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

If H36 is formatted as %:

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

or =H36*3200 ? H36 =0%,10%,15%,or 20%?

no IF required


=IF(I36>0,3200-I36,$3200.00)

or

=3200-I36 if I36 is >=0?

 

[SDInspector]

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.

 

[Peo Sjoblom]

No, there is no distinction between a number derived from a formula and a
typed number when it comes to formatting except maybe that the formula
number might have a lot of decimals not visible while the number you type is
what you type.

Note that if you want to do any kind of calculations with these values you
need to convert them to numbers, a

=SUM(Range)

where Range contains text numbers will return zero From a design and
usability point of view it is always better to use real numbers when
possible (more that 15 digits is not possible with numbers)




--
Regards,

Peo Sjoblom

 

[SDInspector]

Thanks: was confused w/ the text "" v. # - want "30%" to show as text, but
result to show as number: applied your kind instructions and now all is well
again here in Mayberry RFD!

 

[SDInspector]

thanks for the help! applied instructions and worked out - got confused where
wanted "30%" entered as text but corresponding result in next column to show
as number. muchas gracias!

 

[Toppers]

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)..