• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 827
  • Last Modified:

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
Asked:
derekackerman
  • 5
1 Solution
 
Patrick MatthewsCommented:
Hello derekackerman,

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

Regards,

Patrick
0
 
Patrick MatthewsCommented:
Sorry, the function name is HARMEAN()
0
 
Patrick MatthewsCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Patrick MatthewsCommented:
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
 
Patrick MatthewsCommented:
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
 
derekackermanAuthor Commented:
I like the last answer the best!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now