Harmonic Mean IF

**aaochsner** · 08-31-2012, 02:55 PM

I am familiar with the AVERAGEIF and AVERAGEIFS functions, but am looking for a function that performs a HARMONIC mean on an array of data that share a particular parameter.

For example: calculate the harmonic mean of B1 and B2 if A1=A2. Specifically, calcuate the harmonic mean of B1 to Bn if A1 to An share the same parameter.

Does anyone know how to write such a function?

**Ace_XL** · 08-31-2012, 03:05 PM

Do you mean..

=IF(A1=A2,HARMEAN(B1,B2))

**shg** · 08-31-2012, 03:09 PM

Please Login or Register  to view this content.

In D2, which assumes there are no zeros in col B,

=COUNTIF(A1:A5, "Bob") / SUMPRODUCT(--(A1:A5="Bob"), 1/B1:B5)

Or via an array formula,

=HARMEAN(IF(A1:A5="Bob", B1:B5))