How to write a ms excel formula for converting marks into grade?

[NityanandJi]

How to write a ms excel formula for converting marks into grade- A1- 91-100, A2- 81-90, B1- 71-80, B2- 61-70, C1- 51-60, C2- 41-50, D- 33-40, E1- 21-33, E2- 0-21 marks.

 

[NityanandJi]

=IF(A2>=91,”A1”,IF(A2>=81,”A2”,IF(A2>=71,”B1”,IF(A2>=61,”B2”,IF(A2>=51,”C1”,IF(A2>=41,”C2”,IF(A2>=33,”D”,IF(A2>=21,”E1”,”E2”))))))))

I have written this formula but its not working.

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LOOKUP(A2,{0,21,33,41,51,61,71,81,91},{"E2","E1","D","C2","C1","B2","B1","A2","A1"})

or create a lookup table

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2	15	E2	E2			0	E2
3	22	E1	E1			21	E1
4	34	D	D			33	D
5	43	C2	C2			41	C2
6	55	C1	C1			51	C1
7	61	B2	B2			61	B2
8	72	B1	B1			71	B1
9	82	A2	A2			81	A2
10	93	A1	A1			91	A1
Main

Cell Formulas
Range	Formula
B2:B10	B2	=LOOKUP(A2,{0,21,33,41,51,61,71,81,91},{"E2","E1","D","C2","C1","B2","B1","A2","A1"})
C2:C10	C2	=VLOOKUP(A2,$F$2:$G$10,2,1)

 

[Marcelo Branco]

Split first column in two columns, Lower and Higher, and try

Pasta1
	A	B	C	D	E	F
1	LOWER	HIGHER	GRADE		MARKS	GRADE
2	91	100	A1		91	A1
3	81	90	A2		100	A1
4	71	80	B1		81	A2
5	61	70	B2		33	D
6	51	60	C1		20	E2
7	41	50	C2		71	B1
8	33	40	D		18	E2
9	21	32	E1		50	C2
10	0	20	E2
11
Plan3

Cell Formulas
Range	Formula
F2:F9	F2	=INDEX(C$2:C$10,MATCH(E2,B$2:B$10,-1))

M.

 

[NityanandJi]

Hi & welcome to MrExcel.
How about

=LOOKUP(A2,{0,21,33,41,51,61,71,81,91},{"E2","E1","D","C2","C1","B2","B1","A2","A1"})

or create a lookup table

+Fluff 1.xlsm
	A	B	C	D	E	F	G
1
2	15	E2	E2			0	E2
3	22	E1	E1			21	E1
4	34	D	D			33	D
5	43	C2	C2			41	C2
6	55	C1	C1			51	C1
7	61	B2	B2			61	B2
8	72	B1	B1			71	B1
9	82	A2	A2			81	A2
10	93	A1	A1			91	A1
Main

Cell Formulas
Range	Formula
B2:B10	B2	=LOOKUP(A2,{0,21,33,41,51,61,71,81,91},{"E2","E1","D","C2","C1","B2","B1","A2","A1"})
C2:C10	C2	=VLOOKUP(A2,$F$2:$G$10,2,1)

Thanks Fluff its working perfectly.

 

[Fluff]

Glad we could help & thanks for the feedback.