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

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

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

Show us the formula you are using, please
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"misona1" wrote in message
...
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

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

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



"Bernard Liengme" wrote:

Show us the formula you are using, please
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"misona1" wrote in message
...
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

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

"misona1" сообщил/сообщила в новостях следующее: ...


=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"))



=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"))

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

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

This is equivalent:
=IF(B39-C39=0," at estimate",IF(B39-C390,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(B39C39,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.)





misona1 wrote:

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

"Bernard Liengme" wrote:

Show us the formula you are using, please
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"misona1" wrote in message
...
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




--

Dave Peterson

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

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.

st wrote:

"misona1" сообщил/сообщила в новостях следующее: ...

=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"))


=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"))

--

Dave Peterson