Excel Rank formula

Reply #11

theo1946 — Tue, 14 Apr 2015 17:25:17 GMT

=(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

Reply #10

justawiner — Mon, 13 Apr 2015 23:19:53 GMT

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.

Reply #9

theo1946 — Mon, 13 Apr 2015 22:15:57 GMT

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

Reply #8

justawiner — Mon, 13 Apr 2015 20:44:20 GMT

does anyone who knows excel know how to rank this example

Reply #7

justawiner — Mon, 13 Apr 2015 05:28:16 GMT

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

Reply #6

lakerben — Sun, 12 Apr 2015 21:21:46 GMT

Nice work Carlig

Reply #5

CarliG — Sun, 12 Apr 2015 20:16:46 GMT

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

Reply #4

justawiner — Sun, 12 Apr 2015 20:06:50 GMT

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... [ More ]

Reply #3

SergeM — Sun, 12 Apr 2015 18:08:46 GMT

N AF

7, 18 14

2, 12, 15, 27 13

5, 8 12

1, 19 11

17, 21, 35, 40 10

14, 24, 30, 34, 38, 42, 45 9

3, 11, 13, 20, 25, 33, 41 8

9, 29, 31 7

4, 23, 26, 28, 37, 39 6

6, 10, 22, 44 4

32, 36, 43 3

16 2

Another model done with php.

Reply #2

SergeM — Sun, 12 Apr 2015 14:26:22 GMT

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... [ More ]

Reply #1

CARBOB — Sun, 12 Apr 2015 08:46:02 GMT

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

Excel Rank formula

justawiner — Sun, 12 Apr 2015 06:50:33 GMT

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