• Status: Solved
• Priority: Medium
• Security: Public
• Views: 872

# Harmonic Mean VBA Macro Excel

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?
0
derekackerman
• 5
1 Solution

Commented:
Hello derekackerman,

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

Regards,

Patrick
0

Commented:
Sorry, the function name is HARMEAN()
0

Commented:
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.
0

Commented:
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
0

Commented:
derekackerman,

I was being dense.  HARMEAN() is standard, not part of the Analysis ToolPak.  Thus your VBA can simply be:

ActiveCell.Formula = "=HARMEAN(B2:B" & lrow & ")"

Patrick
0

Author Commented:
I like the last answer the best!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.