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]

Q3*R3, not q3*43

 

[FDibbins]

Looks to me like if E2 any value between 6 and 18 (if the other criteria are met), you want it to show 80.

=if(and(T3="abs", Q3<20,or(E2<=4, E2>=20, E2>=6, E2<=18)), 80,Q3*R3)

 

[SkW3rL]

Not quite. I am away from my computer for the rest of the evening. I will be back tomorrow morning, pacific time. I will get more into detail. I really appreciate your help.

 

[SkW3rL]

Ok, good morning. I wish it was as simple as 6-18 just equaled 80, when all less than equal to 20, and the other criteria being met, but the formula is very specific. This is a field insulation estimation workbook. I will explain the rules and provide a better example, but just to make sure all is on the table, the columns are as follows:

E = Pipe Diameter
Q = Linear Foot of Pipe/Insulation
R = Demolition burn rate
T = Insulation Type (ASB = Asbestos, there are many others)

What I need this formula to do is:
If the insulation being demoed is asbestos, and the pipe diameter is 6", and the linear feet to demo is 15', then I need the demo hours to show 80. This is why the specific breakdown for length and diameters.

If the pipe is 4" and under, then the length of the pipe must be shorter than 20' with asbestos to gain a minimum of 80 hours.
6-10" pipe, under 13.33' with asbestos, for a minimum of 80 hours
12-18" pipe, under 16' with asbestos, for a minimum of 80 hours
20" or greater, under 11.43' with asbestos, for a minimum of 80 hours.

I hope this clears a few things up and assists with a solution to my dilemma.

 

[SkW3rL]

This is what I ended up getting to work:

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

Not sure why it works by putting another "AND(", R3 cell, to read off of, but it does. It just seems cumbersome. I couldn't have '=IF(t3="ASB",IF(...' as @FDibbins (don't know how to tag users) had suggested, although I thought it was going to be a good idea but if the 'Insulation Type' column (T3) was any thing other than "ASB", it just returned a 'False' statement. I know that is my fault for not specifying what exactly was going on with the entire workbook and I do apologize. It would have been easier sending it, or posting, for anyone who stumbled across this post by getting a better understanding of the workbook. I really appreciate all your help. I'm sure I'll be back, I really enjoy making these workbooks function.

Thanks again!

 

[FDibbins]

Great stuff figuring it out!! As long as it is working for you, that's all hat really matters