Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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")) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you make a plus sign appear in front of a number? | Excel Discussion (Misc queries) | |||
equal sign in front of date in formual bar | Excel Worksheet Functions | |||
How do I find and replace a number with a minus sign in front? | Excel Discussion (Misc queries) | |||
Plus sign in front of a formula | Excel Discussion (Misc queries) | |||
How do I show a multiplication sign in front of a number | Excel Discussion (Misc queries) |