LinkBack URL
About LinkBacks
I am using this formula to calculate gain or loss
How can i get a + or - sign to show in my answer.
=IF(B3<B2,B2-B3,IF(B3>B2,B3-B2,""))
example: + $100.00
or - $100.00
Any help is appreciated
Thanks
Joel
I am using this formula to calculate gain or loss
How can i get a + or - sign to show in my answer.
=IF(B3<B2,B2-B3,IF(B3>B2,B3-B2,""))
example: + $100.00
or - $100.00
Any help is appreciated
Thanks
Joel
Last edited by J Glanton; 11-11-2011 at 09:44 AM.
This will show as you wish, it becomes a Text entry though so no good for calculating., but if it's not for further calculating then it's fine
A custom format would probably be the best answer
=IF(B3<B2,"- "&"$"&B2-B3,IF(B3>B2,"+ "&"$"&B3-B2,""))
Windows 7 using Office 2007 & 2010
Remember your [ code ] [ /code ] tags, makes reading soooo much easier
Hi JoeOriginally Posted by J Glanton
if B3 > B2 then B3-B2 is always positive
if B3 < B2 then B2-B3 is always positive
So.. When you need your negative values?
Never use Merged Cells in Excel
Also, for + or - sign you can use this custom format:
+[$$-C09]#,##0.00;-[$$-C09]#,##0.00;[$$-C09]0;@
Scottylad2
Thanks
That did what i asked for but did not allow me to format the cells as curency.
My fault for not mentioning that.
So what i want to do is get a cell to say the word Loss or Gain depending on the formula i am using: =IF(B2>B3,(B2-B3),B3-B2)
I will just place this in the cell next to the information i am getting with the formula im showing above. So the left cell will have the number and the right will tell if its gain or loss.
I have been tring to do this myself with no luck.
Any help with this?
Thanks again for getting involved.
Also thanks Zbor.
Got it
=IF(B4<B3,("loss"),"gain")
now if it would only make sense to me.
You have unnecessary parens:
=IF(B4<B3, "loss", "gain")
=IF(condition to test, value to return if true, value to return if false)
EDIT: And this formula
=IF(B2>B3,(B2-B3),B3-B2)
... could be replaced with
=ABS(B2-B3)
Last edited by shg; 11-11-2011 at 05:20 PM.
Entia non sunt multiplicanda sine necessitate
I will change those.
Thank you very much.
Joel
SHG
I hate to second guess someone who obvousiouly has a multitude of experience.
But when i enter this =IF(B4<B3, "loss", "gain")
I get an error. I get a FALSE in the cell
No real concern. Just tring to learn a little while getting answers.
The formula =IF(B3<B2,("loss"),"gain") works fine.
Thanks Again
You're referencing different cells with those two formulas, but irrespective, that formula should never return FALSE. Can you post a workbook with an example of that?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks