Have tried looking this up, and im sure its basic but …
=PERCENTRANK(IF(A1:A20000 = C1);.B1:B20000;B1)
Have two cols of data A and B - with multiple repeated values.
want to do the percentrank function on those values in col B when the value in col A matches the criteria in C1.
the percentrank value is in D1.
Feeling stupid! Thanks for any help
Sorry. From the as-if-formula you posted I cannot conclude what you actually want. From the words I get at least an impression.
Regarding the usage of a range of columm A in the condition you might mean something like
{=IF($A$1:$A$20 = $C$1;PERCENTRANK($B$1:$B$20;$B$1:$B$20);"")} entered for array-evaluation.
You migh be better off with a single-cell-formula like
=IF($A1 = $C$1;PERCENTRANK($B$1:$B$20;$B1);"") in, say F1 and filling it down as far as needed.
Anyway you need to consider that this kind of calculation for 20000 cells (??) may get rather inefficient and time-consuming if the rank-part must be evaluated for very many rows…
First of all, thanks for your response!
Im not sure if ive expressed the probelm correctly:
I have two long columns of data, one could be considered a label(with many repeating values), the other a value. I have a list of the unique data items in a seperate table, and for each of those unique entries I wish calc the Percentrank of a set value in the subset that relates to the unique data label.
Really the function could be anything really - so I guess what I am stuggling with is how to look up and enter as a ‘range’ a variable length list based on a condition.
I suspect my requirement is the need to use an Array-evaluation which is something I haven’t done before