If And or what?

[Oyster1967]

I want to display
A if the mark is 85% - 100%
B if 71% - 84%
C if 51% - 70%
D if 0 - 50%

How do I expand the formula below to capture the data.
Is the IF statement the best option?

=IF(F1>=85%,"A","")
<TABLE style="WIDTH: 60pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=80><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 12pt; mso-width-source: userset; mso-width-alt: 585" width=16><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; HEIGHT: 15pt; COLOR: #9c0006; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl63 height=20 width=64 align=right>86%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 12pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=16>A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>12%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>59%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; HEIGHT: 15pt; COLOR: #9c0006; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl63 height=20 align=right>85%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>73%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>84%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>42%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>6%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; FONT-FAMILY: Calibri; BACKGROUND: #ffc7ce; HEIGHT: 15pt; COLOR: #9c0006; FONT-SIZE: 11pt; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; BORDER-RIGHT: windowtext 0.5pt solid; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" class=xl63 height=20 align=right>99%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>A</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 align=right>65%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64></TD></TR></TBODY></TABLE>

 

[Peter_SSs]

Would doing it this way work for you? Formula copied down.

Excel Workbook
	A	B
1	Mark	Grade
2	86%	A
3	12%	D
4	59%	C
5	85%	A
6	73%	B
7	84%	B
8	42%	D
9	6%	D
10	99%	A
11	65%	C
Grade

 

[Oyster1967]

Thanks Peter thats great, but (there's always a but isn't there) if the cell is blank I dont want any value to be shown, currently a D shows. I tried doing a IF statement, but it didnt work.
PS I dont really understand how the formula works... maybe i can look that up myself.
Thanks again

 

[VBACO]

Just a slight modification to Peters code. This will mean anything above 0 is a "D". 0 will be blank.

=LOOKUP(A12,{0,0.001,0.51,0.71,0.85},{"","D","C","B","A"})

 

[VBACO]

Also, just for instructional purposes. If you had to use the IF statements it would look something like this.

=IF(F1>=85%,"A",IF(F1>=71%,"B",IF(F1>=51%,"C",IF(F1>0,"D",""))))

But Peters method is much better IMO.

 

[Peter_SSs]

Just a slight modification to Peters code. This will mean anything above 0 is a "D". 0 will be blank.

=LOOKUP(A12,{0,0.001,0.51,0.71,0.85},{"","D","C","B","A"})

That may suit the OP but to my thinking it may be possible to actually get a mark of 0 which should result in a "D", as opposed to a row where there is no mark at all that should return a "".

So, my new suggestion is in column C with VBACO's in column B. The difference is in the green row.

Excel Workbook
	A	B	C
1	Mark	VBACO	Peter_SSs
2	86%	A	A
3	12%	D	D
4	59%	C	C
5	0%		D
6
7	85%	A	A
8	73%	B	B
9	84%	B	B
10	42%	D	D
11	6%	D	D
12	99%	A	A
13	65%	C	C
Grade

 

[DaveU]

Here's one way of doing it:

=IF(F1<85,IF(F1<71,IF(F1<51,"D","C"),"B"),"A")

regards,

Dave

 

[DaveU]

Added a correction to formula so blank values don't show as "D":

=IF(F12<85,IF(F12<71,IF(F12<51,IF(F12="","","D"),"C"),"B"),"A")

regards,

Dave

 

[Oyster1967]

Thanks to everyone, I chose to go with Peters 2nd suggestion because students do actually recieve 0% if they dont sit the exam.
I like the IF statements, i use them all the time and will refer to your examples as well in the future.
Thanks again

 

[VBACO]

One more side note oyster. Have we done away with the "F" grade?