IF Formula not working

[Budi_K]

I has try an IF formula from old post for my case, but its not working well.
Only get single value/result (FALSE).

Exercise.xlsx
	E	F
1	%	Result
2	3.0	D.O
3	15.0	D.O
4	18.0	D.O
5	40.0	D.O
6	50.0	D.O
7	55.0	D.O
8	65.0	D.O
9	100.0	D.O
Sheet2

Cell Formulas
Range	Formula
F2:F9	F2	=IF(E2=15,"F Low",IF(E2=18,"F",IF(E2=40,"E",IF(E2=50,"C Low",IF(E2=55,"C",IF(E2=65,"B",IF(E2=100,"A","D.O")))))))

Need help to solve it.

Thanks in advanced

 

[Dave Patton]

You may have entered column E as Text.
You could use a Lookup formula.
Please explain what you are trying to do and whether or not column E should be numbers formatted with 1 decimal place or Text.

T202205a.xlsm
	E	F
1		Result
2	3	???
3	15	F Low
4	18	F
5	40	E
6	50	C Low
7	55	C
8	65	B
9	100	A
10
4a

Cell Formulas
Range	Formula
F2:F9	F2	=IF(E2=3,"???",IF(E2=15,"F Low",IF(E2=18,"F",IF(E2=40,"E",IF(E2=50,"C Low",IF(E2=55,"C",IF(E2=65,"B",IF(E2=100,"A","D.O"))))))))

 

[mustacheman]

Your formula works for the exact input. The reason f2 does not work is because you don't have a defined value for what 3 is. But the function works for on f3 (15 = "F Low").

I'm guessing you might be wanting to put those scores as ranges? So if the score is under 15 it's F Low. To do that you would need to use the greater than or less than >=.

Another way you could accomplish this is a simple vlookup table and have these all defined in another table to reference to. Nested if statements can be complicated when you use that many variables. I would do a vlookup with values 1 to 100 and then put the grades in the next column over. Then use a vlookup instead of an the If statement.

 

[Budi_K]

You may have entered column E as Text.
You could use a Lookup formula.

T202205a.xlsm
	E	F
1		Result
2	3	???
3	15	F Low
4	18	F
5	40	E
6	50	C Low
7	55	C
8	65	B
9	100	A
10
4a

Cell Formulas
Range	Formula
F2:F9	F2	=IF(E2=3,"???",IF(E2=15,"F Low",IF(E2=18,"F",IF(E2=40,"E",IF(E2=50,"C Low",IF(E2=55,"C",IF(E2=65,"B",IF(E2=100,"A","D.O"))))))))

OMG, it's working...thank you very much.

 

[Budi_K]

Your formula works for the exact input. The reason f2 does not work is because you don't have a defined value for what 3 is. But the function works for on f3 (15 = "F Low").

I'm guessing you might be wanting to put those scores as ranges? So if the score is under 15 it's F Low. To do that you would need to use the greater than or less than >=.

Another way you could accomplish this is a simple vlookup table and have these all defined in another table to reference to. Nested if statements can be complicated when you use that many variables. I would do a vlookup with values 1 to 100 and then put the grades in the next column over. Then use a vlookup instead of an the If statement.

Thank you for your advice, I would try for VLOOKUP.

Thank you very much.

 

[Dave Patton]

A guess

T202205a.xlsm
	E	F	G	H	I	J
1	%	Result
2	3%	unknown		0 -3.0%	0	unknown
3	14%	F Low		3.01% - 15.0%	3.01%	F Low
4	17%	F		15.01% - 18.0%	15.01%	F
5
4a

Cell Formulas
Range	Formula
F2:F4	F2	=LOOKUP(E2,I2:J4)

 

[Budi_K]

A guess

T202205a.xlsm
	E	F	G	H	I	J
1	%	Result
2	3%	unknown		0 -3.0%	0	unknown
3	14%	F Low		3.01% - 15.0%	3.01%	F Low
4	17%	F		15.01% - 18.0%	15.01%	F
5
4a

Cell Formulas
Range	Formula
F2:F4	F2	=LOOKUP(E2,I2:J4)

So H Column for scoring as ranges and then using VLOOKUP/LOOKUP in F2.
The same concept for J:K Columns.

Thank you.

 

[Dave Patton]

It will help if you explain what your are trying to do!
My mini example is a guess.
Column H is not used in the calculation; it is explanation text.
The Table for the Lookup is in Columns I:J.

Try reading Excel's Help information for Lookup and Vlookup.