Addition to INDEX formula to choose which table to reference

[bfuentes1412]

Hello. Looking to have the formulas in cells D5, E5, & K5 reference Table 1 or Table 2 based on the 'ZONE'. I tried combining both into one LONG table however, I can't figure out how to get the formula to look for 1, 3, 5, OR 7 independently for the 'ZONE' in table 1. If I need to keep the tables separate I'm totally good with that; combing would also be ok if need be. FYI, all formulas only reference table 1 with the exception of using info in column 'U' as criteria.

The goal is to place a drop down for the zone and have a selection for "1, 3, 5, 7, 9" and another drop down for EV "Yes" or "No". Based on those two choices the formulas in D5, E5 & K5 would use the appropriate table for the calculations. In the example, If Zone 3 is chosen, EV is 'NO', and the customer # is 47 the formulas should pull info from U57:Z57, as shown.

Please let me know if more info is needed. THANK YOU!

xfmr calculator.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
4	ZONE	EV	Adjust Design ft(2)	XFMR SIZE	Total # of Allowable Cust.	# of Customers	Additional Load	KVA PER CUST	MAX DWELLING UTILIZATION	TOTAL LOAD	Recommended Xfrm Size			ZONE	EV	sqft	xfmr	cust #	REMOVAL POINT		ZONE	EV	sqft	xfmr	cust #	REMOVAL POINT
5	3	NO	2600	75	81	47	10	1.11	52.22	62.22	75			9	YES	600	75	21	90		1, 3, 5, 7	YES	600	75	23	90
6														9	YES	600	150	42	180		1, 3, 5, 7	YES	600	150	46	180
7														9	YES	600	300	84	36		1, 3, 5, 7	YES	600	300	92	36
8														9	YES	600	500	140	600		1, 3, 5, 7	YES	600	500	153	600
9														9	YES	800	75	20	90		1, 3, 5, 7	YES	800	75	22	90
10														9	YES	800	150	40	180		1, 3, 5, 7	YES	800	150	44	180
11														9	YES	800	300	80	36		1, 3, 5, 7	YES	800	300	88	36
12														9	YES	800	500	133	600		1, 3, 5, 7	YES	800	500	147	600
13														9	YES	1000	75	20	90		1, 3, 5, 7	YES	1000	75	22	90
14														9	YES	1000	150	40	180		1, 3, 5, 7	YES	1000	150	44	180
15														9	YES	1000	300	80	36		1, 3, 5, 7	YES	1000	300	88	36
16														9	YES	1000	500	133	600		1, 3, 5, 7	YES	1000	500	147	600
17														9	YES	1200	75	20	90		1, 3, 5, 7	YES	1200	75	21	90
18														9	YES	1200	150	40	180		1, 3, 5, 7	YES	1200	150	42	180
19														9	YES	1200	300	80	36		1, 3, 5, 7	YES	1200	300	84	36
20														9	YES	1200	500	133	600		1, 3, 5, 7	YES	1200	500	140	600
21														9	YES	1400	75	19	90		1, 3, 5, 7	YES	1400	75	21	90
22														9	YES	1400	150	38	180		1, 3, 5, 7	YES	1400	150	42	180
23														9	YES	1400	300	76	36		1, 3, 5, 7	YES	1400	300	84	36
24														9	YES	1400	500	127	600		1, 3, 5, 7	YES	1400	500	140	600
25														9	YES	1600	75	19	90		1, 3, 5, 7	YES	1600	75	21	90
26														9	YES	1600	150	38	180		1, 3, 5, 7	YES	1600	150	42	180
27														9	YES	1600	300	76	36		1, 3, 5, 7	YES	1600	300	84	36
28														9	YES	1600	500	127	600		1, 3, 5, 7	YES	1600	500	140	600
29														9	YES	1800	75	19	90		1, 3, 5, 7	YES	1800	75	20	90
30														9	YES	1800	150	38	180		1, 3, 5, 7	YES	1800	150	40	180
31														9	YES	1800	300	76	36		1, 3, 5, 7	YES	1800	300	80	36
32														9	YES	1800	500	127	600		1, 3, 5, 7	YES	1800	500	133	600
33														9	YES	2000	75	18	90		1, 3, 5, 7	YES	2000	75	20	90
34														9	YES	2000	150	36	180		1, 3, 5, 7	YES	2000	150	40	180
35														9	YES	2000	300	72	36		1, 3, 5, 7	YES	2000	300	80	36
36														9	YES	2000	500	120	600		1, 3, 5, 7	YES	2000	500	133	600
37														9	YES	2200	75	18	90		1, 3, 5, 7	YES	2200	75	19	90
38														9	YES	2200	150	36	180		1, 3, 5, 7	YES	2200	150	38	180
39														9	YES	2200	300	72	36		1, 3, 5, 7	YES	2200	300	76	36
40														9	YES	2200	500	120	600		1, 3, 5, 7	YES	2200	500	127	600
41														9	YES	2400	75	17	90		1, 3, 5, 7	YES	2400	75	19	90
42														9	YES	2400	150	34	180		1, 3, 5, 7	YES	2400	150	38	180
43														9	YES	2400	300	68	36		1, 3, 5, 7	YES	2400	300	76	36
44														9	YES	2400	500	113	600		1, 3, 5, 7	YES	2400	500	127	600
45														9	YES	2600	75	15	90		1, 3, 5, 7	YES	2600	75	19	90
46														9	YES	2600	150	30	180		1, 3, 5, 7	YES	2600	150	38	180
47														9	YES	2600	300	60	36		1, 3, 5, 7	YES	2600	300	76	36
48														9	YES	2600	500	100	600		1, 3, 5, 7	YES	2600	500	127	600
49														9	NO	600	75	103	90		1, 3, 5, 7	NO	600	75	129	90
50														9	NO	600	150	206	180		1, 3, 5, 7	NO	600	150	258	180
51														9	NO	600	300	412	36		1, 3, 5, 7	NO	600	300	516	36
52														9	NO	600	500	687	600		1, 3, 5, 7	NO	600	500	860	600
53														9	NO	800	75	75	90		1, 3, 5, 7	NO	800	75	106	90
54														9	NO	800	150	150	180		1, 3, 5, 7	NO	800	150	212	180
55														9	NO	800	300	300	36		1, 3, 5, 7	NO	800	300	424	36
56														9	NO	800	500	500	600		1, 3, 5, 7	NO	800	500	707	600
57														9	NO	1000	75	67	90		1, 3, 5, 7	NO	1000	75	103	90
58														9	NO	1000	150	134	180		1, 3, 5, 7	NO	1000	150	206	180
59														9	NO	1000	300	268	36		1, 3, 5, 7	NO	1000	300	412	36
60														9	NO	1000	500	447	600		1, 3, 5, 7	NO	1000	500	687	600
61														9	NO	1200	75	65	90		1, 3, 5, 7	NO	1200	75	96	90
62														9	NO	1200	150	130	180		1, 3, 5, 7	NO	1200	150	192	180
63														9	NO	1200	300	260	36		1, 3, 5, 7	NO	1200	300	384	36
64														9	NO	1200	500	433	600		1, 3, 5, 7	NO	1200	500	640	600
65														9	NO	1400	75	63	600		1, 3, 5, 7	NO	1400	75	94	600
66														9	NO	1400	150	126	90		1, 3, 5, 7	NO	1400	150	188	90
67														9	NO	1400	300	252	180		1, 3, 5, 7	NO	1400	300	376	180
68														9	NO	1400	500	420	36		1, 3, 5, 7	NO	1400	500	627	36
69														9	NO	1600	75	56	600		1, 3, 5, 7	NO	1600	75	87	600
70														9	NO	1600	150	112	90		1, 3, 5, 7	NO	1600	150	174	90
71														9	NO	1600	300	224	180		1, 3, 5, 7	NO	1600	300	348	180
72														9	NO	1600	500	373	36		1, 3, 5, 7	NO	1600	500	580	36
73														9	NO	1800	75	50	600		1, 3, 5, 7	NO	1800	75	86	600
74														9	NO	1800	150	100	90		1, 3, 5, 7	NO	1800	150	172	90
75														9	NO	1800	300	200	180		1, 3, 5, 7	NO	1800	300	344	180
76														9	NO	1800	500	333	36		1, 3, 5, 7	NO	1800	500	573	36
77														9	NO	2000	75	49	600		1, 3, 5, 7	NO	2000	75	84	600
78														9	NO	2000	150	98	90		1, 3, 5, 7	NO	2000	150	168	90
79														9	NO	2000	300	196	180		1, 3, 5, 7	NO	2000	300	336	180
80														9	NO	2000	500	327	36		1, 3, 5, 7	NO	2000	500	560	36
81														9	NO	2200	75	48	600		1, 3, 5, 7	NO	2200	75	83	600
82														9	NO	2200	150	96	90		1, 3, 5, 7	NO	2200	150	166	90
83														9	NO	2200	300	192	180		1, 3, 5, 7	NO	2200	300	332	180
84														9	NO	2200	500	320	36		1, 3, 5, 7	NO	2200	500	553	36
85														9	NO	2400	75	42	600		1, 3, 5, 7	NO	2400	75	82	600
86														9	NO	2400	150	84	90		1, 3, 5, 7	NO	2400	150	164	90
87														9	NO	2400	300	168	180		1, 3, 5, 7	NO	2400	300	328	180
88														9	NO	2400	500	280	36		1, 3, 5, 7	NO	2400	500	547	36
89														9	NO	2600	75	40	600		1, 3, 5, 7	NO	2600	75	81	600
90														9	NO	2600	150	80	90		1, 3, 5, 7	NO	2600	150	162	90
91														9	NO	2600	300	160	180		1, 3, 5, 7	NO	2600	300	324	180
92														9	NO	2600	500	267	36		1, 3, 5, 7	NO	2600	500	540	36
Sheet6

