Row Ranking Not Working Correctly

[jessebh2003]

Here was have a calculated ranking in column I based on the value of column H. However, column I keeps producing #N/A on row 66. The rows are sorted from smallest to largest based on the value on column I. To troubleshoot, I've tried deleting the formulas and re-adding them; adding values to the blank cells in columns D:G; and even recreating the table from scratch in a new workbook, but all to no avail. Any ideas what would be causing this? Appreciate any help! Thanks!!

Priority Tool.xlsx
	A	B	C	D	E	F	G	H	I	J
3	Proj. ID:	Project Title	What problem are we trying to solve? What is the business impact we are targeting?	Project Scale	Ease (Effort) of Implementation	Quality Impact	Risk	Calculated Priority Number	Rank / Priority	Complete
4	1	Project HRO	Problem HRO	5	5	5	5	625	1
5	2	Project WV	Problem WV	5	5	4	5	500	2
6	3	Project OB	Problem OB	4	5	5	5	500	2
7	4	Project ED	Problem ED	4	3	5	5	300	4
8	5	Project CT	Problem CT	4	3	5	5	300	4
9	6	Project AMA	Problem AMA	4	3	5	5	300	4
10	7	Project PAR	Problem PAR	4	3	5	5	300	4
11	8	Project PAG	Problem PAG	4	3	5	5	300	4
12	9	Project OMN	Problem OMN	5	3	4	5	300	4
13	10	Project DE	Problem DE	3	4	5	5	300	4
14	11	Project ECR	Problem ECR	5	3	5	4	300	4
15	12	Project HP	Problem HP	5	3	5	4	300	4
16	13	Project ACE	Problem ACE	5	3	5	4	300	4
17	14	Project WM	Problem WM	4	3	4	5	240	14
18	15	Project DYS	Problem DYS	4	3	5	4	240	14
19	16	Project CE	Problem CE	5	4	4	3	240	14
20	17	Project IHSA	Problem IHSA	5	3	3	5	225	17
21	18	Project HUD	Problem HUD	3	3	5	5	225	17
22	19	Project QR	Problem QR	5	4	2	4	160	19
23	20	Project NMC	Problem NMC	3	3	4	4	144	20
24	21	Project LAB	Problem LAB	3	3	3	5	135	21
25	22	Project AOC	Problem AOC	5	3	3	3	135	21
26	23	Project NPO	Problem NPO	5	2	4	3	120	23
27	24	Project DEA	Problem DEA	5	2	4	3	120	23
28	25	Project UCAPP	Problem UCAPP	3	4	3	3	108	25
29	26	Project OR	Problem OR	3	4	3	3	108	25
30	27	Project OASIS	Problem OASIS	3	3	3	4	108	25
31	28	Project CMO	Problem CMO	4	3	3	3	108	25
32	29	Project VWOB	Problem VWOB	1	4	5	5	100	29
33	30	Project L4L	Problem L4L	5	5	2	2	100	29
34	31	Project ID	Problem ID	2	2	5	5	100	29
35	32	Project ROL	Problem ROL	4	4	3	2	96	32
36	33	Project HT	Problem HT	4	2	3	4	96	32
37	34	Project HRP	Problem HRP	4	2	4	3	96	32
38	35	Project CLS	Problem CLS	3	2	4	4	96	32
39	36	Project LMST	Problem LMST	5	3	2	3	90	36
40	37	Project AIM	Problem AIM	3	2	3	5	90	36
41	38	Project OPGMA	Problem OPGMA	3	3	3	3	81	38
42	39	Project RMA	Problem RMA	2	2	5	4	80	39
43	40	Project OASISE	Problem OASISE	3	2	3	4	72	40
44	41	Project EOM	Problem EOM	4	3	2	3	72	40
45	42	Project BMT	Problem BMT	3	3	2	4	72	40
46	43	Project LMAA	Problem LMAA	5	3	2	2	60	43
47	44	Project ICMBTS	Problem ICMBTS	2	2	3	5	60	43
48	45	Project HUGS	Problem HUGS	4	1	5	3	60	43
49	46	Project FP	Problem FP	3	3	3	2	54	46
50	47	Project TRC	Problem TRC	4	1	3	4	48	47
51	48	Project NHSC	Problem NHSC	2	3	2	4	48	47
52	49	Project CIN	Problem CIN	4	1	3	4	48	47
53	50	Project PWT	Problem PWT	3	1	3	5	45	50
54	51	Project DTTTL4L	Problem DTTTL4L	5	2	2	2	40	51
55	52	Project WHACE	Problem WHACE	3	1	3	4	36	52
56	53	Project AAT	Problem AAT	3	1	3	4	36	52
57	54	Project PB	Problem PB	3	4	1	3	36	52
58	55	Project OPGER	Problem OPGER	3	1	3	4	36	52
59	56	Project TBLMS	Problem TBLMS	4	4	2	1	32	56
60	57	Project OHTS	Problem OHTS	4	4	1	2	32	56
61	58	Project QV	Problem QV
62	59	Project OHLB	Problem OHLB
63	60	Project LBCIDPI	Problem LBCIDPI
64	61	Project GMCPOSD	Problem GMCPOSD
65	62	Project AMC	Problem AMC
66	63	Project CVICU	Problem CVICU	1	2	2	5	20	#N/A
67	64	Project CO	Problem CO	5	2	1	2	20	#N/A
68	65	Project VTO	Problem VTO	4	4	1	1	16	#N/A
69	66	Project STST	Problem STST	4	1	2	2	16	#N/A
70	67	Project OPGPY1P	Problem OPGPY1P	4	1	2	2	16	#N/A
71	68	Project CDAE	Problem CDAE	4	1	2	2	16	#N/A
72	69	Project PMH	Problem PMH	2	5	5	5	250	#N/A
73	70	Project CVE	Problem CVE	5	2	5	5	250	#N/A
74	71	Project HRI	Problem HRI	4	4	4	4	256	#N/A
Sheet1

Cell Formulas
Range	Formula
H4:H74	H4	=IF(OR(ISBLANK(D4),ISBLANK(E4),ISBLANK(F4),ISBLANK(G4))," ",D4*E4*F4*G4)
I4:I74	I4	=IF(H4=" "," ",RANK.EQ(H4,PriorityNumbers,0))
A4:A74	A4	=ROW()-3

Named Ranges
Name	Refers To	Cells
PriorityNumbers	=Sheet1!$H$4:$H$61	I4:I74

Cells with Data Validation
Cell	Allow	Criteria
D3	Any value
E3	Any value
F3	Any value
G3	Any value
H3	Any value
D4:G74	List	1,2,3,4,5
H4:H74	Any value
I3:I4	Any value
J3	Any value
J4:J74	List	Y,N

 

[Fluff]

You're named range only goes down to H61 which is causing the problem.

 

[jessebh2003]

Dang! Can't believe I missed that. Problem solved. I changed the range to the table column. Thanks, @Fluff!!

 

[Fluff]

You're welcome & thanks for the feedback.