Harmonic Mean

Basic Exercise

Statement

Make an application to calculate the harmonic mean of selected values. Note: $MH = \frac{N}{\frac{1}{x_{1}}+\frac{1}{x_{2}}+\frac{1}{x_{3}}+...+\frac{1}{x_{n}}}$

Suggested Concepts:

Dim - Declares a variable.
For Each - Repeats a group of statements for each element in a collection.
Range - Allows VBA to interact with the worksheet and its cells.
Function - Function will return a value.

Remember that in most cases there is more than one way to solve the exercise.

Hint: Create a variable to add to the denominator the inverse $(\frac{1}{x})$ of the value in a cell. Use the counting of $n$ as the numerator.

Solved Exercise

Exercise Solution

We start by defining the arguments of the function and the output data type
```
Function HaRmOnIcMeAn(N As Range) As Double
```

We declare the variables

Dim Cell As Range
Dim CellVal As Double
Dim Deno As Double

We create a For...Each loop that will run through every selected cell. This loop will calculate the denominator of the harmonic mean
```
For Each Cell In N
    CellVal = Cell.Value
    Deno = Deno + (1 / CellVal)
Next Cell
```
Finally, we establish that the harmonic mean will be defined as the number of terms divided by the denominator previously calculated
```
HaRmOnIcMeAn = N.Count / Deno
```

Consolidated Answer

    Function HaRmOnIcMeAn(N As Range) As Double

    Dim Rng As Range
    Dim CellVal As Double
    Dim Deno As Double

    For Each Rng In N
        CellVal = Rng.Value
        Deno = Deno + (1 / CellVal)
    Next Rng

        HaRmOnIcMeAn = N.Count / Deno

    End Function

Show Answer

Download exercise Download answer

SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.

Excel ® is a registered trademark of the Microsoft Corporation.

INTRODUCTION

BASIC

INTERMEDIATE

ADVANCED