RANK not resulting as hoped

[Jeff]

I guess I misunderstand how RANK works...

The following is pasted across row C11:IV10

"=IF(C10=0,"",COUNT($C$10:$IV$10)-(RANK(C10,$C$10:$IV$10)+COUNTIF($C$10:C10,C10)-1)+1)"

Value in BO10 and CD10 are the same (201) which is the highest in that row...
but BO11 ranks BO10 253 and CD11 ranks CD10 252. I need it to be the other
way around. In other words, rank duplicate values higher the further (to the
right) across the row they are.

Thank you, Jeff

 

[Bob Phillips]

=IF(C10=0,"",(COUNT($C$10:$IV$10)-(RANK(C10,$C$10:$IV$10)+COUNTIF(C$10:$IV10,C10)-1)+1))

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[T. Valko]

Try this:

=IF(C10=0,"",RANK(C10,$C$10:$IV$10,1)+COUNTIF($C$10:C10,C10)-1)

 

[Jeff]

To: Bob & T.

Thank you very much! Both do the trick. I'm not sure which is better but I
like that T.,s is shorter so I'm going with that.

Thanks again, Jeff

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Jeff]

Sorry T., I spoke too soon…

I found that Bob's is actually working better. Both worked in that
particular row but I found that using yours in rows were I have many blanks
in between, it gave me the same problem I originally had but Bob's does work
in that case.

Thanks again for all your help.

Jeff

 

[Jeff]

One more thing...

I also found that this problem seems to only happen when the range is
horizontal. Strange as it is, even my original formula doesn’t have this
problem with vertical ranges.

Jeff

 

[T. Valko]

Hmmm...

I tested both formulas extensively and they both return identical results no
matter how I "abuse" them!

 

[Jeff]

Sorry again T.,

You are absolutly correct. I must have modified your code wrong or something
the first time I tried it. I tried it again and it works perfectly.

Thanks again,

Jeff