Percentrank with array more than 9768 rows

[Bpuad]

Hi,

I'm trying to use the Percentrank function to rank salaries, but the list is more than 10.000 rows which Percentrank doesn't seem to like. I can use the function as long as I keep the array bellow row 9769? Any idea how I can solve this?

 

[Eric W]

In my testing, the PERCENTRANK function does work on ranges over 10,000 rows. Here's my worksheet:

	A	B	C	D	E
1	1		10	0	0.0009
2	2		11	0.001	0.001
3	3		100	0.009	0.009901
4	4		777	0.077	0.077608
5	5		9769	0.976	0.976898
6	6		9800	0.979	0.979998
7	7		10000	1	1
8	8
9	9
10	10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula D1 =PERCENTRANK($A$1:$A$10000,C1) E1 =COUNTIF($A$1:$A$10000,"<"&C1)/COUNTIFS($A$1:$A$10000,"<>"&C1,$A$1:$A$10000,"<>") A1 =ROW() <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

The data in column A goes to row 10000.

It is worth noting that PERCENTRANK only goes to 3 decimal places and truncates after that. So values under .001 will be 0 (see row 1). If that is unacceptable, you can create your own PERCENTRANK function by combining COUNTIF like the formula in E1.

Hope this helps!

 

[Bpuad]

Hi,

thank you, you are so right. The problem was because I had a cell with error on way down in the document. I thought I deleted all data but apparently missed one cell. Sorry for this and thank you for the answer.

/Per

 

[Eric W]

I'm glad you figured it out.