Help in ranking Formula

[George]

Hi All,

I have data in two rows I need to give ranking rule, based on quality first
and then who is highest in production.

Production Quality
9300 100.00%
9310 99.99%
9320 99.98%
9330 99.97%
9340 99.96%
9350 99.95%
9360 100.00%
9370 99.99%
9380 99.98%
9390 99.97%
9400 99.96%
9410 99.95%
9420 100.00%
9430 99.99%

For Instance- If I choose Quality for Ranking, I wil get three 1st ranks
based on quality 100%. But I want only three different ranks for the analysts
based quality first and then on production. So I shld get 1st for (9420 -
100.00%), 2nd for (9360 - 100.00%) and 3rd rank for (9300 - 100.00%). Please
help

Thanks,
George

 

[barry houdini]

Hello George,

Assuming your Production figures are in A2:A15 and percentages in
B2:B15 then try this formula in C2 copied down

=SUMPRODUCT(--(B$2:B$15+A$2:A$15/10^9>B2+A2/10^9))+1

 

[Jarek Kujawa]

will sth. like (insert the formula in 1st row):

=LARGE(IF($B$1:$B$50=100%,$A$1:$A$50,),ROW())&" - 100%"

help?

CTRL+SHIFT+ENTER this formula as it is an array-formula

 

[Jarek Kujawa]

wow! a cute one!
;-)

 

[Bernd P]

Hello George,

A general approach which also works for texts:
http://www.sulprobil.com/html/sorting.html

For your purpose the COUNTIF() + COUNTIF() formula should do...

Regards,
Bernd

 

[T. Valko]

Another one:

=RANK(B2,B$2:B$15)+SUMPRODUCT(--(B2=B$2:B$15),--(A2<A$2:A$15))

 

[george]

Thanks All for your helps. Special Thanks to Valko and Barry, the forumlas
given by you works. Sorry for the delay in letting you, I had a trouble in
finding this thread.

 

[george]

This formula I did try, but not sure if I am using it in the wrong. I am not
so good in excel anyways Thanks I did get wot I had wanted from this thread.

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[barry houdini]

Thanks All for your helps. Special Thanks to Valko and Barry, the forumlas
given by you works.  Sorry for the delay in letting you, I had a trouble in
finding this thread.
--
Thanks,
George






- Show quoted text -

Probably better to go with Biff's suggestion. It's "cleaner" and less
susceptible to error should you have percentages which differ by small
decimal places