MrExcel Publishing
Your One Stop for Excel Tips & Solutions

IF statement


Posted by Fred Taylor on October 17, 2001 11:44 AM

I would like to display the messages "Profit" or "Loss" Or "Even" after doing a IF sum on one cell, if the cell is greater then 0 then "Profit" if under 0 then "loss" if equal to 0 then "Even" but i can only seem to be able to do the first two. Can anyone please help?
Thanx Fred.


Posted by Mark W. on October 17, 2001 11:47 AM

Why don't you format your cell as... "Profit";"Loss";"Even" (nt)

Posted by Eric on October 17, 2001 11:55 AM

wow! where do I go to find out more about this feature? (NT)

Posted by Eric on October 17, 2001 12:05 PM

Found it in the help file under "custom format"- what a great trick (NT)

Posted by Travis on October 17, 2001 12:12 PM

Awsome trick by Mark, and the formula would be =IF(B1>0,


Posted by Travis on October 17, 2001 12:13 PM

Awsome trick by Mark, and the formula would be =IF(B1>0,"Profit",IF(B1<0,"Loss",IF(B1=0,"Even")))


Posted by Fred Taylor on October 17, 2001 12:21 PM

Re: Awsome trick by Mark, and the formula would be =IF(B1>0,"Profit",IF(B1<0,"Loss",IF(B1=0,"Even")))

=IF(B1>0,"Profit",IF(B1&LT;0,"Loss",IF(B1=0,"Even"))) Still gives me the answer loss when the cell shows 0 what i'm i doing wrong?

Posted by IML on October 17, 2001 12:29 PM

Re: Awsome trick by Mark, and the formula would be =IF(B1>0,"Profit",IF(B1<0,"Loss",IF(B1=0,"Even")))

Its probabaly rounding, modify the formula to
=IF(ROUND(B1,2) > 0,"Profit",IF(ROUND(B1,2) &LT; 0,"Loss","Even"))

Posted by Aladin Akyurek on October 17, 2001 12:32 PM

Re: =IF(B1>0,"Profit",IF(B1<0,"Loss",IF(B1=0,"Even")))

=IF(B1>0,"Profit",IF(B1&LT;0,"Loss","Even"))

would work.

However, try also what Mark suggested. If you go for it and you need to do further computations that use these words, you need to use the values in B1 instead.
For example, if this formula is say in C1 and you have formula somewhere else referring to the value of C1 such as

=IF(C1="Profit",....),

you shoud then use:

=IF(B1>0,...)

Aladin

Posted by Fred Taylor on October 17, 2001 12:33 PM

Re: Awsome trick by Mark, and the formula would be =IF(B1>0,"Profit",IF(B1<0,"Loss",IF(B1=0,"Even")))

THANK YOU, That done the trick...:-)