Formula Help - If Cell Falls Within Certain Range

[jonnyd3388]

Hi! So sorry if a question like this has been posed before, I'm sure it will have been covered at some stage .. I'm looking to create a formula within a work spreadsheet as follows;

If the value of a cell is lower than 5 then new cell is A, if it is between 5 and 9.9 then B, if it is between 10 and 14.9 then C and so on (5 in total).

Thanks for any help!

 

[Toadstool]

Hi!

Here's a couple of methods. The CHOOSE assume no number to check will be 0.000000001 away from the next range (e.g. 4.999999999 is treated as 5).

	A	B	C	D	E	F	G

<tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]<5[/TD]
[TD="align: center"]<10[/TD]
[TD="align: center"]<15[/TD]
[TD="align: center"]<20[/TD]
[TD="align: center"]<25[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Result[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]66[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]<5[/TD]
[TD="align: center"]<10[/TD]
[TD="align: center"]<15[/TD]
[TD="align: center"]<20[/TD]
[TD="align: center"]<25[/TD]
[TD="align: center"]Check[/TD]
[TD="align: center"]Result[/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]22[/TD]
[TD="align: center"]33[/TD]
[TD="align: center"]44[/TD]
[TD="align: center"]55[/TD]
[TD="align: center"]66[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]44[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G2[/TH]
[TD="align: left"]=IFERROR(CHOOSE(INT(F2/5)+1,A2,B2,C2,D2,E2),"ERROR")[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G6[/TH]
[TD="align: left"]=IF(F6<5,A6,IF(F6<10,B6,IF(F6<15,C6,IF(F6<20,D6,IF(F6<25,E6,"ERROR")))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

My take on this

Book1
	A	B
1	Score	Grade
2	1	A
3	4	A
4	5	B
5	6	B
6	9	B
7	10	C
8	11	C
9	14	C
10	15	D
11	16	D
12	19	D
13	20	E
14	21	E
15	24	E
16	25
17	26
Test

Cell Formulas
Range	Formula
B2	=LOOKUP(A2,{0,5,10,15,20,25},{"A","B","C","D","E",""})

 

[jonnyd3388]

Thank you guys! These worked brilliantly, really appreciate your help.

 

[Fluff]

Glad we could help & thanks for the feedback