Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 347
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 57
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 896
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 896
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 806
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 57
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Ranking values in multiple subsets using one single formula brianalucas Excel Discussion (Misc queries) 1 December 14th 07 09:08 PM
Formula for Ranking Neil H[_2_] Excel Discussion (Misc queries) 10 December 13th 07 02:53 AM
Ranking Formula Question 57Caddy Excel Discussion (Misc queries) 5 September 26th 07 09:25 PM
Ranking Formula Todd Nelson Excel Discussion (Misc queries) 5 February 3rd 06 12:26 AM
Ranking formula question? Skeep Excel Discussion (Misc queries) 1 January 26th 06 05:50 AM


All times are GMT +1. The time now is 12:26 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"