How to sum lowest lanking numbers?
 

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

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

"J.W." wrote:

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

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




"bj" wrote in message
...
have you thought about using the built in rank function? it allows you to
rank top to bottom or bottom to top.

"J.W." wrote:

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

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.

"N Harkawat" wrote:

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




"bj" wrote in message
...
have you thought about using the built in rank function? it allows you to
rank top to bottom or bottom to top.

"J.W." wrote:

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