Changing Rank Value in Excel Formula

Tag: excel Author: ilvhel1 Date: 2009-08-28
=RANK(F9,$F$5:$F$27,($A$43))

Can anyone help please. In the Rank formula above 'A43' refers to a cell whose value will constantly chnage between 1 and 22, depending on a time function. How do I tell the Rank formula to get the Rank Postion required from cell A43?

are you trying to pick a value from the range, rather than calculate the rank?

Other Answer1

though i don't exactly understand your question, here my approach to clarify what RANK exactly does:

=RANK(value, all_values, ordering)

whereas it returns the RANK of value out of the list of all_values. If ordering is 0, the function sorts descending, if 1 it sorts ascending.

you might consider this example for better understanding. It shows pretty well how to use the function.

the 3rd parameter of RANK (you provide the value of A43 here) is either 0 or 1 for descending or ascending ordering, respectively, and not for the selection of the value you want the rank of.

or am I getting you completely wrong?

regards

comments:

+1 NB any non-zero value for ordering results in a rank as if the list was sorted in ascending order
am i understanding you right? 0 = descending, anything else = ascending? regards
+1, kay didn't know that. regards

Other Answer2

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

Say you put the following in G5:

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

and copied down to G27. Then this, (let's put it in G28):

=MATCH(A43,G5:G27,0)

will find the location of the specified rank in that list and

=INDEX(F5:F27,G28)

will give us the value.

If you're comfortable with Array Formulae then this can be condensed into one super formula:

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

(use the above without the braces and put it into the worksheet with Control-Shift-Enter)