Cell Formulas
Range	Formula
D5	D5	=INDEX(FILTER($X$5:$X$92,($V$5:$V$92=B5)*($W$5:$W$92=C5)*($Y$5:$Y$92>=F5)),1)
E5	E5	=INDEX($Y$5:$Y$92,MATCH(1,($W$5:$W$101=C5)*($V$5:$V$92=B5)*($X$5:$X$101=D5),0))
H5	H5	=SUM(D5)*1.2/E5
I5	I5	=SUM(H5)*F5
J5	J5	=SUM(I5+G5)
K5	K5	=IF(J5>SUMPRODUCT((Z5:Z92)*(C5=W5:W92)*(D5=X5:X92)*(E5=Y5:Y92)),INDEX(X5:X92,1+SUM((C5=W5:W92)*(D5=X5:X92)*(E5=Y5:Y92)*SEQUENCE(88))),D5)

 

[kevin9999]

I'm not sure that I fully understand your formulas, but why not stack one range on top of the other & adjust the formulas to match? Does this look right?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
4	ZONE	EV	Adjust Design ft(2)	XFMR SIZE	Total # of Allowable Cust.	# of Customers	Additional Load	KVA PER CUST	MAX DWELLING UTILIZATION	TOTAL LOAD	Recommended Xfrm Size			ZONE	EV	sqft	xfmr	cust #	REMOVAL POINT
5	9	NO	2600	75	81	47	10	1.11	52.22	62.22	75			1, 3, 5, 7	YES	600	75	23	90
6														1, 3, 5, 7	YES	600	150	46	180
7														1, 3, 5, 7	YES	600	300	92	36
8														1, 3, 5, 7	YES	600	500	153	600
9														1, 3, 5, 7	YES	800	75	22	90
10														1, 3, 5, 7	YES	800	150	44	180
11														1, 3, 5, 7	YES	800	300	88	36
12														1, 3, 5, 7	YES	800	500	147	600
13														1, 3, 5, 7	YES	1000	75	22	90
14														1, 3, 5, 7	YES	1000	150	44	180
15														1, 3, 5, 7	YES	1000	300	88	36
16														1, 3, 5, 7	YES	1000	500	147	600
17														1, 3, 5, 7	YES	1200	75	21	90
18														1, 3, 5, 7	YES	1200	150	42	180
19														1, 3, 5, 7	YES	1200	300	84	36
20														1, 3, 5, 7	YES	1200	500	140	600
21														1, 3, 5, 7	YES	1400	75	21	90
22														1, 3, 5, 7	YES	1400	150	42	180
23														1, 3, 5, 7	YES	1400	300	84	36
24														1, 3, 5, 7	YES	1400	500	140	600
25														1, 3, 5, 7	YES	1600	75	21	90
26														1, 3, 5, 7	YES	1600	150	42	180
27														1, 3, 5, 7	YES	1600	300	84	36
28														1, 3, 5, 7	YES	1600	500	140	600
29														1, 3, 5, 7	YES	1800	75	20	90
30														1, 3, 5, 7	YES	1800	150	40	180
31														1, 3, 5, 7	YES	1800	300	80	36
32														1, 3, 5, 7	YES	1800	500	133	600
33														1, 3, 5, 7	YES	2000	75	20	90
34														1, 3, 5, 7	YES	2000	150	40	180
35														1, 3, 5, 7	YES	2000	300	80	36
36														1, 3, 5, 7	YES	2000	500	133	600
37														1, 3, 5, 7	YES	2200	75	19	90
38														1, 3, 5, 7	YES	2200	150	38	180
39														1, 3, 5, 7	YES	2200	300	76	36
40														1, 3, 5, 7	YES	2200	500	127	600
41														1, 3, 5, 7	YES	2400	75	19	90
42														1, 3, 5, 7	YES	2400	150	38	180
43														1, 3, 5, 7	YES	2400	300	76	36
44														1, 3, 5, 7	YES	2400	500	127	600
45														1, 3, 5, 7	YES	2600	75	19	90
46														1, 3, 5, 7	YES	2600	150	38	180
47														1, 3, 5, 7	YES	2600	300	76	36
48														1, 3, 5, 7	YES	2600	500	127	600
49														1, 3, 5, 7	NO	600	75	129	90
50														1, 3, 5, 7	NO	600	150	258	180
51														1, 3, 5, 7	NO	600	300	516	36
52														1, 3, 5, 7	NO	600	500	860	600
53														1, 3, 5, 7	NO	800	75	106	90
54														1, 3, 5, 7	NO	800	150	212	180
55														1, 3, 5, 7	NO	800	300	424	36
56														1, 3, 5, 7	NO	800	500	707	600
57														1, 3, 5, 7	NO	1000	75	103	90
58														1, 3, 5, 7	NO	1000	150	206	180
59														1, 3, 5, 7	NO	1000	300	412	36
60														1, 3, 5, 7	NO	1000	500	687	600
61														1, 3, 5, 7	NO	1200	75	96	90
62														1, 3, 5, 7	NO	1200	150	192	180
63														1, 3, 5, 7	NO	1200	300	384	36
64														1, 3, 5, 7	NO	1200	500	640	600
65														1, 3, 5, 7	NO	1400	75	94	600
66														1, 3, 5, 7	NO	1400	150	188	90
67														1, 3, 5, 7	NO	1400	300	376	180
68														1, 3, 5, 7	NO	1400	500	627	36
69														1, 3, 5, 7	NO	1600	75	87	600
70														1, 3, 5, 7	NO	1600	150	174	90
71														1, 3, 5, 7	NO	1600	300	348	180
72														1, 3, 5, 7	NO	1600	500	580	36
73														1, 3, 5, 7	NO	1800	75	86	600
74														1, 3, 5, 7	NO	1800	150	172	90
75														1, 3, 5, 7	NO	1800	300	344	180
76														1, 3, 5, 7	NO	1800	500	573	36
77														1, 3, 5, 7	NO	2000	75	84	600
78														1, 3, 5, 7	NO	2000	150	168	90
79														1, 3, 5, 7	NO	2000	300	336	180
80														1, 3, 5, 7	NO	2000	500	560	36
81														1, 3, 5, 7	NO	2200	75	83	600
82														1, 3, 5, 7	NO	2200	150	166	90
83														1, 3, 5, 7	NO	2200	300	332	180
84														1, 3, 5, 7	NO	2200	500	553	36
85														1, 3, 5, 7	NO	2400	75	82	600
86														1, 3, 5, 7	NO	2400	150	164	90
87														1, 3, 5, 7	NO	2400	300	328	180
88														1, 3, 5, 7	NO	2400	500	547	36
89														1, 3, 5, 7	NO	2600	75	81	600
90														1, 3, 5, 7	NO	2600	150	162	90
91														1, 3, 5, 7	NO	2600	300	324	180
92														1, 3, 5, 7	NO	2600	500	540	36
93														9	YES	600	75	21	90
94														9	YES	600	150	42	180
95														9	YES	600	300	84	36
96														9	YES	600	500	140	600
97														9	YES	800	75	20	90
98														9	YES	800	150	40	180
99														9	YES	800	300	80	36
100														9	YES	800	500	133	600
101														9	YES	1000	75	20	90
102														9	YES	1000	150	40	180
103														9	YES	1000	300	80	36
104														9	YES	1000	500	133	600
105														9	YES	1200	75	20	90
106														9	YES	1200	150	40	180
107														9	YES	1200	300	80	36
108														9	YES	1200	500	133	600
109														9	YES	1400	75	19	90
110														9	YES	1400	150	38	180
111														9	YES	1400	300	76	36
112														9	YES	1400	500	127	600
Sheet2

