HELP with nest "IF(AND" Formula to determine structure size

[bfuentes1412]

Hello,

I'm trying to figure out a formula to determine a structure size (Q5) based on the total value of cells J6:N6 along with the value in P5. I partially can get it to work using nested "IF(AND.." but can't figure out how to mix in the "Cable Trench" and "2 banks of "*"" into the formula to make it work.

Here's the criteria... If the total value of J6:N6 is >4 and P5 is <=500, then 6x8'6".. If J6:N6 is <4 and P5 is <=500, then 92x74, IF J6:N6 is >4 and P5 is >500, then 8x10, IF ANY value in J6:N6 is = 2 banks of "*", then 8x10 (XFMR size doesn't matter), IF ANY value in J6:N6 = Cable Trench, then 10x12 (XFMR size doesn't matter). In the example provided, Q5 should return "10x12" b/c N6 list's "CABLE TRENCH".

Please let me know if I need to add more info or provide clarification. Thank you!

All in One Calculator v2.xlsm
	I	J	K	L	M	N	O	P	Q	R	S
3		panel 1	panel 2	panel 3	panel 4	panel 5		xfmr size	structure		structure size
4											92x74
5	duct size	4"	4"	4"	4"	24x36		500	6x8'6"		6x8'6"
6	# of duct	1	1	1	2	CABLE TRENCH					8x10
7											10x12
Sheet2

Cell Formulas
Range	Formula
Q5	Q5	=IF(AND(SUM(J6:N6)>4,P5<=500),S5,IF(AND(SUM(J6:N6)<=4,P5<=500),S4,IF(AND(SUM(J6:N6)>4,P5>500),S6,IF(AND(SUM(J6:N6)<=4,P5>500),S6,S7))))

 

[DanteAmor]

There is a detail in your criteria, if the sum is <4 and P5 > 500, then you are not putting a result. With the following formula I am returning "None" for that.

=LET(a,J6:N6,b,SUM(a),c,CONCAT(a),IF(ISNUMBER(SEARCH("2 banks of ""*""",c)),S6,IF(ISNUMBER(SEARCH("CABLE TRENCH",c)),S7,IF(AND(b>4,P5>500),S6,IF(AND(b>4,P5<=500),S5,IF(AND(b<4,P5<=500),S4,"None"))))))

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[bfuentes1412]

Thanks @DanteAmor! That works. For the "<4 and P5 > 500" that criteria should return "8x10".

 

[bfuentes1412]

@DanteAmor.. Found 1 issue... if I enter "2 banks of "*"" and also enter "Cable Trench" in another cell, it doesn't change to 10x12. It defaults to the size for the 2 banks. The cable trench should supersede the 2 banks.

 

[DanteAmor]

Try:

=LET(a,J6:N6,b,SUM(a),c,CONCAT(a),IF(ISNUMBER(SEARCH("CABLE TRENCH",c)),S7,IF(ISNUMBER(SEARCH("2 banks of ""*""",c)),S6,IF(AND(b>4,P5>500),S6,IF(AND(b>4,P5<=500),S5,IF(AND(b<4,P5<=500),S4,"None"))))))

 

[bfuentes1412]

Think that did it.. Thanks @DanteAmor!