Nested If Function

[DOH Guy]

Hello All, I have having great difficulty trying to find the right formulas in excel to do what I want it to do. I am trying to have a If function formula switch between which formula to use based off another cell.

Like this, I want the cell to use this function if B12 has the color "White" selected. =IF(B12="White",IF(G1>189,"Pass","Fail")) but if B12 has the color "Yellow" selected I want the same cell to use this function IF(B12="Yellow",IF(G1>142,"Pass","Fail")). As you can see it is the same formula but the only differences are the colors and the "IF" function of G1. How can I have the same cell (I1) choose which formula to use based upon what color is selected in B12. If G1's average is above 189 while the color white is selected it passes at the same time if G1's average is above 142 while the color is y yellow it passes.

In the end how can I have the same cell choose which formula to used based upon what color is selected in B12.

I hope this makes sense. Here is formula I come up with but it does work but only in my head lol. Where am I wrong and how can I make this work

=IF(B12="White",IF(G1>189,"Pass","Fail")), IF(B12="Yellow",IF(G1>142,"Pass","Fail"))

 

[dreid1011]

You could try this:

=IF(B12="White",IF(G1>189,"Pass","Fail"),IF(B12="Yellow",IF(G1>142,"Pass","Fail"),""))

But I prefer this:

=IF(OR(AND(B12="White",G1>189),AND(B12="Yellow",G1>142)),"Pass","Fail")

 

[DOH Guy]

You could try this:

=IF(B12="White",IF(G1>189,"Pass","Fail"),IF(B12="Yellow",IF(G1>142,"Pass","Fail"),""))

But I prefer this:

=IF(OR(AND(B12="White",G1>189),AND(B12="Yellow",G1>142)),"Pass","Fail")

AWESOME! That worked perfectly! Thank you so much. I have been busting my brain back and forth all day trying to figure that out. Thank You

 

[dreid1011]

AWESOME! That worked perfectly! Thank you so much. I have been busting my brain back and forth all day trying to figure that out. Thank You

You're welcome, I am happy to help.

 

[Alex Blakenburg]

If you want to make it more scalable then you could do something like this.
(Ideally make the lookup table an actual Excel table so that the formula automatically includes additional rows as you add them)

Book2
	A	B	C	D	E	F	G	H
1						Score	200
2						Pass/Fail	Pass	<--- Result
3
11
12	Colour	White				Colour	Pass threshold
13						White	189
14						Yellow	142
Sheet1

Cell Formulas
Range	Formula
G2	G2	=IF( G1>VLOOKUP(B12,$F$13:$G$17,2,FALSE), "Pass", "Fail" )