Cell Formulas
Range	Formula
D5	D5	=INDEX(FILTER($Q$5:$Q$180,($O$5:$O$180=B5)*($P$5:$P$180=C5)*($R$5:$R$180>=F5)),1)
E5	E5	=INDEX($R$5:$R$95,MATCH(1,($P$5:$P$104=C5)*($O$5:$O$95=B5)*($Q$5:$Q$104=D5),0))
H5	H5	=SUM(D5)*1.2/E5
I5	I5	=SUM(H5)*F5
J5	J5	=SUM(I5+G5)
K5	K5	=IF(J5>SUMPRODUCT((S5:S180)*(C5=P5:P180)*(D5=Q5:Q180)*(E5=R5:R180)),INDEX(Q5:Q180,1+SUM((C5=P5:P180)*(D5=Q5:Q180)*(E5=R5:R180)*SEQUENCE(176))),D5)

Cells with Data Validation
Cell	Allow	Criteria
A5	List	1,3,5,7,9
B5	List	YES,NO

 

[kevin9999]

Actually, I can see that it would make a difference. Here's another option:

OxiXL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
4	ZONE	EV	Adjust Design ft(2)	XFMR SIZE	Total # of Allowable Cust.	# of Customers	Additional Load	KVA PER CUST	MAX DWELLING UTILIZATION	TOTAL LOAD	Recommended Xfrm Size			ZONE	EV	sqft	xfmr	cust #	REMOVAL POINT		ZONE	EV	sqft	xfmr	cust #	REMOVAL POINT
5	9	YES	2600	300	60	47	10	6.00	282.00	292.00	500			9	YES	600	75	21	90		1, 3, 5, 7	YES	600	75	23	90
6														9	YES	600	150	42	180		1, 3, 5, 7	YES	600	150	46	180
7														9	YES	600	300	84	36		1, 3, 5, 7	YES	600	300	92	36
8														9	YES	600	500	140	600		1, 3, 5, 7	YES	600	500	153	600
9														9	YES	800	75	20	90		1, 3, 5, 7	YES	800	75	22	90
10														9	YES	800	150	40	180		1, 3, 5, 7	YES	800	150	44	180
11														9	YES	800	300	80	36		1, 3, 5, 7	YES	800	300	88	36
12														9	YES	800	500	133	600		1, 3, 5, 7	YES	800	500	147	600
13														9	YES	1000	75	20	90		1, 3, 5, 7	YES	1000	75	22	90
14														9	YES	1000	150	40	180		1, 3, 5, 7	YES	1000	150	44	180
15														9	YES	1000	300	80	36		1, 3, 5, 7	YES	1000	300	88	36
16														9	YES	1000	500	133	600		1, 3, 5, 7	YES	1000	500	147	600
17														9	YES	1200	75	20	90		1, 3, 5, 7	YES	1200	75	21	90
18														9	YES	1200	150	40	180		1, 3, 5, 7	YES	1200	150	42	180
19														9	YES	1200	300	80	36		1, 3, 5, 7	YES	1200	300	84	36
20														9	YES	1200	500	133	600		1, 3, 5, 7	YES	1200	500	140	600
21														9	YES	1400	75	19	90		1, 3, 5, 7	YES	1400	75	21	90
22														9	YES	1400	150	38	180		1, 3, 5, 7	YES	1400	150	42	180
23														9	YES	1400	300	76	36		1, 3, 5, 7	YES	1400	300	84	36
24														9	YES	1400	500	127	600		1, 3, 5, 7	YES	1400	500	140	600
25														9	YES	1600	75	19	90		1, 3, 5, 7	YES	1600	75	21	90
26														9	YES	1600	150	38	180		1, 3, 5, 7	YES	1600	150	42	180
27														9	YES	1600	300	76	36		1, 3, 5, 7	YES	1600	300	84	36
28														9	YES	1600	500	127	600		1, 3, 5, 7	YES	1600	500	140	600
29														9	YES	1800	75	19	90		1, 3, 5, 7	YES	1800	75	20	90
30														9	YES	1800	150	38	180		1, 3, 5, 7	YES	1800	150	40	180
31														9	YES	1800	300	76	36		1, 3, 5, 7	YES	1800	300	80	36
32														9	YES	1800	500	127	600		1, 3, 5, 7	YES	1800	500	133	600
33														9	YES	2000	75	18	90		1, 3, 5, 7	YES	2000	75	20	90
34														9	YES	2000	150	36	180		1, 3, 5, 7	YES	2000	150	40	180
35														9	YES	2000	300	72	36		1, 3, 5, 7	YES	2000	300	80	36
36														9	YES	2000	500	120	600		1, 3, 5, 7	YES	2000	500	133	600
37														9	YES	2200	75	18	90		1, 3, 5, 7	YES	2200	75	19	90
38														9	YES	2200	150	36	180		1, 3, 5, 7	YES	2200	150	38	180
39														9	YES	2200	300	72	36		1, 3, 5, 7	YES	2200	300	76	36
40														9	YES	2200	500	120	600		1, 3, 5, 7	YES	2200	500	127	600
41														9	YES	2400	75	17	90		1, 3, 5, 7	YES	2400	75	19	90
42														9	YES	2400	150	34	180		1, 3, 5, 7	YES	2400	150	38	180
43														9	YES	2400	300	68	36		1, 3, 5, 7	YES	2400	300	76	36
44														9	YES	2400	500	113	600		1, 3, 5, 7	YES	2400	500	127	600
45														9	YES	2600	75	15	90		1, 3, 5, 7	YES	2600	75	19	90
46														9	YES	2600	150	30	180		1, 3, 5, 7	YES	2600	150	38	180
47														9	YES	2600	300	60	36		1, 3, 5, 7	YES	2600	300	76	36
48														9	YES	2600	500	100	600		1, 3, 5, 7	YES	2600	500	127	600
49														9	NO	600	75	103	90		1, 3, 5, 7	NO	600	75	129	90
50														9	NO	600	150	206	180		1, 3, 5, 7	NO	600	150	258	180
51														9	NO	600	300	412	36		1, 3, 5, 7	NO	600	300	516	36
52														9	NO	600	500	687	600		1, 3, 5, 7	NO	600	500	860	600
53														9	NO	800	75	75	90		1, 3, 5, 7	NO	800	75	106	90
54														9	NO	800	150	150	180		1, 3, 5, 7	NO	800	150	212	180
55														9	NO	800	300	300	36		1, 3, 5, 7	NO	800	300	424	36
56														9	NO	800	500	500	600		1, 3, 5, 7	NO	800	500	707	600
57														9	NO	1000	75	67	90		1, 3, 5, 7	NO	1000	75	103	90
58														9	NO	1000	150	134	180		1, 3, 5, 7	NO	1000	150	206	180
59														9	NO	1000	300	268	36		1, 3, 5, 7	NO	1000	300	412	36
60														9	NO	1000	500	447	600		1, 3, 5, 7	NO	1000	500	687	600
61														9	NO	1200	75	65	90		1, 3, 5, 7	NO	1200	75	96	90
62														9	NO	1200	150	130	180		1, 3, 5, 7	NO	1200	150	192	180
63														9	NO	1200	300	260	36		1, 3, 5, 7	NO	1200	300	384	36
64														9	NO	1200	500	433	600		1, 3, 5, 7	NO	1200	500	640	600
65														9	NO	1400	75	63	600		1, 3, 5, 7	NO	1400	75	94	600
66														9	NO	1400	150	126	90		1, 3, 5, 7	NO	1400	150	188	90
67														9	NO	1400	300	252	180		1, 3, 5, 7	NO	1400	300	376	180
68														9	NO	1400	500	420	36		1, 3, 5, 7	NO	1400	500	627	36
69														9	NO	1600	75	56	600		1, 3, 5, 7	NO	1600	75	87	600
70														9	NO	1600	150	112	90		1, 3, 5, 7	NO	1600	150	174	90
71														9	NO	1600	300	224	180		1, 3, 5, 7	NO	1600	300	348	180
72														9	NO	1600	500	373	36		1, 3, 5, 7	NO	1600	500	580	36
73														9	NO	1800	75	50	600		1, 3, 5, 7	NO	1800	75	86	600
74														9	NO	1800	150	100	90		1, 3, 5, 7	NO	1800	150	172	90
75														9	NO	1800	300	200	180		1, 3, 5, 7	NO	1800	300	344	180
76														9	NO	1800	500	333	36		1, 3, 5, 7	NO	1800	500	573	36
77														9	NO	2000	75	49	600		1, 3, 5, 7	NO	2000	75	84	600
78														9	NO	2000	150	98	90		1, 3, 5, 7	NO	2000	150	168	90
79														9	NO	2000	300	196	180		1, 3, 5, 7	NO	2000	300	336	180
80														9	NO	2000	500	327	36		1, 3, 5, 7	NO	2000	500	560	36
81														9	NO	2200	75	48	600		1, 3, 5, 7	NO	2200	75	83	600
82														9	NO	2200	150	96	90		1, 3, 5, 7	NO	2200	150	166	90
83														9	NO	2200	300	192	180		1, 3, 5, 7	NO	2200	300	332	180
84														9	NO	2200	500	320	36		1, 3, 5, 7	NO	2200	500	553	36
85														9	NO	2400	75	42	600		1, 3, 5, 7	NO	2400	75	82	600
86														9	NO	2400	150	84	90		1, 3, 5, 7	NO	2400	150	164	90
87														9	NO	2400	300	168	180		1, 3, 5, 7	NO	2400	300	328	180
88														9	NO	2400	500	280	36		1, 3, 5, 7	NO	2400	500	547	36
89														9	NO	2600	75	40	600		1, 3, 5, 7	NO	2600	75	81	600
90														9	NO	2600	150	80	90		1, 3, 5, 7	NO	2600	150	162	90
91														9	NO	2600	300	160	180		1, 3, 5, 7	NO	2600	300	324	180
92														9	NO	2600	500	267	36		1, 3, 5, 7	NO	2600	500	540	36
Sheet3

