<div>
Hello derekackerman,<br />
<br />
If you are using the Analysis ToolPak, you will have the HARMMEAN() function, that will do this rather easily.<br />
<br />
Regards,<br />
<br />
Patrick
</div>


Hello derekackerman,

If you are using the Analysis ToolPak, you will have the HARMMEAN() function, that will do this rather easily.

Regards,

Patrick

<div>
Sorry, the function name is HARMEAN()
</div>


<div>
BTW, you can also calculate harmonic means with an array formula. &nbsp;For example, this finds the harmonic <br />
mean for A1:A5...<br />
<br />
{=1/(SUM(1/A1:A5)/COUNT(A1<wbr />:A5))}<br />
<br />
To enter an array formula, do not enter the curly braces, and hit Ctrl+Shift+Enter instead of Enter to finish it off. &nbsp;<br />
Excel will then display those braces to indicate that it's an array formula.
</div>


BTW, you can also calculate harmonic means with an array formula.  For example, this finds the harmonic
mean for A1:A5...

{=1/(SUM(1/A1:A5)/COUNT(A1:A5))}

To enter an array formula, do not enter the curly braces, and hit Ctrl+Shift+Enter instead of Enter to finish it off.  
Excel will then display those braces to indicate that it's an array formula.

<div>
derekackerman,<br />
<br />
So, in VBA, you can do this to get your array formula:<br />
<br />
ActiveCell.FormulaArray = &quot;=1/(SUM(1/B2:B&quot; &amp;&nbsp;lrow &amp;&nbsp;&quot;)/COUNT(B2:B&quot; &amp;&nbsp;lrow &amp;&nbsp;&quot;))&quot;<br />
<br />
Patrick
</div>


derekackerman,

So, in VBA, you can do this to get your array formula:

ActiveCell.FormulaArray = "=1/(SUM(1/B2:B" & lrow & ")/COUNT(B2:B" & lrow & "))"

Patrick

<div>
I like the last answer the best!
</div>


<div>
<div class="content wysiwyg-content">
I need to comput the H.M. of a column of numbers, this doesn't work:<br />
<br />
ActiveCell.Formula = &quot;=rX/SUM(1/B2:B&quot; &amp;&nbsp;lrow &amp;&nbsp;&quot;)&quot;<br />
<br />
for each cell in row 2 to the last row, I need to sum the Reciprocals (1/cell value), and then divide that into the number of cells (N)<br />
<br />
Or is there a built in function for it?
</div>
</div>


I need to comput the H.M. of a column of numbers, this doesn't work:

ActiveCell.Formula = "=rX/SUM(1/B2:B" & lrow & ")"

for each cell in row 2 to the last row, I need to sum the Reciprocals (1/cell value), and then divide that into the number of cells (N)

Or is there a built in function for it?

Harmonic Mean VBA Macro Excel

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.