Rank function

[vmjan02]

I am not sure as why the rank function is not working, any wany out

 =RANK(M4, $M$4:$M$500000, 0) + COUNTIF($M$4:M4, M4) - 3

FDT_Daily_Analysis_Report_V1.xlsm
	M	N	O
3	Fault Rate	>=50	Top 5 FDT
4	1%	Yes	#N/A
5	1%	Yes	#N/A
6	1%	Yes	#N/A
7	2%	Yes	#N/A
8	1%	Yes	#N/A
9	3%	Yes	#N/A
10	1%	Yes	#N/A
11	2%	Yes	#N/A
12	1%	Yes	#N/A
13	1%	Yes	#N/A
14	1%	Yes	#N/A
15	2%	Yes	#N/A
16	1%	Yes	#N/A
17	2%	Yes	#N/A
18	1%	Yes	#N/A
19	1%	Yes	#N/A
20	2%	Yes	#N/A
21	6%	No	#N/A
22	2%	Yes	#N/A
23	1%	Yes	#N/A
24	1%	Yes	#N/A
25	0%	Yes	#N/A
26	2%	Yes	#N/A
27	1%	Yes	#N/A
28	3%	Yes	#N/A
29	2%	Yes	#N/A
30	1%	Yes	#N/A
31	2%	Yes	#N/A
32	1%	Yes	#N/A
33	3%	No	#N/A
34	1%	Yes	#N/A
35	1%	Yes	#N/A
36	1%	Yes	#N/A
37	3%	Yes	#N/A
38	1%	Yes	#N/A
39	4%	Yes	#N/A
40	1%	Yes	#N/A
41	1%	Yes	#N/A
42	3%	No	#N/A
43	4%	Yes	#N/A
44	7%	Yes	#N/A
45	2%	Yes	#N/A
46	1%	Yes	#N/A
47	6%	Yes	#N/A
48	4%	Yes	#N/A
49	4%	Yes	#N/A
50	2%	Yes	#N/A
51	3%	Yes	#N/A
52	1%	Yes	#N/A
53	7%	Yes	#N/A
54	9%	Yes	#N/A
55	8%	Yes	#N/A
56	4%	Yes	#N/A
57	5%	Yes	#N/A
58	5%	Yes	#N/A
59	9%	Yes	#N/A
60	1%	Yes	#N/A
61	4%	No	#N/A
62	2%	Yes	#N/A
63	8%	No	#N/A
64	2%	Yes	#N/A
65	3%	No	#N/A
66	2%	No	#N/A
67	4%	No	#N/A
68	3%	Yes	#N/A
Top-FDT-Fault-Rate

Cell Formulas
Range	Formula
N4:N68	N4	=IF(L4>=50,"Yes","No")
O4:O68	O4	=RANK(M4, $M$4:$M$500000, 0) + COUNTIF($M$4:M4, M4) - 3

 

[RobP]

Hi,

not sure what your +COUNTIF is looking to do, but if you delete it does the RANK work on its own ?

Rob

 

[Fluff]

Check that your numbers are real numbers & not text.
Also check that you don't have any #N/A values in col M

 

[vmjan02]

Hi,

not sure what your +COUNTIF is looking to do, but if you delete it does the RANK work on its own ?

Rob

Rank don't work it still giving me same error #N/A

 

[vmjan02]

Check that your numbers are real numbers & not text.
Also check that you don't have any #N/A values in col M

yes they are numbers and not text there are no #N/A values in column M.

 

[Fluff]

The only way that I can get the formula to return #N/A on all rows, is if all the numbers are text, or there is at least one #N/A error in col M.
Try changing the range the formula is looking at to your actual data range, rather than all the way down to row 500,000 & see if that works.

 

[vmjan02]

ok, it is strange for me.

 

[Fluff]

What happens if you limit the range as I suggested?

 

[vmjan02]

nothing same result as #N/A

 

[Fluff]

Can you share the actual workbook?