Conditional Formatting with Rank

[Bella Jeon]

Dear Excel Expert,

I'd like to color the cell based on the Ranking to see the Lowest price, 2nd Lowest price, 3rd Lowest price within the same Q'ty Quotation.
i.g : The Lowest Price : in Green
2nd Lowest Price : in Yellow
3rd Lowest Price : in Red.. ect.

How can I get this result ?

Part # Q'ty A B C D E F
12345 10 $45.00 $45.00 $30.50 $34.88 $30.00 $5.00
12345 20 $18.00 $18.00 $12.20 $16.08 $14.03 $8.48
12345 30 $10.46 $11.25 $11.30 $10.49 $14.03 $8.48
12345 40 $10.75 $9.20 $10.39 $14.03 $8.48
12345 50 $9.56 $9.50 $8.80 $10.30 $14.03 $8.48
12346 10 $250.00 $196.17 $200.00 $105.00
12346 20 $83.34 $65.43 $15.21 $4.96
12346 30 $50.00 $39.25 $15.21 $4.96
12346 40 $31.25 $25.22 $15.21 $4.96
12346 50 $33.36 $25.00 $22.08 $15.21 $4.96
12347 10 $10.77 $10.53 $9.45 $10.40 $5.00 $2.00
12347 20 $10.62 $10.31 $8.91 $9.93 $8.71 $8.30
12347 30 $9.89 $9.69 $8.80 $9.48 $8.71 $8.30
12347 40 $9.47 $9.30 $8.50 $8.58 $8.71 $8.30
12347 50 $9.28 $8.94 $8.40 $8.05 $8.71 $8.30

what kind of formula do I need to use ?

 

[Somendra Misra]

Hi Bella Jeon,

Welcome to the forum.

Please have a look of attached file, is this what you are looking after?

Just advise if any issue.

Regards,

 

[Bella Jeon]

Hi Somendra,

Oh My God !!! How could you be that Fast ? !!! I can't believe that you've feedback that quick !
Yes, exactly, that's what I want to have. Thanks Millions !

 

[Somendra Misra]

@Bella Jeon

Glad you solve it.

Regards,

 

[SirJB7]

Hi, Bella Jeon!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

Regards!