• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 872
  • 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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
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
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.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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