IF Statement

[BeckaB]

I am trying to do an if statement based on another if statement:

So if someone is In a team I want the formula to do another If statement based on this. Below is my current formula:

=IF(C2<54%,"1",IF(C2>=80%,"2","3"))

But I need to repeat this if statement 3 times as I have 3 teams with different %'s

So almost =IF(B2="Team1" then do the above, if it is team 2 do IF(C2<59%,"1",IF(C2>=90%,"2","3")) and so on

 

[FracinDean]

Make a table with three columns, "Team", "Threshold1", and "Threshold2", and 3 rows, one for each Team. For example purposes, name the 9 cells in the table as a named range "TeamThresholdValues".

Use the first column in your table as the validation range for cell B2.

Put the following formula in cell D2, and you will have the answer you are looking for:

=if(C2<vlookup(B2,TeamThesholdValues,2,FALSE),1,if(C2>=vlookup(B2,TeamThresholdValues,3,FALSE),2,3)

 

[BeckaB]

This won't work as in column B This specifies which team I need to look at

 

[FracinDean]

I made an error in my formula, here is what I meant to write:

=IF(C2<vlookup(b2,teamthresholdvalues,2,false),1,if(c2>=VLOOKUP(B2,TeamThresholdValues,3,FALSE),2,3))</vlookup(b2,teamthresholdvalues,2,false),1,if(c2>

 

[FracinDean]

=IF(C2><vlookup(b2,teamthesholdvalues,2,false),1,if(c2>VLOOKUP(B2,TeamThresholdValues,2,FALSE),1,IF(C2>=(VLOOKUP(B2,TeamThresholdValues,3,FALSE),2,3))

</vlookup(b2,teamthesholdvalues,2,false),1,if(c2>

 

[FracinDean]

...Except the first part should say

=IF(C2<VLOOKUP

 

[FracinDean]

I'm having great difficulty with the code editor today. I have not had this problem in the past. I apologize to any that have been inconvenienced.

I am trying to say the first operator should have been a less than sign, not a greater than sign.

Wow...

 

[BeckaB]

Hi, the vlookup won't work as I don't want to have a list of the % I need it to see if it is < or >= to a % so it needs to be an IF statement

 

[jmacleary]

Hi fracinDean

Whenever you use the < or > sign in a post, make sure it has a space before and after it - otherwise the forum tries to interpret it as a piece of html.

 

[FracinDean]

Thank you very much jmacleary

IF(B2="Team1",IF(C2 < 0.54,1,IF(C2 > =0.8,2,3),IF(B2="Team2",IF(C2 < 0.59,1,IF(C2 > = 0.90,2,3),IF(C2 < 0.65,1,IF(C2 > = 0.95,2,3))))))

I hope I counted all the parentheses correctly.