Percentrank Formula No Longer Working

[rob51852]

I run some analysis each year and create a fresh workbook to do it in. Part of the analysis involves using Percentrank. For some reason this year I get the #VALUE! error even though I have used it in exactly the same way as previously.

The formula I am trying to use is: =100-(100*PERCENTRANK($N$2:$N$653,N2))

An extract from the sheet is included below. Can anyone see what the issue is?

Thanks

Trending Value 2021.xlsx
	N	O
1	Combined	ShY Rank
2	7.083465239	#VALUE!
3	-4.458466414	#VALUE!
4	-15.29020513	#VALUE!
5	-1.052507419	#VALUE!
6	-4.371007647	#VALUE!
7	-4.103220303	#VALUE!
8	-0.640398403	#VALUE!
9	1.535574278	#VALUE!
10	-43.34228108	#VALUE!
11	-2.456869908	#VALUE!
12	2.155525212	#VALUE!
13	-11.6081485	#VALUE!
14	-1.914077583	#VALUE!
15	-0.804026971	#VALUE!
16	-27.21923689	#VALUE!
17	-2.997530854	#VALUE!
18	-8.388484482	#VALUE!
19	-16.66188387	#VALUE!
20	7.070365308	#VALUE!
21	0.638454467	#VALUE!
22	0.844089852	#VALUE!
23	-11.85605073	#VALUE!
24	-0.699404472	#VALUE!
25	4.752906977	#VALUE!
26	-28.1858108	#VALUE!
27	1.203280167	#VALUE!
28	-23.30967552	#VALUE!
29	-1.19847131	#VALUE!
30	-15.68041309	#VALUE!
31	0.354366252	#VALUE!
32	6.371955159	#VALUE!
33	-1.058341217	#VALUE!
34	-0.298401649	#VALUE!
35	-3.200451097	#VALUE!
36	1.162737127	#VALUE!
37	0.054674686	#VALUE!
38	-86.13671584	#VALUE!
Analysis

Cell Formulas
Range	Formula
N2:N38	N2	=K2+M2
O2:O38	O2	=100-(100*PERCENTRANK($N$2:$N$653,N2))

 

[Toadstool]

Hi Rob51852,

You don't give us columns K and M so we can't use your XL2BB formula, but I think the problem is in your column N.

If any value in column N has #VALUE! then every row of column O will return #VALUE!