Weighted harmonic mean that ignores #DIV/0

Question

I have this values:

• Dataset: I have a row in a sheet (AQ4:KK4) with 255 values. The dataset have some 0 values.
• Weights: I have a column in another sheet (L3:L257), also 255 values. Weights are never 0.

I use transpose for the weights and try to convert the dataset array to its reciprocals, but fail with error. This is the formula I used but I'm confused. It works when the dataset doesn't have 0:

=PRODUCT(SUM(IF(AQ4:KK4<>0,TRANSPOSE(L3:L25),FALSE)),1/SUMPRODUCT(IF(AQ4:KK4<>0,TRANSPOSE(L3:L25),""),1/(IF(AQ4:KK4<>0,AQ4:KK4,""))))

I tried searching on the web but there are very few answers and they refer more to the weighted arithmetic average.

What is the correct formula?

Answer 1

I agree with the analysis presented in the previous answer, but I'm not that fascinated by new functions and I use them when necessary, so I prefer traditional solutions. I suggest the following formula:

=MMULT(--(data<>0),weights)/MMULT(IF(data<>0,1/data,0),weights)

where data and weights represent appropriate ranges.

Answer 2

I may have a solution to your problem, although I'm not sure it is mathematically correct. Feel free to comment my post if that's the case.

As you know, the problem you have comes from the 0 value in your dataset. One workaround would be to remove these values from the calculation.

But that creates another problem: your array representing the weights would not be of the same size, and your SUMPRODUCT would fail.

So you would need to remove some of the weights too, if their counterpart in the dataset is zero.

Fortunately, using array formulas, it's easy to do! You need the following functions to do this (I recommend to check the MS documentation to understand how they work):

• LET as a general function, to be able to set variables you can reuse in the formulas
• HSTACK which combines 2 arrays horizontally
• FILTER which is used to filter an array (potentially using another array)
• TAKE and DROP which can be used to truncate an array

Here is the formula in an inline form:

=LET(x, B5:B7, y, D3:F3, xy, HSTACK(x, TRANSPOSE(y)), xy_filtered, FILTER(xy, DROP(xy,, 1)<>0), x_filtered, TAKE(xy_filtered,, 1), y_filtered, DROP(xy_filtered,, 1), PRODUCT(SUM(x_filtered),1/SUMPRODUCT(x_filtered,1/y_filtered)))

Here is the formula in a user friendly form:

=LET(
x, B5:B7,
y, D3:F3,
xy, HSTACK(x, TRANSPOSE(y)),
xy_filtered, FILTER(xy, DROP(xy,, 1)<>0),
x_filtered, TAKE(xy_filtered,, 1),
y_filtered, DROP(xy_filtered,, 1),
PRODUCT(SUM(x_filtered),1/SUMPRODUCT(x_filtered,1/y_filtered))
)"                      
                    

Here is how it works:

• The LET is the main function, which is used to define the local variables used in the calculations. Some are temporary variables for the sake of clarity.
• x and y are respectively the weight range (vertical) and dataset range (horizontal). You need to replace those by your own ranges.
• xy is a 2 column matrix composed of the weight column and the dataset transposed. Now the weight and the dataset value are side by side in the same matrix.
• xy_filtered is the same matrix, but the rows where the y is zero are filtered out.
• DROP(xy,,1) is used to remove the x column in the xy matrix (i.e. drop no line and 1 column). In other words, it represents y.
• Conversely, TAKE(xy,,1) is used to keep the x column in the xy matrix (i.e. keep all lines and 1 column)
• Since your matrix xy_filtered is now clean of all the 0 values, you can split it back into an x_filtered and y_filtered variables, using DROP and TAKE functions.
• The last argument of the LET function is always what you return. So I simply added the function you provided, but remove the now unnecessary IF statements and replaced the ranges you provided by the x_filtered and y_filtered variables.

Here is an example with 3 values only. My formula (green) returns the same value as yours (yellow):

And now if we add a 0:

Answer 3

If you have Excel 365, then this simpler formula will work; it might not work correctly in earlier versions of Excel.

=COUNTIF(B3:B12,">0")/SUMPRODUCT(IFERROR(1/B3:B12,0),TRANSPOSE(1/E1:N1))