Home |
Search |
Today's Posts |
#1
|
|||
|
|||
ranking an row of values
I would like to modify the way Excel ranks a range of values. An example
might be helpful: value rank 4.6 1 4.7 2 4.9 3 5.1 4 5.2 5.5 5.2 5.5 5.3 7 5.4 8 5.5 9 5.6 10 5.8 11 6.1 12 6.2 13 6.3 14 6.5 15.5 6.5 15.5 6.8 17 7.2 18 7.7 19 8 20 8.1 21 As you can see, I would like the ranking to deal with identical values by assigning them both the average rank of the values immediately adjacent to them (see 5.2 as an example.). Is there a convenient way to do this? |
#2
|
|||
|
|||
One way:
=RANK(A1,$A$1:$A$10,1)+(COUNTIF($A$1:$A$10,A1)-1)/2 Tim C "Wazooli" wrote in message ... I would like to modify the way Excel ranks a range of values. An example might be helpful: value rank 4.6 1 4.7 2 4.9 3 5.1 4 5.2 5.5 5.2 5.5 5.3 7 5.4 8 5.5 9 5.6 10 5.8 11 6.1 12 6.2 13 6.3 14 6.5 15.5 6.5 15.5 6.8 17 7.2 18 7.7 19 8 20 8.1 21 As you can see, I would like the ranking to deal with identical values by assigning them both the average rank of the values immediately adjacent to them (see 5.2 as an example.). Is there a convenient way to do this? |
#3
|
|||
|
|||
If your values are in A1:A14, then enter in cell B1:
=SUM(ROW(INDIRECT(RANK($A1,$A$1:$A$14,TRUE)&":"&RA NK ($A1,$A$1:$A$14,TRUE)+COUNTIF($A$1:$A$14,$A1)-1))/COUNTIF ($A$1:$A$14,$A1)) Enter this formula with CTRL+SHIFT+ENTER (as array formula). Finally copy it down to B14. HTH, Bernd |
#4
|
|||
|
|||
Thanks. Works like a charm. Now I need to go figure out why.
"Tim C" wrote: One way: =RANK(A1,$A$1:$A$10,1)+(COUNTIF($A$1:$A$10,A1)-1)/2 Tim C "Wazooli" wrote in message ... I would like to modify the way Excel ranks a range of values. An example might be helpful: value rank 4.6 1 4.7 2 4.9 3 5.1 4 5.2 5.5 5.2 5.5 5.3 7 5.4 8 5.5 9 5.6 10 5.8 11 6.1 12 6.2 13 6.3 14 6.5 15.5 6.5 15.5 6.8 17 7.2 18 7.7 19 8 20 8.1 21 As you can see, I would like the ranking to deal with identical values by assigning them both the average rank of the values immediately adjacent to them (see 5.2 as an example.). Is there a convenient way to do this? |
#5
|
|||
|
|||
It counts the number of duplicates, and adds .5 for each one (minus itself).
Tim C "Wazooli" wrote: Thanks. Works like a charm. Now I need to go figure out why. "Tim C" wrote: One way: =RANK(A1,$A$1:$A$10,1)+(COUNTIF($A$1:$A$10,A1)-1)/2 Tim C "Wazooli" wrote: I would like to modify the way Excel ranks a range of values. An example might be helpful: value rank 4.6 1 4.7 2 4.9 3 5.1 4 5.2 5.5 5.2 5.5 5.3 7 5.4 8 5.5 9 5.6 10 5.8 11 6.1 12 6.2 13 6.3 14 6.5 15.5 6.5 15.5 6.8 17 7.2 18 7.7 19 8 20 8.1 21 As you can see, I would like the ranking to deal with identical values by assigning them both the average rank of the values immediately adjacent to them (see 5.2 as an example.). Is there a convenient way to do this? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Zero values in a log chart | Charts and Charting in Excel | |||
Second serie doesn't use X-as values | Charts and Charting in Excel | |||
Save External Link Values | Excel Discussion (Misc queries) | |||
Formula to list unique values | Excel Worksheet Functions | |||
If I have X,Y data how do I sum the Y values using a set of bins based on x values | Excel Worksheet Functions |