If statement with grades

[rwmill9716]

I need an IF statement that keys off grades of C- to A+. Something like this: IF(B1>="C-",A1,""). I know that statement doesn't work, while using a series of OR statements does, but I wondered if there was something simpler than the OR statement approach which uses 9 substatements, e.g., OR(B1="C-",B1="C", etc).

 

[Sam_D_Ben]

Something like this.

Excel 2013/2016
	A	B
1	Data	Remarks
2	10	C-
Sheet10

Cell Formulas
Range	Formula
B2	=IF(OR(A2<=10,A2<=15),"C-",IF(OR(A2<=20,A2<=25),"C",IF(OR(A2<=30,A2<=35),"B-",IF(OR(A2<=40, A2<=45),"B",IF(OR(A2<=50, A2<=55),"A-",IF(OR(A2<=60, A2<=100),"A+","-"))))))

 

[rwmill9716]

Thanks, Sam_D_Ben,

This is not exactly what I was looking for. Assume your col A had a list of names and Col B a list of grades. I want names in Col C that had a grade of C- or better. I know that I can do this with a series of OR statements (and I set it up that way), but I wondered if there was a simplier IF statement, like IF(B2>="C-",A2,"") for Cell C2.

 

[Fluff]

Maybe

Book1
	A	B	C
1	Name	Grade
2	Tom Pearce	A	Tom Pearce
3	Bill Brewer	D	Peter Gurney
4	Jan Stewer	F	Dan'l Whiddon
5	Peter Gurney	C-	Harry Hawke
6	Peter Davy	D+	Tom Cobbley
7	Dan'l Whiddon	C+
8	Harry Hawke	A-
9	Tom Cobbley	B+
End

Cell Formulas
Range	Formula
C2	=IFERROR(INDEX($A$2:$A$9,AGGREGATE(15,6,(ROW($A$2:$A$9)-ROW($A$2)+1)/($B$2:$B$9<="C+"),ROWS($1:1))),"")

 

[Eric W]

Have you tried filtering it? Just select column B, click the filter button, then check all the A, B, and C boxes.

If you want a formula, here are 2 options:

Excel 2012

	A	B	C	D	E	F	G
Name	Grade	Passers	Score above:	B-	Grades
Amy	A	Amy	Bob	F
Bob	B-	Bob	Erica	D-
Claire	C-	Claire	Fred	D+
Dan	D	Erica	Irene	D+
Erica	A+	Fred	Kelly	C-
Fred	B+	Hal		C
Ginger	F	Irene		C+
Hal	C-	Kelly		B-
Irene	B+			B
Jack	F			B+
Kelly	A-			A-
A
A+

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1
​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]C2[/TH]
[TD="align: left"]=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$20)/(LEFT($B$2:$B$20)={"A","B","C"}),ROWS($C$2:$C2))),"")[/TD]
[/TR]
[TR]
[TH]D2[/TH]
[TD="align: left"]=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$20)/(MATCH($B$2:$B$20,$G$2:$G$14,0)>=MATCH($E$1,$G$2:$G$14,0)),ROWS($C$2:$C2))),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



The first has the A, B, or C hardcoded in the formula (following +/- sign ignored). The second lets you put an arbitrary grade in E1, and selects the grades that are higher in the grade list in column G.

 

[Rick Rothstein]

Maybe

Book1
	A	B	C
1	Name	Grade
2	Tom Pearce	A	Tom Pearce
3	Bill Brewer	D	Peter Gurney
4	Jan Stewer	F	Dan'l Whiddon
5	Peter Gurney	C-	Harry Hawke
6	Peter Davy	D+	Tom Cobbley
7	Dan'l Whiddon	C+
8	Harry Hawke	A-
9	Tom Cobbley	B+
End

Cell Formulas
Range	Formula
C2	=IFERROR(INDEX($A$2:$A$9,AGGREGATE(15,6,(ROW($A$2:$A$9)-ROW($A$2)+1)/($B$2:$B$9<="C+"),ROWS($1:1))),"")

Assuming Fluff's layout, here is an array-entered** formula that you can use...

=IFERROR(INDEX(A:A,SMALL(IF(B$2:B$9<"D",ROW(B$2:B$9)),ROWS(C$1:C1))),"")

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself.

 

[rwmill9716]

Thanks, Eric and Fluff. Both of these work for me.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Rick Rothstein]

Thanks, Eric and Fluff. Both of these work for me.

Just wondering... did my formula not work for you?