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
1	Name	Grade	Passers	Score above:	B-		Grades
2	Amy	A	Amy	Bob			F
3	Bob	B-	Bob	Erica			D-
4	Claire	C-	Claire	Fred			D+
5	Dan	D	Erica	Irene			D+
6	Erica	A+	Fred	Kelly			C-
7	Fred	B+	Hal				C
8	Ginger	F	Irene				C+
9	Hal	C-	Kelly				B-
10	Irene	B+					B
11	Jack	F					B+
12	Kelly	A-					A-
13							A
14							A+

<tbody>
</tbody>

Sheet1
​

Worksheet Formulas Cell Formula C2 =IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$20)/(LEFT($B$2:$B$20)={"A","B","C"}),ROWS($C$2:$C2))),"") D2 =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))),"") <tbody> </tbody>

<tbody>
</tbody>

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?