Using HARMEAN (Harmonic Mean) In A Pivot Table - Is It Possible?

[CRMORE]

Hi,

I have some huge files I'm working with and only just got my head around pivot tables which has been amazing. The only issue I'm having is I need to get the harmonic mean in one column but I can only see a way to display values as an average which I assume is just normal mean? Is there a way to get a harmonic mean / HARMEAN function in the pivot table?

Thanks
Alex

[dflak]

I think what you are going to have to do is use a helper column in the source data to get the Harmonic Mean. This might even require an array formula to group it by value or by date. This column will display the Harmonic Mean against each of the values to be tabulated by the pivot table.

So if you had 4 items in the mean, the pivot table would want to sum them and you would get an answer 4 times bigger than you want. Use Average instead. Since each of the HMs in the 4 items are the same, the average will be the same as any one of them.

Here is a formula that might help:

Please Login or Register  to view this content.

In your case, Function is HARMEAN, Lookup Range is the range containing the date or item you want to group on. Lookup Value is the specific value of the date or item (usually a cell on the row with the formula) and Value Range is what goes into the Harmonic Mean. So it might look something like

=HARMEAN(IF($A$2:$A$100="Item 1",IF (NOT(ISBLANK($B$2:$B$100)),$B$2:$B$100,False)))

This would be entered as an array formula (CTRL-SHIFT-ENTER).

Each row in the helper cell will compute the HM for all values in Column B where Item 1 appears in Column A

[CRMORE]

Thanks for the help! Tried that formula on a small sample and definitely works. My fear is that Excel may struggle to manage it on my sheets (with tables) as each one is about 800,000 rows. Let's see! If not will just make do with mean average on pivot table as it is easy and quick to do with large data sets.