Welcome Guest
You last visited January 16, 2017, 9:40 pm
All times shown are
Eastern Time (GMT-5:00)

# Excel Rank formula

Topic closed. 11 replies. Last post 2 years ago by theo1946.

 Page 1 of 1
GA
United States
Member #158157
August 13, 2014
99 Posts
Offline
 Posted: April 12, 2015, 2:50 am - IP Logged
 0 1 2 3 4 5 6 7 8 9 3 0 0 0 0 3 9 14 14 9

hello i check and couldn't find a thread. but can someone who understands excel better than me tell me what formula i could use to rank these?

i want to rack the numberline in the top 0-9 based on amounts of times hit. highest to lowest please. and if there is a tie, i was trying to put the higher number come first. but i dont know how to. any spare change for the poor?

ORLANDO, FLORIDA
United States
Member #4924
June 3, 2004
5961 Posts
Offline
 Posted: April 12, 2015, 4:46 am - IP Logged

Don't know if this is what you need. I copied the numbers, then paste special, transpose, column I &column J,  then sort column J high to low.

 7 14 8 14 6 9 9 9 0 3 5 3 1 0 2 0 3 0 4 0

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: April 12, 2015, 10:26 am - IP Logged
 0 1 2 3 4 5 6 7 8 9 3 0 0 0 0 3 9 14 14 9

hello i check and couldn't find a thread. but can someone who understands excel better than me tell me what formula i could use to rank these?

i want to rack the numberline in the top 0-9 based on amounts of times hit. highest to lowest please. and if there is a tie, i was trying to put the higher number come first. but i dont know how to. any spare change for the poor?

Add a row for rank.

Add column for numbers, add row for rank.
Fill with data by rank, by number, else '-'.

 DIGIT 0 1 2 3 4 5 6 7 8 9 FREQ. 3 0 0 0 0 3 9 14 14 9 RANK 5 7 7 7 7 5 3 1 1 3 NRS\RANK 1 2 3 4 5 6 7 8 9 10 1 7 - 6 - 0 - 1 - - - 2 8 - 9 - 5 - 2 - - - 3 - - - - - - 3 - - - 4 - - - - - - 4 - - - 5 - - - - - - - - - - 6 - - - - - - - - - - 7 - - - - - - - - - - 8 - - - - - - - - - - 9 - - - - - - - - - - 10 - - - - - - - - - -

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: April 12, 2015, 2:08 pm - IP Logged
NAF
7, 1814
2, 12, 15, 2713
5, 812
1, 1911
17, 21, 35, 4010
14, 24, 30, 34, 38, 42, 459
3, 11, 13, 20, 25, 33, 418
9, 29, 317
4, 23, 26, 28, 37, 396
6, 10, 22, 444
32, 36, 433
162

Another model done with php.

GA
United States
Member #158157
August 13, 2014
99 Posts
Offline
 Posted: April 12, 2015, 4:06 pm - IP Logged
NAF
7, 1814
2, 12, 15, 2713
5, 812
1, 1911
17, 21, 35, 4010
14, 24, 30, 34, 38, 42, 459
3, 11, 13, 20, 25, 33, 418
9, 29, 317
4, 23, 26, 28, 37, 396
6, 10, 22, 444
32, 36, 433
162

Another model done with php.

thank you very much for your response, but i am not sure i understand you.

I have number 0-9 listed example of placement: cell A1-J1 the first ten cells.

Below it i have the amount of times each number hits in A2-J2.

Now i want to rank  0 1 2 3 4 5 6 7 8 9. in the ranking order from most hit to least hit in A3-J3.

IN my example above it would come like 8 7 9 6 5 0 4 3 2 1. but i dont know how to rank it like that in excel or what code to start with in A3.

and noticeably if two or more numbers have a tie, like 8 & 7 hit 14 times, the order would be highest first. so order 8 7.

thank you very much i just wanted to explain more clearly.

Florida
United States
Member #66575
October 30, 2008
3549 Posts
Offline
 Posted: April 12, 2015, 4:16 pm - IP Logged

Maybe you can alter / adjust this formula

=(IF((H17>0),RANK(H17,\$H\$17:\$Q\$17),(RANK(H17,\$H\$17:\$Q\$17,1)+COUNTIF(\$H\$17:\$Q\$17,">0")))+COUNTIF(\$H\$17:H17,H17))-1

New Mexico
United States
Member #86099
January 29, 2010
11158 Posts
Online
 Posted: April 12, 2015, 5:21 pm - IP Logged

Maybe you can alter / adjust this formula

=(IF((H17>0),RANK(H17,\$H\$17:\$Q\$17),(RANK(H17,\$H\$17:\$Q\$17,1)+COUNTIF(\$H\$17:\$Q\$17,">0")))+COUNTIF(\$H\$17:H17,H17))-1

Nice work Carlig!

GA
United States
Member #158157
August 13, 2014
99 Posts
Offline
 Posted: April 13, 2015, 1:28 am - IP Logged

As in this example, i just want to understand what does what in the formula. thanks again.

GA
United States
Member #158157
August 13, 2014
99 Posts
Offline
 Posted: April 13, 2015, 4:44 pm - IP Logged

As in this example, i just want to understand what does what in the formula. thanks again.

does anyone who knows excel know how to rank this example?

Bakersfield, Ca
United States
Member #89877
April 17, 2010
202 Posts
Offline
 Posted: April 13, 2015, 6:15 pm - IP Logged

does anyone who knows excel know how to rank this example?

Did you try carlig's formula? it worked for me.

GA
United States
Member #158157
August 13, 2014
99 Posts
Offline
 Posted: April 13, 2015, 7:19 pm - IP Logged

it didn't work for me, i don't know which part of the formula to change to which particular cell, if someone could do it with the example ill see what does what.

Bakersfield, Ca
United States
Member #89877
April 17, 2010
202 Posts
Offline
 Posted: April 14, 2015, 1:25 pm - IP Logged

it didn't work for me, i don't know which part of the formula to change to which particular cell, if someone could do it with the example ill see what does what.

=(IF((A2>0),RANK(A2,\$A\$2:\$J\$2),(RANK(A2,\$A\$2:\$J\$2,1)+COUNTIF(\$A\$2:\$J\$2,">0")))+COUNTIF(\$A\$2:A2,A2))-1

You will need to change the column in the formula or copy and paste this formula to cell a3 and then copy cell a3  in b3 thru j3 with the formula paste.

 0 1 2 3 4 5 6 7 8 9 3 0 0 0 0 3 9 14 14 9 5 7 8 9 10 6 3 1 2 4

By the way,

Thank You CarliG

 Page 1 of 1