Negative currency in E5, how to get "loss" text in A5?

[StargateFan]

If =E4-E2 formula in E5 shows a negative number meaning income loss,
how can we have text in A5 say "Income Loss". And it would label cell
as "Income Gain" if the number in E5 is positive? Thx. D

 

[Cimjet]

Hi
Try this,
=IF(E5<0,"Income Lost","IncomeGain") in A5
HTH
John

 

[Cimjet]

Hi again
This one =IF(OR(E5=0,E5=""),"",IF(E5<0,"Income Lost","IncomeGain"))
Will leave the cell blank is E5 is 0 or empty.
HTH
John

 

[StargateFan]

Hi again
This one =IF(OR(E5=0,E5=""),"",IF(E5<0,"Income Lost","IncomeGain"))
Will leave the cell blank is E5 is 0 or empty.
HTH
John

John, thank you!

Just out of curiousity, I was given code a few years back that I use
in case where cell is blank and I substituted it to see what I would
see (also added colon to text and space between Income and Gain and
changed Lost to Loss <g>):

=IF(E5<>"",IF(E5<0,"Income Loss:","Income Gain:"),"")

I didn't keep this because it only handles if blank or not. But it
did point out that I didn't think of what if E5=0 (no change in
salary) where text should become just plain "Income:", I think.

So since I've been having more luck lately in a scripting language
with If statements, tried finding a solution again and looked at more
If examples. I found something that seems to work when I tested it
for E5=0, E5<0, or E5>0 (blank scenario not covered, though):

=IF(E5<0,"Income Loss:",IF(E5>0,"Income Gain:",IF(E5=0,"Income:")))

I think Or is supposed to help with adding an extra If to one of them
(?) so an Or example helped me to get this:

=IF(E5<0,"Income Loss:",IF(E5>0,"Income
Gain:",IF(OR(E5=0,E5="")"Income:")))

which gives error and doesn't work. Don't know if because syntax no
good or something simple, i.e., a bracket being wrong, etc. ...

What do you think will help with adding in something to handle last
case together, If E5=0, E5="" (blank)?

Thanks!

 

[Cimjet]

Hi
You almost had it, just forgot a comma before your last "Income"
=IF(E5<0,"Income Loss:",IF(E5>0,"Income Gain:",IF(OR(E5=0,E5=""),"Income:")))
HTH
John

 

[StargateFan]

Hi
You almost had it, just forgot a comma before your last "Income"

=IF(E5<0,"Income Loss:",IF(E5>0,"Income Gain:",IF(OR(E5=0,E5=""),"Income:")))

Well, darn. The only thing was that it didn't quite work. Tested it
and when E5 is blank I got "Income Gain:" when it should be under
condition E5=0 which should result in just "Income:".

So I got to thinking how sometimes in conditional formatting, the
order in which you put the conditions changes the outcome. And, after
reading tons more about nested if statements this morning (but this
time starting to understand better), the conditions, or "logical
tests" were stumping me on how to switch them around successfully. I
tried various permutations and got stuck every time.

So, for future reference and for others dimwits like me (yes, I'm a
dimwit when it comes to this stuff still ... <g>), I ended up figuring
the trick below that will work for me in future. As long as the
original formula works, no reason why the rearrangements shouldn't.

This:

=IF(E5<0,"Income Loss:",IF(E5>0,"Income
Gain:",IF(OR(E5=0,E5=""),"Income:")))

breaks down to this:

=
IF(E5<0,"Income Loss:",
IF(E5>0,"Income Gain:",
IF(OR(E5=0,E5=""),"Income:"
)))

and then I just cut the last line and put it above the first to get
this (<d'uh> simple when you figure out a trick!):

=
IF(OR(E5=0,E5=""),"Income:",
IF(E5<0,"Income Loss:",
IF(E5>0,"Income Gain:",
)))

Then I put everything back on one line and it seems to give me correct
results in every one of the 4 cases, whether ="" (blank), =0, >0, or,
<0:

=IF(OR(E5=0,E5=""),"Income:",IF(E5<0,"Income Loss:",IF(E5>0,"Income
Gain:",)))

Awesome! Thanks. I've tried to do things like this before, but only
lately have I been having moderate to good success. Glad I persevered

 

[Cimjet]

Hi StargateFan
Good Show, I always put the "or" & the "and" at the beginning of a formula but I
didn't check all the state, I saw you were missing a comma so I just did that.
You know that's how we learn.
Regards
John