Rank function using a conditional

[xpous]

Hi:

I have a table with 3 columns (NAME, ROOM, GRADE). I would like to be able to have a 4th column using the Rank function to have the NAMES ranked by GRADE but depending on the ROOM. So, in the 4th column, I should have 3 NAMES ranked as 1 if I have 3 ROOMS. The ROOMS are not numeric. They have a name too like (RED, GREEN, BLUE).

I'm breaking my head trying to figure out how can I do that without any luck. Can you please help?

 

[Marcelo Branco]

Hi

Could you provide a data sample and expected results?

M.

 

[BiocideJ]

Could you post an example?
I'm not sure I understand what the result is supposed to look like.

Here is a somewhat generally aimed shot in the dark.

Excel 2007
	A	B	C	D	E	F
1	NAME	ROOM	GRADE		ROOM	Top Grade
2	Sam	Green	95		Blue	Susan
3	Paul	Red	80		Green	Jessica
4	Joe	Blue	75		Red	Paul
5	Susan	Green	90
6	Jessica	Green	96
7	Ralph	Blue	94
8	Brian	Blue	55
9	Renee	Red	40
10	Josie	Red	80
Sheet1

Cell Formulas
Range	Formula
F2	{=LOOKUP(MAX(($C$2:$C$10)*($B$2:$B$10=E2)),$C$2:$C$10,$A$2:$A$10)}
F3	{=LOOKUP(MAX(($C$2:$C$10)*($B$2:$B$10=E3)),$C$2:$C$10,$A$2:$A$10)}
F4	{=LOOKUP(MAX(($C$2:$C$10)*($B$2:$B$10=E4)),$C$2:$C$10,$A$2:$A$10)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[xpous]

Hi:

You have the data table right BiocideJ. The only thing is that I need Rank function, not Max.

 

[BiocideJ]

Hi:

You have the data table right BiocideJ. The only thing is that I need Rank function, not Max.

Then I definitely need an example. How do you expect to see the ranks? The way you are describing what you want it seems like you could get your results simply by sorting the columns.

 

[xpous]

Like everything in Excel, there are many ways to get to a result and sorting may be a manual way of doing it but, that's not what I need.

I need a way that I could just replace the values of the first 3 columns and the Rank could be automatically generated. I should see in that 4th column (the Ranking) with 1,5,3,2,1,6,4,3,... the 1 could be repeated because there will always be a 1st ranked per ROOM. So, then I could know how the NAME is RANK per ROOM.

 

[Marcelo Branco]

Using the data sample kindly provided by BiocideJ in #3, maybe (Excel 2007 or higher)

D2
=COUNTIFS($B:$B,B2,$C:$C,">"&C2)+1
copy down

If you want to break the ties, for example Paul and Josie in Group Red, the formula can be adjusted.

M.

 

[BiocideJ]

Using the data sample kindly provided by BiocideJ in #3, maybe (Excel 2007 or higher)

D2
=COUNTIFS($B:$B,B2,$C:$C,">"&C2)+1
copy down

If you want to break the ties, for example Paul and Josie in Group Red, the formula can be adjusted.

M.

Brilliant. Provides the same results a rank function would provide also. (i.e. multiple #1's)

 

[CalvinTy]

I can see the confusion here... Biocide's table was nice to see, but I did get puzzled when "Susan" got the top grade but Susan isn't even in Blue group (in Green group) and the top grade of Green group is actually Jessica.

I see that Marcelo Branco's solution works well. I hadn't even known that a column can be selected without a row number before, i.e. $B:$B would select entire B column (no need to actually specify number of rows).

Nice to learn several things today (first day on the forum after registering back in March). Thanks to xpous for posting this topic then.

* CalvinTy

 

[Marcelo Branco]

Brilliant. Provides the same results a rank function would provide also. (i.e. multiple #1's)

Thank you.

To break the ties instead of
=COUNTIFS($B:$B,B2,$C:$C,">"&C2)+1

this
=COUNTIFS($B:$B,B2,$C:$C,">"&C2)+COUNTIFS($B$2:B2,B2,$C$2:C2,C2)

M.