Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help in ranking Formula
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help in ranking Formula
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^9B2+A2/10^9))+1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help in ranking Formula
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 On 29 Gru, 14:46, George wrote: 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help in ranking Formula
wow! a cute one!
;-) On 29 Gru, 15:12, barry houdini wrote: 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^9B2+A2/10^9))+1 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help in ranking Formula
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help in ranking Formula
Another one:
=RANK(B2,B$2:B$15)+SUMPRODUCT(--(B2=B$2:B$15),--(A2<A$2:A$15)) -- Biff Microsoft Excel MVP "George" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help in ranking Formula
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 "T. Valko" wrote: Another one: =RANK(B2,B$2:B$15)+SUMPRODUCT(--(B2=B$2:B$15),--(A2<A$2:A$15)) -- Biff Microsoft Excel MVP "George" wrote in message ... 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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help in ranking Formula
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. -- Thanks, George "Jarek Kujawa" wrote: 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 On 29 Gru, 14:46, George wrote: 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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help in ranking Formula
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "george" wrote in message ... 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 "T. Valko" wrote: Another one: =RANK(B2,B$2:B$15)+SUMPRODUCT(--(B2=B$2:B$15),--(A2<A$2:A$15)) -- Biff Microsoft Excel MVP "George" wrote in message ... 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help in ranking Formula
On Dec 30, 3:43*pm, george wrote:
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 "T. Valko" wrote: Another one: =RANK(B2,B$2:B$15)+SUMPRODUCT(--(B2=B$2:B$15),--(A2<A$2:A$15)) -- Biff Microsoft Excel MVP "George" wrote in message ... 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- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ranking values in multiple subsets using one single formula | Excel Discussion (Misc queries) | |||
Formula for Ranking | Excel Discussion (Misc queries) | |||
Ranking Formula Question | Excel Discussion (Misc queries) | |||
Ranking Formula | Excel Discussion (Misc queries) | |||
Ranking formula question? | Excel Discussion (Misc queries) |