Rank Function

[vmjan02]

I tried a lot but not sure as what it the problem, the function is not giving me the correct output, in column AQ, any way to fix it.

Book1
	AM	AN	AO	AP	AQ	AR
5						Actual Rank
6	Row Labels	Count of PHONE_NUM			Rank	should Be
7	RIYADH_Tuwaiq_TWIQRDJ113V	13			1	1
8	RIYADH_Al Yarmuk_YRMKRDJ1152	11			2	2
9	RIYADH_Al Munsiyah_MUNSRDJ1192	5			3	3
10	RIYADH_Qurtubah_KRTBRDJ1250	5			4	4
11	(blank)	5
12	RIYADH_Al Munsiyah_MUNSRDJ1223	4			6	5
13	RIYADH_Al Yarmuk_YRMKRDJ118D	4			7	6
14	RIYADH_Al Rabeea_RABERDJ1294	4			8	7
15	RIYADH_Al Nadhim_NDIMRDJ0039	3			9
16	RIYADH_Ghirnatah_GRNTRDJ114E	3			10
17	RIYADH_Qurtubah_KRTBRDJ1257	3			11
18	RIYADH_Al Munsiyah_MUNSRDJ1232	3			12
19	RIYADH_Huteen_HTYNRDJ1124	3			13
20	RIYADH_Al Murabba_MURBRDJ128R	3			14
21	RIYADH_Al Qadisiyah_QADSRDJ1113	3			15
22	RIYADH_Tuwaiq_TWIQRDJ113G	2			16
23	RIYADH_Ishbiliyyah_SPELRDJ127B	2			17
24	RIYADH_Al Muruj_MRUJRDJ1185	2			18
25	RIYADH_Yasmeen_YSMNRDJ1125	2			19
26	RIYADH_Ishbiliyyah_SPELRDJ127D	2			20
27	RIYADH_Al Mughrizat_MGRZRDJ123H	2			21
28	RIYADH_Al Falah_FLAHRDJ1124	2			22
29	RIYADH_Tuwaiq_TWIQRDJ114S	2			23
30	RIYADH_Yasmeen_YSMNRDJ11C6	2			24
31	RIYADH_Yasmeen_YSMNRDJ1126	2			25
32	RIYADH_Al Malqa_MLGARDJ151C	2			26
33	RIYADH_Ar Rayyan_RYANRDJ115H	2			27
34	RIYADH_Al Ezdehar_ZDHRRDJ113D	2			28
35	RIYADH_Qurtubah_KRTBRDJ0013	1			29
36	RIYADH_Al Masani_MSNERDJ1182	1			30
37	RIYADH_Al Amal_AMELRDJ000D	1			31
38	RIYADH_Al Ezdehar_ZDHRRDJ113N	1			32
39	RIYADH_IRQAH_ERQARDJ12Z1	1			33
40	RIYADH_Al Qirawan_GYRNRDJ1527	1			34
41	MAKKAH_AL UMRAH AL JADIDAH_UMRNKHJ023N	1			35
42	RIYADH_Al Rabeea_RABERDJ1291	1			36
43	RIYADH_Al Nadhim_NDIMRDJ114U	1			37
44	JEDDAH_AL SALAMEH_SLMHKHJ11F9	1			38
45	RIYADH_Huteen_HTYNRDJ1123	1			39
46	RIYADH_Al Rimayah_RMYARDJ113C	1			40
47	RIYADH_Al Munsiyah_MUNSRDJ1190	1			41
48	RIYADH_Al Shohada_SHHDRDJ002O	1			42
49	RIYADH_Qurtubah_KRTBRDJ1251	1			43
50	RIYADH_Al Wahah_WAHARDJ1119	1			44
51	RIYADH_Tuwaiq_TWIQRDJ1142	1			45
52	TABUK_AL RABIEA_RBEATBJ0007	1			46
53	RIYADH_Yasmeen_YSMNRDJ1128	1			47
54	JEDDAH_AL RUGHAMAH_RGMAKHJ140M	1			48
55	RIYADH_Dherat Al Laben_ZHRHRDJ1133	1			49
56	ABHA_Al Badea_BADERRJ000W	1			50
57	RIYADH_Ghirnatah_GRNTRDJ114M	1			51
58	RIYADH_Al Yarmuk_YRMKRDJ118C	1			52
59	KHAMIS MUSHAYT_Al Jameiyine_KMISRRJ183C	1			53
60	RIYADH_Al Janadriyah_JNADRDJ0018	1			54
61	RIYADH_Al Munsiyah_MUNSRDJ0000	1			55
62	RIYADH_Al Yarmuk_YRMKRDJ119D	1			56
63	RIYADH_Ishbiliyyah_SPELRDJ127W	1			57
64	RIYADH_Al Yarmuk_YRMKRDJ119O	1			58
65	JEDDAH_AL MANAR_MNARKHJ001T	1			59
66	RIYADH_An Nakhil_NKELRDJ1390	1			60
67	JEDDAH_AJAWED_JAWDKHJ000V	1			61
68	RIYADH_An Narjis_NRJSRDJ11BM	1			62
69	AL QURAYYAT_AL FAISALIAH_FISLJFJ00C5	1			63
70	RIYADH_An Nuzhah_NZHARDJ117D	1			64
71	RIYADH_Ad Dar Al Baida_ADDRRDJ1153	1			65
72	JIZAN_ISKAN KING ABDULLAH FOR REFUGEE_RWENJZJ0000	1			66
73	RIYADH_Al Aqiq_AQEQRDJ1137	1			67
74	RIYADH_As Sahafah_SHAFRDJ115G	1			68
75	RIYADH_Al Ezdehar_ZDHRRDJ113G	1			69
76	RIYADH_Dherat Al Laben_DLBNRDJ0101	1			70
77	RIYADH_Dherat Al Laben_DLBNRDJ0116	1			71
78	RIYADH_-_ZDHRRDJ113D	1			72
79	DAMMAM_Al Fayhaa_FIHAEAJ11D5	1			73
80	KHAMIS MUSHAYT_Thelalah_ZETNRRJ0000	1			74
81	JEDDAH_AL YAQOOT_YAQTKHJ001L	1			75
82	RIYADH_Al Yarmuk_YRMKRDJ1182	1			76
83	RIYADH_Al Yarmuk_YRMKRDJ1184	1			77
84	Grand Total	147
Sheet1

