Clever Geek Handbook

Change rank value in Excel formula

=RANK(F9,$F$5:$F$27,($A$43))

Can anyone help. In the rank formula above, "A43" refers to a cell whose value will continually change between 1 and 22, depending on the function of time. How can I specify a rank formula to get the ranking required from cell A43?

+2


source to share


2 answers


although I don't understand your question exactly, here's my approach to figure out what exactly does RANK

:

=RANK(value, all_values, ordering)

whereas it returns RANK from value

from the list all_values

. If ordering

equal to 0, the function sorts in descending order; if 1, it sorts in ascending order.

you can consider this example for better understanding. He shows you how to use the function.



The 3rd parameter RANK

(you specify the value of A43 here) is either 0 or 1 for descending or ascending order, respectively, and not for choosing the value that is required for the rank.

or do I completely misunderstand you?

considers

+2


source


It looks like you want to get the value of the cell in F5: F27, whose rank corresponds to the value of A43.

Let's say you added the following to G5:

=RANK(F5,$F$5:$F$27)

and copied up to G27. Then this, (put this in G28):

=MATCH(A43,G5:G27,0)

will find the location of the specified rank in this list, and



=INDEX(F5:F27,G28)

will give us the value.

If you are comfortable with Array Formulaulas , then it can be boiled down into one super formula:

{=INDEX(F5:F27,MATCH(A43,RANK(F5:F27,F5:F27),0))}

(use the above without parentheses and place it in the worksheet with Control-Shift-Enter)

+1


source






More articles:


All Articles