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?

 

[xpous]

You are a Genius Marcelo!

Thanks a lot

 

[Marcelo Branco]

You are a Genius Marcelo!

Thanks a lot

You are welcome and thank you for the feedback

M.
ps: not a genius , just someone who follows the threads in this forum, to improve my knowledge of Excel, and sometimes helping people.