Nested IF AND OR Functions

[SkW3rL]

New member here & I am sorry if this is posted & answered elsewhere. I tried to do a search but wasn't finding exactly what I was looking for. I am trying to automate a workbook for another department at my office. As they are not too XL savvy, I saw an opportunity to help the team and get some quality time testing formulas. I have completed most of the workbook, with exception to my last formula; It is 4 nested IF AND OR functions:

=IF(AND(
Q3<=11.43,E3>=20,T3="ASB"),80,
IF(AND(OR(
E3=12, E3=14, E3=16, E3=18), Q3<=13.34,T3="ASB"),80,
IF(AND(OR(
E3=6, E3=8, E3=10), Q3<=16,T3="ASB"),80,
IF(AND(
E3<=4, Q3<=20, T3="ASB"), 80, Q3*R3))))

I have tried a few variations of the formula and it still never works. I believe the problem is from have some less than/greater than requirements in there and it getting confused. Basically, I need it to come back with 80 if it meets all the "AND" criteria in its respective row. I am by no means an expert when it comes to excel, but enjoy tinkering until I can get the functions to do what I want, but this one, I cannot seem to get right. Maybe I am not looking at it correctly, or there might be another way.

Thanks for the help in advance!

 

[SkW3rL]

Testing these line by line, it works until I get to the last function, then it becomes wonky and not giving me the desired "80" when e3=4 and q3=anything under 19.99. It just takes the false equation and multiplies q3xr3.

 

[FDibbins]

This is a quick summary of your rules as I see them...
[Table="width:, class:grid"][tr][td] [/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][/tr]
[tr][td]

11​

[/td][td]E3[/td][td]>=20[/td][td]12, 14, 16, 18[/td][td]6, 8, 10[/td][td]<=4[/td][/tr]

[tr][td]

12​

[/td][td]Q3[/td][td]<=11.43[/td][td]<=11.43[/td][td]<=16[/td][td]<=20[/td][/tr]

[tr][td]

13​

[/td][td]T3[/td][td]ABS[/td][td]ABS[/td][td]ABS[/td][td]ABS[/td][/tr]
[/table]

At 1st glance...
1. you could remove the ABS from each IF and pit it at the start...
=if(T2="ABS", IF(...............
2. You have various specific values for E3 - >=20, 12, 14, 16, 18 6, 8, 10, <=4
could there be values in between those?

 

[SkW3rL]

Thanks for your help! Your table is almost correct, d12 should be <=13.34. The additional numbers can be there. It won't matter too much from any standpoint by inputting them; they will never be entered, but it won't hurt any. I have tried doing all less thans/greater thans instead of equals to, nothing seems to work. Moving the ABS statement to the beginning changed nothing. When e3=4 and q3=19, they just get multiplied.

 

[SkW3rL]

=IF(T3="ASB",
IF(AND(
E3>=20,Q3<=11.43,),80,
IF(AND(OR(
E3>=12,E3<=18),Q3<=13.34),80,
IF(AND(OR(
E3>=6,E3<=10),Q3<=16),80,Q3*R3))))

This is how I have set it up now...

 

[SkW3rL]

=IF(T3="ASB",
IF(AND(
E3>=20,Q3<=11.43,),80,
IF(AND(OR(
E3>=12,E3<=18),Q3<=13.34),80,
IF(AND(OR(
E3>=6,E3<=10),Q3<=16),80,
IF(AND(
E3<=4,Q3<20),80,Q3*R3)))))

Posted the wrong formula...

 

[JPGKIll3R]

Hi, SkW3rL -

I tested your formula, line per line, and it worked as it should:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]B[/TD]
[TD]E[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[TD]T[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]20[/TD]
[TD]11.43[/TD]
[TD][/TD]
[TD]ASB[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]12[/TD]
[TD]13.34[/TD]
[TD][/TD]
[TD]ASB[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]14[/TD]
[TD]13.34[/TD]
[TD][/TD]
[TD]ASB[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]16[/TD]
[TD]13.34[/TD]
[TD][/TD]
[TD]ASB[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]18[/TD]
[TD]13.34[/TD]
[TD][/TD]
[TD]ASB[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]6[/TD]
[TD]16[/TD]
[TD][/TD]
[TD]ASB[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]8[/TD]
[TD]16[/TD]
[TD][/TD]
[TD]ASB[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]10[/TD]
[TD]16[/TD]
[TD][/TD]
[TD]ASB[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]4[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]ASB[/TD]
[/TR]
[TR]
[TD]80[/TD]
[TD]4[/TD]
[TD]10[/TD]
[TD]2[/TD]
[TD]ASB[/TD]
[/TR]
</tbody>[/TABLE]

 

[FDibbins]

My reason for asking about the "in between" numbers, was that if they wont ever be there, or it doesn't matter if they are there, maybe you could use >=6 and <=18, instead of specifying certain values. And maybe put those values in a table, then use vlookup or MATCH

 

[SkW3rL]

That's crazy if its working for you. Can you try e3=4, q3=17? It seems to give me problems between 16 and 20.

 

[SkW3rL]

With the following formula:

=IF(T3="ASB",
IF(AND(
E3>=20,Q3<=11.43,),80,
IF(AND(OR(
E3>=12,E3<=18),Q3<=13.34),80,
IF(AND(OR(
E3>=6,E3<=10),Q3<=16),80,
IF(AND(
E3<=4,Q3<20),80,Q3*R3)))))

and inputting e3=4, q3=17, I get a return of 68, which is what q3*43 would be.