HARMMEAN Function in Microsoft Excel
Part 1: Introduction
Definition
The HARMMEAN function in Microsoft Excel returns the harmonic mean of a data set. The harmonic mean is the reciprocal of the arithmetic mean of reciprocals.
Purpose
The HARMMEAN function calculates the harmonic mean of a set of numbers. The harmonic mean can be appropriate for situations when the average of rates is desired.
Syntax & Arguments
The syntax for the HARMMEAN function is as follows:
HARMMEAN(number1, [number2], ...) The HARMMEAN function has the following arguments:
number1: This argument is required. It represents the first number for which you want to calculate the mean.number2, ...: These arguments are optional. They represent the subsequent numbers for which you want to calculate the mean. You can provide up to 255 arguments. Alternatively, you can use a single array or a reference to an array instead of arguments separated by commas.
Return Value
The HARMMEAN function returns the harmonic mean of the provided numbers.
Remarks
- Arguments can be numbers or names, arrays, or references containing numbers.
- Logical values and text representations of numbers you type directly into the list of arguments are counted.
- If an array or reference argument contains text, logical values, or empty cells, those values are ignored; however, cells with zero values are included.
- Arguments that are error values or text that cannot be translated into numbers cause errors.
- If any data point ≤ 0, HARMMEAN returns the #NUM! Error value.
Part 2: Examples
Let’s look at examples of using the HARMMEAN function in Excel in business.
Example 1
Purpose: To calculate the harmonic mean of the number of products sold.
| A | B | C | |
|---|---|---|---|
| 1 | Products Sold | Formula | Result |
| 2 | 4 | =HARMMEAN(A2:A4) | 4.444 |
| 3 | 5 | ||
| 4 | 7 |
In this example, we calculate the harmonic mean of the number of products sold using the HARMMEAN function. The result is approximately 4.444.
Example 2
Purpose: To calculate the harmonic mean of the number of clients.
| A | B | C | |
|---|---|---|---|
| 1 | Clients | Formula | Result |
| 2 | 10 | =HARMMEAN(A2:A4) | 6.667 |
| 3 | 5 | ||
| 4 | 8 |
In this example, we calculate the harmonic mean of the number of clients using the HARMMEAN function. The result is approximately 6.667.
Example 3
Purpose: To calculate the harmonic mean of the number of orders.
| A | B | C | |
|---|---|---|---|
| 1 | Orders | Formula | Result |
| 2 | 6 | =HARMMEAN(A2:A4) | 5.455 |
| 3 | 5 | ||
| 4 | 7 |
In this example, we calculate the harmonic mean of the number of orders using the HARMMEAN function. The result is approximately 5.455.
Example 4
Purpose: To calculate the harmonic mean of the number of units produced.
| A | B | C | |
|---|---|---|---|
| 1 | Units Produced | Formula | Result |
| 2 | 4 | =HARMMEAN(A2:A4) | 4.444 |
| 3 | 3 | ||
| 4 | 6 |
In this example, we calculate the harmonic mean of the number of units produced using the HARMMEAN function. The result is approximately 4.444.
Example 5
Purpose: To calculate the harmonic mean of the number of deliveries.
| A | B | C | |
|---|---|---|---|
| 1 | Deliveries | Formula | Result |
| 2 | 4 | =HARMMEAN(A2:A4) | 4.444 |
| 3 | 5 | ||
| 4 | 7 |
In this example, we calculate the harmonic mean of the number of deliveries using the HARMMEAN function. The result is approximately 4.444.
Example 6
Purpose: To calculate the harmonic mean of the number of products sold, but only if the number exceeds 2.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Products Sold | Formula | Result | Check |
| 2 | 4 | =IF(A2>2, HARMMEAN(A2:A4), “N/A”) | 4.444 | Pass |
| 3 | 1.5 | =IF(A3>2, HARMMEAN(A3:A4), “N/A”) | N/A | Fail |
| 4 | 5 | =IF(A4>2, HARMMEAN(A4:A4), “N/A”) | 5 | Pass |
In this example, we use the IF function to check if the number of products sold exceeds 2. If it is, we calculate the HARMMEAN of that number. If it’s not, we return “N/A”.
Example 7
Purpose: To calculate the sum of the HARMMEAN values of the number of clients.
| A | B | C | |
|---|---|---|---|
| 1 | Clients | Formula | Result |
| 2 | 4 | =HARMMEAN(A2:A4) | 4.444 |
| 3 | 5 | =HARMMEAN(A3:A4) | 4.667 |
| 4 | 6 | =HARMMEAN(A4:A4) | 6 |
| 5 | Sum | =SUM(C2:C4) | 15.111 |
In this example, we calculate the HARMMEAN of the number of clients and then sum those values using the SUM function.
Example 8
Purpose: To look up the number of orders in a table and calculate the HARMMEAN of that number.
Assume we have another table with Order IDs and corresponding Orders:
| D | E | |
|---|---|---|
| 1 | Order ID | Orders |
| 2 | 101 | 4 |
| 3 | 102 | 5 |
| 4 | 103 | 6 |
Now, we want to look up the number of orders for a specific Order ID and calculate the HARMMEAN of that number:
| A | B | C | |
|---|---|---|---|
| 1 | Order ID | Formula | Result |
| 2 | 101 | =HARMMEAN(VLOOKUP(A2, D2:E4, 2, FALSE)) | 2 |
| 3 | 102 | =HARMMEAN(VLOOKUP(A3, D2:E4, 2, FALSE)) | 2.236 |
| 4 | 103 | =HARMMEAN(VLOOKUP(A4, D2:E4, 2, FALSE)) | 2.449 |
In this example, we’re using the VLOOKUP function to find the number of orders for a specific Order ID in a separate table (D2:E4), and then we’re calculating the HARMMEAN of that number.
Example 9
Purpose: To calculate the HARMMEAN of the number of units produced, but only if the number is not an error.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Units Produced | Formula | Result | Check |
| 2 | 4 | =IF(ISERROR(HARMMEAN(A2:A4)), “Error”, HARMMEAN(A2:A4)) | 4.444 | Pass |
| 3 | 0 | =IF(ISERROR(HARMMEAN(A3:A4)), “Error”, HARMMEAN(A3:A4)) | Error | Fail |
| 4 | 5 | =IF(ISERROR(HARMMEAN(A4:A4)), “Error”, HARMMEAN(A4:A4)) | 5 | Pass |
In this example, we’re using the ISERROR function to check if the HARMMEAN of the number of units produced is an error. If it is, we return “Error”. If it’s not, we calculate the HARMMEAN.
Example 10
Purpose: To calculate the HARMMEAN of the number of deliveries and round the result to the nearest whole number.
| A | B | C | |
|---|---|---|---|
| 1 | Deliveries | Formula | Result |
| 2 | 4 | =ROUND(HARMMEAN(A2:A4), 0) | 4 |
| 3 | 5 | =ROUND(HARMMEAN(A3:A4), 0) | 5 |
| 4 | 7 | =ROUND(HARMMEAN(A4:A4), 0) | 7 |
In this example, we’re calculating the HARMMEAN of the number of deliveries and then rounding the result to the nearest whole number using the ROUND function.
Example 11
Purpose: To calculate the HARMMEAN of the number of products sold and return the absolute value of the result.
| A | B | C | |
|---|---|---|---|
| 1 | Products Sold | Formula | Result |
| 2 | -4 | =ABS(HARMMEAN(A2:A4)) | Error |
| 3 | -5 | =ABS(HARMMEAN(A3:A4)) | Error |
| 4 | -6 | =ABS(HARMMEAN(A4:A4)) | Error |
In this example, we calculate the HARMMEAN of the number of products sold and then return the result’s absolute value using the ABS function.
Example 12
Purpose: To calculate the HARMMEAN of the number of clients and return the integer part of the result.
| A | B | C | |
|---|---|---|---|
| 1 | Clients | Formula | Result |
| 2 | 4.5 | =INT(HARMMEAN(A2:A4)) | 4 |
| 3 | 5.5 | =INT(HARMMEAN(A3:A4)) | 5 |
| 4 | 6.5 | =INT(HARMMEAN(A4:A4)) | 6 |
In this example, we calculate the HARMMEAN of the number of clients and then return the integer part of the result using the INT function.
Example 13
Purpose: To calculate the HARMMEAN of the number of orders and return the result raised to the power of 2.
| A | B | C | |
|---|---|---|---|
| 1 | Orders | Formula | Result |
| 2 | 4 | =POWER(HARMMEAN(A2:A4), 2) | 19.802 |
| 3 | 5 | =POWER(HARMMEAN(A3:A4), 2) | 21.802 |
| 4 | 6 | =POWER(HARMMEAN(A4:A4), 2) | 36 |
In this example, we calculate the HARMMEAN of the number of orders and then return the result to the power of 2 using the POWER function.
Part 3: Tips and Tricks
- Be careful when using the HARMMEAN function with data sets that contain zero or negative values. The function will return an error in these cases.
- The HARMMEAN function can be helpful when dealing with rates or ratios.
- Remember that the HARMMEAN function ignores text, logical values, and empty cells in the provided arguments. However, it includes cells with the value zero.
- The HARMMEAN function is always less than the arithmetic and geometric mean.