Cell Formulas
Range	Formula
D5	D5	=IF(A5<9,INDEX(FILTER($X$5:$X$92,($V$5:$V$92=B5)*($W$5:$W$92=C5)*($Y$5:$Y$92>=F5)),1),INDEX(FILTER($Q$5:$Q$92,($O$5:$O$92=B5)*($P$5:$P$92=C5)*($R$5:$R$92>=F5)),1))
E5	E5	=IF(A5<9,INDEX($Y$5:$Y$92,MATCH(1,($W$5:$W$101=C5)*($V$5:$V$92=B5)*($X$5:$X$101=D5),0)),INDEX($R$5:$R$92,MATCH(1,($P$5:$P$101=C5)*($O$5:$O$92=B5)*($Q$5:$Q$101=D5),0)))
H5	H5	=SUM(D5)*1.2/E5
I5	I5	=SUM(H5)*F5
J5	J5	=SUM(I5+G5)
K5	K5	=IF(A5<9,IF(J5>SUMPRODUCT((Z5:Z92)*(C5=W5:W92)*(D5=X5:X92)*(E5=Y5:Y92)),INDEX(X5:X92,1+SUM((C5=W5:W92)*(D5=X5:X92)*(E5=Y5:Y92)*SEQUENCE(88))),D5),IF(J5>SUMPRODUCT((S5:S92)*(C5=P5:P92)*(D5=Q5:Q92)*(E5=R5:R92)),INDEX(Q5:Q92,1+SUM((C5=P5:P92)*(D5=Q5:Q92)*(E5=R5:R92)*SEQUENCE(88))),D5))

