How to sum lowest lanking numbers?

J

J.W.

I have the following code that ranks numbers from highest to lowest. ie.
50=1, 40=2, etc.
{=SUM(1*(D8<$D$7:$D$23))+1+IF(ROW(D8)-ROW($D$7)=0,0,SUM(1*(D8=OFFSET($D$1,0,0,INDEX(ROW(D8)-ROW($D$7)+1,1)-1,1))))}
This is good for most of my sheet. What I don't know is how to change this
so that it ranks numbers from lowest to highest. ie. 40=1, 50=2. I'm sure
it's simple, but I can't see it. Thanks
 
B

bj

have you thought about using the built in rank function? it allows you to
rank top to bottom or bottom to top.
 
N

N Harkawat

for ranking lowest as 1(first)
=SUM(--(D7>$D$7:$D$23))+COUNTIF($D$7:D7,D7)

Simply change the sign of ">" to "<" and you get the order the other way

Of course both are array entered {ctrl+shift+enter}

The problem of built in rank function is that it gives the same rank if
there are 2 or more identical values AND
it just works with numbers
 
J

J.W.

Hi, THANKS for the help. I thought that I tried the reversing of the "<">".
It did work with the results i was looking for.
Thanks again.
 
Top