CODEWITHSUNDEEP
excelexcel-formula
navima
navima

Reputation: 23

RANK only working with reference? Weighted rank

The RANK function does not work when the array to search in is not a reference. For example

RANK(Number,A10:A20)

works perfectly, but

RANK(Number,{1,2,3})

or

RANK(Number,A10:A20*10+B10:B20)

does not.

Why do the above not work?
How can I get weighted rank of a number without using excess cells?

Upvotes: 2

Views: 183

Answers (1)

ImaginaryHuman072889
ImaginaryHuman072889

Reputation: 5195

This is because RANK needs to refer to a cell range (for whatever reason) rather than a calculated or hardcoded array. Not sure why this is the case though. Perhaps someone else knows the answer.

Here's one alternative:

= SUMPRODUCT((Number>(A10:A20*10+B10:B20))+0)+1

Upvotes: 1

Related Questions