=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?
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
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)