Cells with Data Validation
Cell	Allow	Criteria
A5	List	1,3,5,7,9
B5	List	YES,NO

 

[bfuentes1412]

That worked! Thank you! I'm sure I'm doing things the "long" way with this. Can this be simplified using a macro or VBA?

 

[kevin9999]

Can this be simplified using a macro or VBA?

Interesting question - yes, and no. Better off without macros if you can as it adds another level of complexity to your project (having to save in a certain format, other users might have security levels set high blocking macros, more complicated to maintain/adjust etc.) I'm sure there's a simpler formula solution, but formulas are not my thing - hopefully someone else can come up with a simpler solution.

 

[bfuentes1412]

No worries. Thanks again for the help. Much appreciated!

 

[Peter_SSs]

I'm not quite sure what the K5 formula is actually trying to achieve, but here are a couple of shorter ones that I think do what you want for D5 & E5

23 04 16.xlsm
	A	B	C	D	E	F							M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
4	ZONE	EV	Adjust Design ft(2)	XFMR SIZE	Total # of Allowable Cust.	# of Customers								ZONE	EV	sqft	xfmr	cust #	REMOVAL POINT		ZONE	EV	sqft	xfmr	cust #	REMOVAL POINT
5	3	NO	2600	75	81	47								9	YES	600	75	21	90		1, 3, 5, 7	YES	600	75	23	90
6														9	YES	600	150	42	180		1, 3, 5, 7	YES	600	150	46	180
7														9	YES	600	300	84	36		1, 3, 5, 7	YES	600	300	92	36
8														9	YES	600	500	140	600		1, 3, 5, 7	YES	600	500	153	600
9														9	YES	800	75	20	90		1, 3, 5, 7	YES	800	75	22	90
10														9	YES	800	150	40	180		1, 3, 5, 7	YES	800	150	44	180
11														9	YES	800	300	80	36		1, 3, 5, 7	YES	800	300	88	36
12														9	YES	800	500	133	600		1, 3, 5, 7	YES	800	500	147	600
13														9	YES	1000	75	20	90		1, 3, 5, 7	YES	1000	75	22	90
14														9	YES	1000	150	40	180		1, 3, 5, 7	YES	1000	150	44	180
15														9	YES	1000	300	80	36		1, 3, 5, 7	YES	1000	300	88	36
16														9	YES	1000	500	133	600		1, 3, 5, 7	YES	1000	500	147	600
17														9	YES	1200	75	20	90		1, 3, 5, 7	YES	1200	75	21	90
18														9	YES	1200	150	40	180		1, 3, 5, 7	YES	1200	150	42	180
19														9	YES	1200	300	80	36		1, 3, 5, 7	YES	1200	300	84	36
20														9	YES	1200	500	133	600		1, 3, 5, 7	YES	1200	500	140	600
21														9	YES	1400	75	19	90		1, 3, 5, 7	YES	1400	75	21	90
22														9	YES	1400	150	38	180		1, 3, 5, 7	YES	1400	150	42	180
23														9	YES	1400	300	76	36		1, 3, 5, 7	YES	1400	300	84	36
24														9	YES	1400	500	127	600		1, 3, 5, 7	YES	1400	500	140	600
25														9	YES	1600	75	19	90		1, 3, 5, 7	YES	1600	75	21	90
26														9	YES	1600	150	38	180		1, 3, 5, 7	YES	1600	150	42	180
27														9	YES	1600	300	76	36		1, 3, 5, 7	YES	1600	300	84	36
28														9	YES	1600	500	127	600		1, 3, 5, 7	YES	1600	500	140	600
29														9	YES	1800	75	19	90		1, 3, 5, 7	YES	1800	75	20	90
30														9	YES	1800	150	38	180		1, 3, 5, 7	YES	1800	150	40	180
31														9	YES	1800	300	76	36		1, 3, 5, 7	YES	1800	300	80	36
32														9	YES	1800	500	127	600		1, 3, 5, 7	YES	1800	500	133	600
33														9	YES	2000	75	18	90		1, 3, 5, 7	YES	2000	75	20	90
34														9	YES	2000	150	36	180		1, 3, 5, 7	YES	2000	150	40	180
35														9	YES	2000	300	72	36		1, 3, 5, 7	YES	2000	300	80	36
36														9	YES	2000	500	120	600		1, 3, 5, 7	YES	2000	500	133	600
37														9	YES	2200	75	18	90		1, 3, 5, 7	YES	2200	75	19	90
38														9	YES	2200	150	36	180		1, 3, 5, 7	YES	2200	150	38	180
39														9	YES	2200	300	72	36		1, 3, 5, 7	YES	2200	300	76	36
40														9	YES	2200	500	120	600		1, 3, 5, 7	YES	2200	500	127	600
41														9	YES	2400	75	17	90		1, 3, 5, 7	YES	2400	75	19	90
42														9	YES	2400	150	34	180		1, 3, 5, 7	YES	2400	150	38	180
43														9	YES	2400	300	68	36		1, 3, 5, 7	YES	2400	300	76	36
44														9	YES	2400	500	113	600		1, 3, 5, 7	YES	2400	500	127	600
45														9	YES	2600	75	15	90		1, 3, 5, 7	YES	2600	75	19	90
46														9	YES	2600	150	30	180		1, 3, 5, 7	YES	2600	150	38	180
47														9	YES	2600	300	60	36		1, 3, 5, 7	YES	2600	300	76	36
48														9	YES	2600	500	100	600		1, 3, 5, 7	YES	2600	500	127	600
49														9	NO	600	75	103	90		1, 3, 5, 7	NO	600	75	129	90
50														9	NO	600	150	206	180		1, 3, 5, 7	NO	600	150	258	180
51														9	NO	600	300	412	36		1, 3, 5, 7	NO	600	300	516	36
52														9	NO	600	500	687	600		1, 3, 5, 7	NO	600	500	860	600
53														9	NO	800	75	75	90		1, 3, 5, 7	NO	800	75	106	90
54														9	NO	800	150	150	180		1, 3, 5, 7	NO	800	150	212	180
55														9	NO	800	300	300	36		1, 3, 5, 7	NO	800	300	424	36
56														9	NO	800	500	500	600		1, 3, 5, 7	NO	800	500	707	600
57														9	NO	1000	75	67	90		1, 3, 5, 7	NO	1000	75	103	90
58														9	NO	1000	150	134	180		1, 3, 5, 7	NO	1000	150	206	180
59														9	NO	1000	300	268	36		1, 3, 5, 7	NO	1000	300	412	36
60														9	NO	1000	500	447	600		1, 3, 5, 7	NO	1000	500	687	600
61														9	NO	1200	75	65	90		1, 3, 5, 7	NO	1200	75	96	90
62														9	NO	1200	150	130	180		1, 3, 5, 7	NO	1200	150	192	180
63														9	NO	1200	300	260	36		1, 3, 5, 7	NO	1200	300	384	36
64														9	NO	1200	500	433	600		1, 3, 5, 7	NO	1200	500	640	600
65														9	NO	1400	75	63	600		1, 3, 5, 7	NO	1400	75	94	600
66														9	NO	1400	150	126	90		1, 3, 5, 7	NO	1400	150	188	90
67														9	NO	1400	300	252	180		1, 3, 5, 7	NO	1400	300	376	180
68														9	NO	1400	500	420	36		1, 3, 5, 7	NO	1400	500	627	36
69														9	NO	1600	75	56	600		1, 3, 5, 7	NO	1600	75	87	600
70														9	NO	1600	150	112	90		1, 3, 5, 7	NO	1600	150	174	90
71														9	NO	1600	300	224	180		1, 3, 5, 7	NO	1600	300	348	180
72														9	NO	1600	500	373	36		1, 3, 5, 7	NO	1600	500	580	36
73														9	NO	1800	75	50	600		1, 3, 5, 7	NO	1800	75	86	600
74														9	NO	1800	150	100	90		1, 3, 5, 7	NO	1800	150	172	90
75														9	NO	1800	300	200	180		1, 3, 5, 7	NO	1800	300	344	180
76														9	NO	1800	500	333	36		1, 3, 5, 7	NO	1800	500	573	36
77														9	NO	2000	75	49	600		1, 3, 5, 7	NO	2000	75	84	600
78														9	NO	2000	150	98	90		1, 3, 5, 7	NO	2000	150	168	90
79														9	NO	2000	300	196	180		1, 3, 5, 7	NO	2000	300	336	180
80														9	NO	2000	500	327	36		1, 3, 5, 7	NO	2000	500	560	36
81														9	NO	2200	75	48	600		1, 3, 5, 7	NO	2200	75	83	600
82														9	NO	2200	150	96	90		1, 3, 5, 7	NO	2200	150	166	90
83														9	NO	2200	300	192	180		1, 3, 5, 7	NO	2200	300	332	180
84														9	NO	2200	500	320	36		1, 3, 5, 7	NO	2200	500	553	36
85														9	NO	2400	75	42	600		1, 3, 5, 7	NO	2400	75	82	600
86														9	NO	2400	150	84	90		1, 3, 5, 7	NO	2400	150	164	90
87														9	NO	2400	300	168	180		1, 3, 5, 7	NO	2400	300	328	180
88														9	NO	2400	500	280	36		1, 3, 5, 7	NO	2400	500	547	36
89														9	NO	2600	75	40	600		1, 3, 5, 7	NO	2600	75	81	600
90														9	NO	2600	150	80	90		1, 3, 5, 7	NO	2600	150	162	90
91														9	NO	2600	300	160	180		1, 3, 5, 7	NO	2600	300	324	180
92														9	NO	2600	500	267	36		1, 3, 5, 7	NO	2600	500	540	36
bfuentes1412

