5 Formulae in 1 Cell? (IF, AND, OR)...no Buts about it!

[ThanksAlot]

You have to be a lot smarter than I am to figure this out. I wish I were fluent in the language of Excel, but I am not. How the rest of you get this stuff is beyond me. Thanks for taking a look.<o></o>
I have sorted this out the best I could.<o></o>
<o> </o>
<o> </o>
IF AD2 = YES, and AE2 = YES, AND T2 = Positive 1 thru Positive 20, then U2 = T2 +1<o></o>
<o> </o>
OR<o></o>
<o> </o>
IF AD2 = YES, and AE2 = YES, AND T2 = Negative 1 thru Negative 10, then U2= T2+ Neg. 1 (-1)<o></o>
<o> </o>
OR<o></o>
<o> </o>
IF AD2 = YES, and AE2 = NO, AND T2 = Positive 1 thru Positive 20, then U2 = Neg. 1 (-1)<o></o>
<o> </o>
OR<o></o>
<o> </o>
IF AD2 = YES, and AE2 = NO, AND T2 = Negative 1 thru Negative 10, then U2= Pos. 1 (+1)<o></o>
<o> </o>
OR<o></o>
<o></o>
IF AD2 = NO, and AE2 = NO, then U2 = T2 (No change between T2 and U2)<o></o>
<o> </o>
Possible values in U2, depending on value in T2:<o></o>
<o> </o>
Yes, Yes w/ Positive 3 in T2= 4<o></o>
Yes, Yes w/ Negative 3 in T2 = (pos.) 1<o></o>
Yes, No w/ Positive 3 in T2 = (neg.) -1<o></o>
Yes, No w/ Negative 3 in T2 = (neg.) -4<o></o>
No, No = T2 (no change)<o></o>
<o> </o>
ThanksAlot! -Jon<o></o>
<o> </o>

 

[Weazel]

So briefly looking at the logic, shouldn't the answers be reversed? Or am I just over tired?

Yes, No w/ Negative 3 in T2 = (neg.) -4
Yes, Yes w/ Negative 3 in T2 = (pos.) 1

 

[Weazel]

In the mean time you can try...

=IF(AND(AD2="Yes",AE2="Yes",T2>=1,T2<=20),T2+1,IF(AND(AD2="Yes",AE2="Yes",T2>=-10,T2<=-1),T2+-1,IF(AND(AD2="Yes",AE2="No",T2>=1,T2<=20),-1,IF(AND(AD2="Yes",AE2="No",T2>=-10,T2<=-1),1,IF(AND(AD2="No",AE2="No"),T2,"")))))

in U2
Probably not the most elegant or efficient.
The result will be the reverse of your tests for the negatives, hence my previous question, though I may have overlooked or missed something

I'm also kind of anxious for someone to come along and show me how to make it shorter.

 

[ThanksAlot]

WEAZEL! YOU are The MAN. You did it. This was the most complicated formula I have had to sweat over. Seriously, I have wanted to come up with a way of tracking this information for three years. I finally sat down to do it this week and I worked day and night just to get as far as I did. And YOU solved the question. You are my Guru! I know nothing of Excel. I did not even know how to explain my goal, and you untangled my mess.

I tested all scenarios: (YES, YES +), (YES, YES -), (YES, NO +), (YES, NO -), and (NO, NO). Right off the bat, (NO, NO) worked. Then, I tried the two positive conditions. They worked! I only had trouble with the two negative conditions, but I knew you had the answer. In order to understand what you did, I broke down the conclusions, line by line. I reversed the answers for the two negative conditions, which you mentioned in your first post, and they worked. I applied your formula to over 500 lines, I double-checked each answer, and they were all correct.

Thanks for being brave enough and good enough to take on my question and solve it. Cheers, Weazel!

 

[Scott Huish]

Perhaps:

=IF(AD2="YES",IF(OR(AND(T2>=1,T2<=20),AND(T2<=-1,T2>=-10)),T2+SIGN(T2)*IF(AE2="NO",-1,1),""),IF(AND(AD2=AE2,AE2="NO"),T2,""))

 

[ThanksAlot]

Ha! Nice job, Scott. I think you not only got it right, but you reduced the formula's length. It all works, though for the both the YES,NO + and YES, NO -, the answers are reversed. I made the change to Weasel's formula, but I don't know where to start with yours simply because...I don't understand a word of it!

You both get kudos for making this work. Thanks for your help! -J Scott

 

[Scott Huish]

If I understand you, change this part IF(AE2="NO",-1,1) to IF(AE2="NO",1,-1)

 

[Scott Huish]

Shortened it a little more and with the correction:

=IF(AD2="YES",IF(AND(T2>=-10,T2<=20,T2<>0),T2+SIGN(T2)*IF(AE2="NO",1,-1),""),IF(AND(AD2=AE2,AE2="NO"),T2,""))