How to display $ sign in front of: 50 under, 50 over

M

misona1

So, I have a nested IF formula in a cell and the result is i.e. : 50 over.
I want it to say $50 over.
How?
Thanks
 
M

misona1

This is the formula, although I don't think it has anything to do with the
formula rather with formatting the cell...? Format cell then Number then
custom. But I don't know what to enter as the custom thing.

=IF(SUM(B39)-SUM(C39)=0," at
estimate",IF(SUM(B39)-SUM(C39)>0,(SUM(B39)-SUM(C39))& " over",
ABS(SUM(B39)-SUM(C39))& " under"))

Thanks
 
S

st

misona1 said:
=IF(SUM(B39)-SUM(C39)=0," at
estimate",IF(SUM(B39)-SUM(C39)>0,(SUM(B39)-SUM(C39))& " over",
ABS(SUM(B39)-SUM(C39))& " under"))
Click to expand...

=IF(SUM(B39)-SUM(C39)=0," at
estimate",IF(SUM(B39)-SUM(C39)>0, "$"& (SUM(B39)-SUM(C39))& " over",
"$"& ABS(SUM(B39)-SUM(C39))& " under"))
 
D

Dave Peterson

First, your existing formula doesn't need the =sum() function.

This is equivalent:
=IF(B39-C39=0," at estimate",IF(B39-C39>0,B39-C39& " over",
ABS(B39-C39)& " under"))

In fact, instead of using b39-c39=0, I'd just check to compare b39 with c39:
=IF(B39=C39," at estimate",IF(B39>C39,B39-C39& " over",
ABS(B39-C39)& " under"))

You could use this formula:
=b39-c39
and a custom format of:
General" Over";General" Under";"at estimate";@

The custom format has 4 parts:
positive;negative;zero;text

You may like this custom format slightly better:
General" Over";[Red]General" Under";"at estimate";@

(You'll see the ## Under in Red.)
 
D

Dave Peterson

And I forgot the $'s!

$0.00" Over";[Red]$0.00" Under";"at estimate";@
or
$0.00" Over";$0.00" Under";"at estimate";@

You don't have to use the cents portion, either.
 
Top