Cell Formulas
Range	Formula
AQ7:AQ84	AQ7	=IFERROR(IF(OR(AM7="#N/A",AM7="Grand Total",AM7="(blank)"),"",RANK(AN7,$AN$7:$AN$500000,0)+COUNTIF($AN$7:AN7,AN7)-2),"")

 

[myall_blues]

The problem is not with the RANK but rather with the numbers you are ranking, in column AN. In your formula in column AQ you are instructing it to put a blank ("") if the value in AM is "(blank)", and it is doing it correctly, but the false part of the formula when the rank is calculated is still based on the entire set of values in column AN. As a test if you delete the 5 in AN11 you will find that your RANK formula works perfectly.
I don't know how you are generating the values for Count of PHONE_NUM in column AN, but if it's a formula then I would include the condition there, and then in column AQ just use a simple rank formula.
For example, in column AN:

=IF(OR(AM7="#N/A",AM7="Grand Total",AM7="(blank)"),0,COUNT(....)

or you might instead be able to use a COUNTIFS formula.

 

[hagia_sofia]

Hello, your might try:

=IFERROR(IF(OR(AM7="#N/A",AM7="Grand Total",AM7="(blank)"),"",COUNTIFS($AM$7:$AM$84,"<>"&"Grand Total",$AM$7:$AM$84,"<>"&"(blank)",$AM$7:$AM$84,"<>"&"#N/A",$AN$7:$AN$84,">"&AN7))+COUNTIF($AN$7:AN7,AN7),"")

or (as it seems that you are more interested in a sequence of sorted data)

=LET(
a,AM7:AM84,
IF((a<>"(blank)")*(a<>"#N/A")*(a<>"Grand Total"),SCAN(0,a,LAMBDA(a,b,IF(b="(blank)",a,a+1))),""))

 

[vmjan02]

The problem is not with the RANK but rather with the numbers you are ranking, in column AN. In your formula in column AQ you are instructing it to put a blank ("") if the value in AM is "(blank)", and it is doing it correctly, but the false part of the formula when the rank is calculated is still based on the entire set of values in column AN. As a test if you delete the 5 in AN11 you will find that your RANK formula works perfectly.
I don't know how you are generating the values for Count of PHONE_NUM in column AN, but if it's a formula then I would include the condition there, and then in column AQ just use a simple rank formula.
For example, in column AN:

=IF(OR(AM7="#N/A",AM7="Grand Total",AM7="(blank)"),0,COUNT(....)

or you might instead be able to use a COUNTIFS formula.

ok if instead of "" i change it to 0, still the output is not correct.

in this case the output is 1,2,3,4,0,6 instead of 1,2,3,4,5 and so on....

 

[vmjan02]

Hello, your might try:

=IFERROR(IF(OR(AM7="#N/A",AM7="Grand Total",AM7="(blank)"),"",COUNTIFS($AM$7:$AM$84,"<>"&"Grand Total",$AM$7:$AM$84,"<>"&"(blank)",$AM$7:$AM$84,"<>"&"#N/A",$AN$7:$AN$84,">"&AN7))+COUNTIF($AN$7:AN7,AN7),"")

or (as it seems that you are more interested in a sequence of sorted data)

=LET(
a,AM7:AM84,
IF((a<>"(blank)")*(a<>"#N/A")*(a<>"Grand Total"),SCAN(0,a,LAMBDA(a,b,IF(b="(blank)",a,a+1))),""))

this is perfect, thanks a lot