Cell Formulas
Range	Formula
D5	D5	=LET(T,IF(A5=9,O5:R92,V5:Y92),INDEX(FILTER(INDEX(T,0,3),(INDEX(T,0,1)=B5)*(INDEX(T,0,2)=C5)*(INDEX(T,0,4)>=F5)),1))
E5	E5	=LET(T,IF(A5=9,O5:R92,V5:Y92),INDEX(FILTER(INDEX(T,0,4),(INDEX(T,0,1)=B5)*(INDEX(T,0,2)=C5)*(INDEX(T,0,4)>=F5)),1))

 

[bfuentes1412]

Thanks Peter, appreciate the help! K5 is validating if the original transformer size, 'D5', is still within the "removal point" based on the total load and if it's not, recommends the appropriate size. The original transformer size in D5 is only based on the design sqft, the amount of proposed customers, zone, and EV.

 

[bfuentes1412]

Actually, I can see that it would make a difference. Here's another option:

OxiXL.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
4	ZONE	EV	Adjust Design ft(2)	XFMR SIZE	Total # of Allowable Cust.	# of Customers	Additional Load	KVA PER CUST	MAX DWELLING UTILIZATION	TOTAL LOAD	Recommended Xfrm Size			ZONE	EV	sqft	xfmr	cust #	REMOVAL POINT		ZONE	EV	sqft	xfmr	cust #	REMOVAL POINT
5	9	YES	2600	300	60	47	10	6.00	282.00	292.00	500			9	YES	600	75	21	90		1, 3, 5, 7	YES	600	75	23	90
6														9	YES	600	150	42	180		1, 3, 5, 7	YES	600	150	46	180
7														9	YES	600	300	84	36		1, 3, 5, 7	YES	600	300	92	36
8														9	YES	600	500	140	600		1, 3, 5, 7	YES	600	500	153	600
9														9	YES	800	75	20	90		1, 3, 5, 7	YES	800	75	22	90
10														9	YES	800	150	40	180		1, 3, 5, 7	YES	800	150	44	180
11														9	YES	800	300	80	36		1, 3, 5, 7	YES	800	300	88	36
12														9	YES	800	500	133	600		1, 3, 5, 7	YES	800	500	147	600
13														9	YES	1000	75	20	90		1, 3, 5, 7	YES	1000	75	22	90
14														9	YES	1000	150	40	180		1, 3, 5, 7	YES	1000	150	44	180
15														9	YES	1000	300	80	36		1, 3, 5, 7	YES	1000	300	88	36
16														9	YES	1000	500	133	600		1, 3, 5, 7	YES	1000	500	147	600
17														9	YES	1200	75	20	90		1, 3, 5, 7	YES	1200	75	21	90
18														9	YES	1200	150	40	180		1, 3, 5, 7	YES	1200	150	42	180
19														9	YES	1200	300	80	36		1, 3, 5, 7	YES	1200	300	84	36
20														9	YES	1200	500	133	600		1, 3, 5, 7	YES	1200	500	140	600
21														9	YES	1400	75	19	90		1, 3, 5, 7	YES	1400	75	21	90
22														9	YES	1400	150	38	180		1, 3, 5, 7	YES	1400	150	42	180
23														9	YES	1400	300	76	36		1, 3, 5, 7	YES	1400	300	84	36
24														9	YES	1400	500	127	600		1, 3, 5, 7	YES	1400	500	140	600
25														9	YES	1600	75	19	90		1, 3, 5, 7	YES	1600	75	21	90
26														9	YES	1600	150	38	180		1, 3, 5, 7	YES	1600	150	42	180
27														9	YES	1600	300	76	36		1, 3, 5, 7	YES	1600	300	84	36
28														9	YES	1600	500	127	600		1, 3, 5, 7	YES	1600	500	140	600
29														9	YES	1800	75	19	90		1, 3, 5, 7	YES	1800	75	20	90
30														9	YES	1800	150	38	180		1, 3, 5, 7	YES	1800	150	40	180
31														9	YES	1800	300	76	36		1, 3, 5, 7	YES	1800	300	80	36
32														9	YES	1800	500	127	600		1, 3, 5, 7	YES	1800	500	133	600
33														9	YES	2000	75	18	90		1, 3, 5, 7	YES	2000	75	20	90
34														9	YES	2000	150	36	180		1, 3, 5, 7	YES	2000	150	40	180
35														9	YES	2000	300	72	36		1, 3, 5, 7	YES	2000	300	80	36
36														9	YES	2000	500	120	600		1, 3, 5, 7	YES	2000	500	133	600
37														9	YES	2200	75	18	90		1, 3, 5, 7	YES	2200	75	19	90
38														9	YES	2200	150	36	180		1, 3, 5, 7	YES	2200	150	38	180
39														9	YES	2200	300	72	36		1, 3, 5, 7	YES	2200	300	76	36
40														9	YES	2200	500	120	600		1, 3, 5, 7	YES	2200	500	127	600
41														9	YES	2400	75	17	90		1, 3, 5, 7	YES	2400	75	19	90
42														9	YES	2400	150	34	180		1, 3, 5, 7	YES	2400	150	38	180
43														9	YES	2400	300	68	36		1, 3, 5, 7	YES	2400	300	76	36
44														9	YES	2400	500	113	600		1, 3, 5, 7	YES	2400	500	127	600
45														9	YES	2600	75	15	90		1, 3, 5, 7	YES	2600	75	19	90
46														9	YES	2600	150	30	180		1, 3, 5, 7	YES	2600	150	38	180
47														9	YES	2600	300	60	36		1, 3, 5, 7	YES	2600	300	76	36
48														9	YES	2600	500	100	600		1, 3, 5, 7	YES	2600	500	127	600
49														9	NO	600	75	103	90		1, 3, 5, 7	NO	600	75	129	90
50														9	NO	600	150	206	180		1, 3, 5, 7	NO	600	150	258	180
51														9	NO	600	300	412	36		1, 3, 5, 7	NO	600	300	516	36
52														9	NO	600	500	687	600		1, 3, 5, 7	NO	600	500	860	600
53														9	NO	800	75	75	90		1, 3, 5, 7	NO	800	75	106	90
54														9	NO	800	150	150	180		1, 3, 5, 7	NO	800	150	212	180
55														9	NO	800	300	300	36		1, 3, 5, 7	NO	800	300	424	36
56														9	NO	800	500	500	600		1, 3, 5, 7	NO	800	500	707	600
57														9	NO	1000	75	67	90		1, 3, 5, 7	NO	1000	75	103	90
58														9	NO	1000	150	134	180		1, 3, 5, 7	NO	1000	150	206	180
59														9	NO	1000	300	268	36		1, 3, 5, 7	NO	1000	300	412	36
60														9	NO	1000	500	447	600		1, 3, 5, 7	NO	1000	500	687	600
61														9	NO	1200	75	65	90		1, 3, 5, 7	NO	1200	75	96	90
62														9	NO	1200	150	130	180		1, 3, 5, 7	NO	1200	150	192	180
63														9	NO	1200	300	260	36		1, 3, 5, 7	NO	1200	300	384	36
64														9	NO	1200	500	433	600		1, 3, 5, 7	NO	1200	500	640	600
65														9	NO	1400	75	63	600		1, 3, 5, 7	NO	1400	75	94	600
66														9	NO	1400	150	126	90		1, 3, 5, 7	NO	1400	150	188	90
67														9	NO	1400	300	252	180		1, 3, 5, 7	NO	1400	300	376	180
68														9	NO	1400	500	420	36		1, 3, 5, 7	NO	1400	500	627	36
69														9	NO	1600	75	56	600		1, 3, 5, 7	NO	1600	75	87	600
70														9	NO	1600	150	112	90		1, 3, 5, 7	NO	1600	150	174	90
71														9	NO	1600	300	224	180		1, 3, 5, 7	NO	1600	300	348	180
72														9	NO	1600	500	373	36		1, 3, 5, 7	NO	1600	500	580	36
73														9	NO	1800	75	50	600		1, 3, 5, 7	NO	1800	75	86	600
74														9	NO	1800	150	100	90		1, 3, 5, 7	NO	1800	150	172	90
75														9	NO	1800	300	200	180		1, 3, 5, 7	NO	1800	300	344	180
76														9	NO	1800	500	333	36		1, 3, 5, 7	NO	1800	500	573	36
77														9	NO	2000	75	49	600		1, 3, 5, 7	NO	2000	75	84	600
78														9	NO	2000	150	98	90		1, 3, 5, 7	NO	2000	150	168	90
79														9	NO	2000	300	196	180		1, 3, 5, 7	NO	2000	300	336	180
80														9	NO	2000	500	327	36		1, 3, 5, 7	NO	2000	500	560	36
81														9	NO	2200	75	48	600		1, 3, 5, 7	NO	2200	75	83	600
82														9	NO	2200	150	96	90		1, 3, 5, 7	NO	2200	150	166	90
83														9	NO	2200	300	192	180		1, 3, 5, 7	NO	2200	300	332	180
84														9	NO	2200	500	320	36		1, 3, 5, 7	NO	2200	500	553	36
85														9	NO	2400	75	42	600		1, 3, 5, 7	NO	2400	75	82	600
86														9	NO	2400	150	84	90		1, 3, 5, 7	NO	2400	150	164	90
87														9	NO	2400	300	168	180		1, 3, 5, 7	NO	2400	300	328	180
88														9	NO	2400	500	280	36		1, 3, 5, 7	NO	2400	500	547	36
89														9	NO	2600	75	40	600		1, 3, 5, 7	NO	2600	75	81	600
90														9	NO	2600	150	80	90		1, 3, 5, 7	NO	2600	150	162	90
91														9	NO	2600	300	160	180		1, 3, 5, 7	NO	2600	300	324	180
92														9	NO	2600	500	267	36		1, 3, 5, 7	NO	2600	500	540	36
Sheet3

