Product function with exponents

[Bgreen83]

Hello,
I'm trying to use the PRODUCT function to accurately rollup locations to a region level for each weighted geometric mean. The cells highlighted in yellow are zero values. I have not included those cells in the region rollup (blue cells). If I include those cells in my current calculations, I receive #NULL or zero. Is there are way to include those cells and still receive the correct values that are currently displayed?

GMLOS Calculation.xlsx
	A	B	C	D	E	F	G	H	I	J
4	Location	Metric	Detail	Month	Year	Year2	Actual	Total Cases	GMLOS O/E	GMLOS
5	Location A	Acute LOS OE	Premier Source	January	2022	Jan-22	Actual	242	0.76	2.81
6	Location B	Acute LOS OE	Premier Source	January	2022	Jan-22	Actual	353	0.77	3.22
7	Location C	Acute LOS OE	Premier Source	January	2022	Jan-22	Actual	41	0.84	3.10
8	Region A	Acute LOS OE	Premier Source	January	2022	Jan-22	Actual	1.32695826534905E-73	0.77
9	Location D	Acute LOS OE	Premier Source	January	2022	Jan-22	Actual	294	1.08	3.67
10	Location E	Acute LOS OE	Premier Source	January	2022	Jan-22	Actual	6	1.51	4.24
11	Location F	Acute LOS OE	Premier Source	January	2022	Jan-22	Actual	0	-	-
12	Region B	Acute LOS OE	Premier Source	January	2022	Jan-22	Actual	33187502300.8676	1.08
13	Location G	Acute LOS OE	Premier Source	January	2022	Jan-22	Actual	0	-	-
14	Location H	Acute LOS OE	Premier Source	January	2022	Jan-22	Actual	332	0.94	3.86
15	Location I	Acute LOS OE	Premier Source	January	2022	Jan-22	Actual	528	1.19	4.49
16	Location J	Acute LOS OE	Premier Source	January	2022	Jan-22	Actual	207	0.84	3.25
17	Region C	Acute LOS OE	Premier Source	January	2022	Jan-22	Actual	2891016317865320	1.03
GMLOS

Cell Formulas
Range	Formula
H5:H7,H9:H11,H13:H16	H5	=SUMIFS(Query1!$C:$C,Query1!$H:$H,VLOOKUP($A5,LOOKUPS!$B$2:$C$31,COLUMNS(LOOKUPS!$B$2:$C$2),0),Query1!$A:$A,VLOOKUP($F5,LOOKUPS!$Q$2:$R$40,COLUMNS(LOOKUPS!$Q$2:$R$2),0))
I5:I7,I9:I11,I13:I16	I5	=SUMIFS(Query1!$J:$J,Query1!$H:$H,VLOOKUP($A5,LOOKUPS!$B$2:$C$31,COLUMNS(LOOKUPS!$B$2:$C$2),0),Query1!$A:$A,VLOOKUP($F5,LOOKUPS!$Q$2:$R$40,COLUMNS(LOOKUPS!$Q$2:$R$2),0))
J5:J7,J9:J11,J13:J16	J5	=SUMIFS(Query1!$K:$K,Query1!$H:$H,VLOOKUP($A5,LOOKUPS!$B$2:$C$31,COLUMNS(LOOKUPS!$B$2:$C$2),0),Query1!$A:$A,VLOOKUP($F5,LOOKUPS!$Q$2:$R$40,COLUMNS(LOOKUPS!$Q$2:$R$2),0))
H8,H17	H8	=TRIM(PRODUCT($I5^$H5,$I6^$H6,$I7^$H7))
I8,I12,I17	I8	=H8^(1/SUM($H5:$H7))
H12	H12	=TRIM(PRODUCT($I9^$H9,$I10^$H10))
J12,J17	J12	=IFERROR(SUM(L12/N12),"")

Named Ranges
Name	Refers To	Cells
Query1!ExternalData_1	=Query1!$A$1:$H$687	H13:J16, H9:J11, H5:J7

 

[JamesCanale]

Your formulas are long ones. I put a simple approach to what I think might help. Basically, if the formula encounters a zero, it makes it a one but then doesn't consider the zero as part of the nth root for the geometric mean.

MrExcelPlayground10.xlsx
	A	B	C	D	E
1	5	4.472136		5	4.472136
2	4			4
3	0			4
4	4			5
5	5
Sheet7

Cell Formulas
Range	Formula
B1	B1	=PRODUCT(IF(A1:A5<>0,A1:A5,1))^(1/COUNTIF(A1:A5,"<>0"))
E1	E1	=PRODUCT(IF(D1:D4<>0,D1:D4,1))^(1/COUNTIF(D1:D4,"<>0"))

 

[Bgreen83]

Thank you for your response! I guess I'm not sure how I would use your simple approach in my example above. For instance, in cell H12, I'm taking cell I9 and raising (^) that to cell H9.

In cell H12 I'm using this formula =TRIM(PRODUCT($I9^$H9,$I10^$H10)) but didn't include the cells in yellow (i11 & H11) because they're both zero. Cell H12 (Region B) is the rollup for Locations D, E & F.