Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Equal ranks - but no gaps
I checked quite a number of answers in this area, but couldn't find a
solution to my little ranking problem (column B is the ranking as I would like to see it): A B 1 Value Rank 2 1 1 3 1 1 4 1 1 5 2 2 <----- that being the 'tricky' part :) 6 3 3 Thank you very much! Thomas |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Equal ranks - but no gaps
It's late ...ignore my last reply!
"Thomas" wrote: I checked quite a number of answers in this area, but couldn't find a solution to my little ranking problem (column B is the ranking as I would like to see it): A B 1 Value Rank 2 1 1 3 1 1 4 1 1 5 2 2 <----- that being the 'tricky' part :) 6 3 3 Thank you very much! Thomas |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Equal ranks - but no gaps
Try:
=SMALL($A$1:$A$5,ROW()) "Thomas" wrote: I checked quite a number of answers in this area, but couldn't find a solution to my little ranking problem (column B is the ranking as I would like to see it): A B 1 Value Rank 2 1 1 3 1 1 4 1 1 5 2 2 <----- that being the 'tricky' part :) 6 3 3 Thank you very much! Thomas |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Equal ranks - but no gaps
one method would be to use some helper columns(C:D?)
use advanced filter to generate a list of unique numbers from column A in the first helper column in the second helper column, use the rank() function on the first helper column in column B use vlookup(A2,C$1!:D$3,2) copy and drag down "Thomas" wrote: I checked quite a number of answers in this area, but couldn't find a solution to my little ranking problem (column B is the ranking as I would like to see it): A B 1 Value Rank 2 1 1 3 1 1 4 1 1 5 2 2 <----- that being the 'tricky' part :) 6 3 3 Thank you very much! Thomas |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Equal ranks - but no gaps
Set B2=1
in B3 and copy down: =IF(A3=A2,B2,B2+1) "Thomas" wrote: I checked quite a number of answers in this area, but couldn't find a solution to my little ranking problem (column B is the ranking as I would like to see it): A B 1 Value Rank 2 1 1 3 1 1 4 1 1 5 2 2 <----- that being the 'tricky' part :) 6 3 3 Thank you very much! Thomas |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Equal ranks - but no gaps
If the values are unsorted:
=IF(A2="","",SUMPRODUCT(--(A2A$2:A$6),1/COUNTIF(A$2:A$6,A$2:A$6&""))+1) -- Biff Microsoft Excel MVP "Toppers" wrote in message ... Set B2=1 in B3 and copy down: =IF(A3=A2,B2,B2+1) "Thomas" wrote: I checked quite a number of answers in this area, but couldn't find a solution to my little ranking problem (column B is the ranking as I would like to see it): A B 1 Value Rank 2 1 1 3 1 1 4 1 1 5 2 2 <----- that being the 'tricky' part :) 6 3 3 Thank you very much! Thomas |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Equal ranks - but no gaps
Thanks a lot!! Worked like a charm!!!!!
"T. Valko" wrote: If the values are unsorted: =IF(A2="","",SUMPRODUCT(--(A2A$2:A$6),1/COUNTIF(A$2:A$6,A$2:A$6&""))+1) -- Biff Microsoft Excel MVP "Toppers" wrote in message ... Set B2=1 in B3 and copy down: =IF(A3=A2,B2,B2+1) "Thomas" wrote: I checked quite a number of answers in this area, but couldn't find a solution to my little ranking problem (column B is the ranking as I would like to see it): A B 1 Value Rank 2 1 1 3 1 1 4 1 1 5 2 2 <----- that being the 'tricky' part :) 6 3 3 Thank you very much! Thomas |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Equal ranks - but no gaps
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Thomas" wrote in message ... Thanks a lot!! Worked like a charm!!!!! "T. Valko" wrote: If the values are unsorted: =IF(A2="","",SUMPRODUCT(--(A2A$2:A$6),1/COUNTIF(A$2:A$6,A$2:A$6&""))+1) -- Biff Microsoft Excel MVP "Toppers" wrote in message ... Set B2=1 in B3 and copy down: =IF(A3=A2,B2,B2+1) "Thomas" wrote: I checked quite a number of answers in this area, but couldn't find a solution to my little ranking problem (column B is the ranking as I would like to see it): A B 1 Value Rank 2 1 1 3 1 1 4 1 1 5 2 2 <----- that being the 'tricky' part :) 6 3 3 Thank you very much! Thomas |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
PivotTable w/ Ranks | Excel Discussion (Misc queries) | |||
sorting with RANK/VLOOKUP (problem with equal ranks) | Excel Worksheet Functions | |||
DUPLICATE RANKS | Excel Worksheet Functions | |||
Transpose Column With Gaps to Row With No Gaps? | Excel Discussion (Misc queries) | |||
Sum of ranks | Excel Worksheet Functions |