Cell Formulas
Range	Formula
D5	D5	=IF(A5<9,INDEX(FILTER($X$5:$X$92,($V$5:$V$92=B5)*($W$5:$W$92=C5)*($Y$5:$Y$92>=F5)),1),INDEX(FILTER($Q$5:$Q$92,($O$5:$O$92=B5)*($P$5:$P$92=C5)*($R$5:$R$92>=F5)),1))
E5	E5	=IF(A5<9,INDEX($Y$5:$Y$92,MATCH(1,($W$5:$W$101=C5)*($V$5:$V$92=B5)*($X$5:$X$101=D5),0)),INDEX($R$5:$R$92,MATCH(1,($P$5:$P$101=C5)*($O$5:$O$92=B5)*($Q$5:$Q$101=D5),0)))
H5	H5	=SUM(D5)*1.2/E5
I5	I5	=SUM(H5)*F5
J5	J5	=SUM(I5+G5)
K5	K5	=IF(A5<9,IF(J5>SUMPRODUCT((Z5:Z92)*(C5=W5:W92)*(D5=X5:X92)*(E5=Y5:Y92)),INDEX(X5:X92,1+SUM((C5=W5:W92)*(D5=X5:X92)*(E5=Y5:Y92)*SEQUENCE(88))),D5),IF(J5>SUMPRODUCT((S5:S92)*(C5=P5:P92)*(D5=Q5:Q92)*(E5=R5:R92)),INDEX(Q5:Q92,1+SUM((C5=P5:P92)*(D5=Q5:Q92)*(E5=R5:R92)*SEQUENCE(88))),D5))

Cells with Data Validation
Cell	Allow	Criteria
A5	List	1,3,5,7,9
B5	List	YES,NO

Sorry! Thought it worked but it only populates the next size up. It should recommend the transformer size based on the value of the total load. So, if the original transformer is a 75kVA but the total load is say, 181kVA, the recommended transformer size should be 300 since 180 is the removal point for a 150kVA transformer. The formula you provided rounds a 75 to 150 and stops there, but if the value is above the removal for the 150 it stays at 150.

 

[kevin9999]

Sorry! Thought it worked but it only populates the next size up. It should recommend the transformer size based on the value of the total load. So, if the original transformer is a 75kVA but the total load is say, 181kVA, the recommended transformer size should be 300 since 180 is the removal point for a 150kVA transformer. The formula you provided rounds a 75 to 150 and stops there, but if the value is above the removal for the 150 it stays at 150.

I'm a bit lost on your formula, happy to leave you in the more capable hands of @Peter_SSs .