Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4,393
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.misc
st st is offline
external usenet poster
 
Posts: 9
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do you make a plus sign appear in front of a number? fairchildbrad Excel Discussion (Misc queries) 3 March 1st 07 01:57 PM
equal sign in front of date in formual bar Ron 130 Excel Worksheet Functions 1 April 5th 06 10:23 PM
How do I find and replace a number with a minus sign in front? D.Sommer Excel Discussion (Misc queries) 1 November 7th 05 11:13 AM
Plus sign in front of a formula marksuza Excel Discussion (Misc queries) 3 October 20th 05 06:34 PM
How do I show a multiplication sign in front of a number accounting beginner Excel Discussion (Misc queries) 8 March 21st 05 04:59 PM


All times are GMT +1. The time now is 06:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"