# GEOMEAN Function in Microsoft Excel

## Part 1: Introduction

### Definition

The GEOMEAN function in Microsoft Excel returns the geometric mean of an array or range of positive data.

### Purpose

The GEOMEAN function calculates the average growth rate given compound interest with variable rates. It’s beneficial when dealing with rates of change, such as in financial or statistical data.

### Syntax & Arguments

The syntax for the GEOMEAN function is as follows:

syntax
```GEOMEAN(number1, [number2], ...) ```

The GEOMEAN 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 GEOMEAN function returns the geometric 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, GEOMEAN returns the #NUM! Error value.

## Part 2: Examples

Let’s look at examples of using the GEOMEAN function in Excel in business.

### Example 1

Purpose: To calculate the geometric mean of the number of products sold.

ABC
1Products SoldFormulaResult
24=GEOMEAN(A2:A4)4.932
35
47

In this example, we calculate the geometric mean of the number of products sold using the GEOMEAN function. The result is approximately 4.932.

### Example 2

Purpose: To calculate the geometric mean of the number of clients.

ABC
1ClientsFormulaResult
210=GEOMEAN(A2:A4)7.368
35
48

In this example, we calculate the geometric mean of the number of clients using the GEOMEAN function. The result is approximately 7.368.

### Example 3

Purpose: To calculate the geometric mean of the number of orders.

ABC
1OrdersFormulaResult
26=GEOMEAN(A2:A4)5.476
35
47

In this example, we calculate the geometric mean of the number of orders using the GEOMEAN function. The result is approximately 5.476.

### Example 4

Purpose: To calculate the geometric mean of the number of units produced.

ABC
1Units ProducedFormulaResult
24=GEOMEAN(A2:A4)4.160
33
46

In this example, we calculate the geometric mean of the number of units produced using the GEOMEAN function. The result is approximately 4.160.

### Example 5

Purpose: To calculate the geometric mean of the number of deliveries.

ABC
1DeliveriesFormulaResult
24=GEOMEAN(A2:A4)4.932
35
47

In this example, we calculate the geometric mean of the number of deliveries using the GEOMEAN function. The result is approximately 4.932.

### Example 6

Purpose: To calculate the geometric mean of the number of products sold, but only if the number exceeds 2.

ABCD
1Products SoldFormulaResultCheck
24=IF(A2>2, GEOMEAN(A2:A4), “N/A”)5.476Pass
31.5=IF(A3>2, GEOMEAN(A3:A4), “N/A”)N/AFail
45=IF(A4>2, GEOMEAN(A4:A4), “N/A”)5Pass

In this example, we use the IF function to check if the number of products sold exceeds 2. If it is, we calculate the GEOMEAN of that number. If it’s not, we return “N/A”.

### Example 7

Purpose: To calculate the sum of the GEOMEAN values of the number of clients.

ABC
1ClientsFormulaResult
24=GEOMEAN(A2:A4)4.932
35=GEOMEAN(A3:A4)5.476
46=GEOMEAN(A4:A4)6
5Sum=SUM(C2:C4)16.408

In this example, we’re calculating the GEOMEAN of the number of clients and then summing those values using the SUM function.

### Example 8

Purpose: To look up the number of orders in a table and calculate the GEOMEAN of that number.

Assume we have another table with Order IDs and corresponding Orders:

DE
1Order IDOrders
21014
31025
41036

Now, we want to look up the number of orders for a specific Order ID and calculate the GEOMEAN of that number:

ABC
1Order IDFormulaResult
2101=GEOMEAN(VLOOKUP(A2, D2:E4, 2, FALSE))2
3102=GEOMEAN(VLOOKUP(A3, D2:E4, 2, FALSE))2.236
4103=GEOMEAN(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 GEOMEAN of that number.

### Example 9

Purpose: To calculate the GEOMEAN of the number of units produced, but only if the number is not an error.

ABCD
1Units ProducedFormulaResultCheck
24=IF(ISERROR(GEOMEAN(A2:A4)), “Error”, GEOMEAN(A2:A4))4.932Pass
30=IF(ISERROR(GEOMEAN(A3:A4)), “Error”, GEOMEAN(A3:A4))ErrorFail
45=IF(ISERROR(GEOMEAN(A4:A4)), “Error”, GEOMEAN(A4:A4))5Pass

In this example, we’re using the ISERROR function to check if the GEOMEAN of the number of units produced is an error. If it is, we return “Error”. If it’s not, we calculate the GEOMEAN.

### Example 10

Purpose: To calculate the GEOMEAN of the number of deliveries and round the result to the nearest whole number.

ABC
1DeliveriesFormulaResult
24=ROUND(GEOMEAN(A2:A4), 0)5
35=ROUND(GEOMEAN(A3:A4), 0)5
47=ROUND(GEOMEAN(A4:A4), 0)7

In this example, we’re calculating the GEOMEAN 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 GEOMEAN of the number of products sold and return the absolute value of the result.

ABC
1Products SoldFormulaResult
2-4=ABS(GEOMEAN(A2:A4))Error
3-5=ABS(GEOMEAN(A3:A4))Error
4-6=ABS(GEOMEAN(A4:A4))Error

In this example, we calculate the GEOMEAN of the number of products sold and then return the result’s absolute value using the ABS function.

### Example 12

Purpose: To calculate the GEOMEAN of the number of clients and return the integer part of the result.

ABC
1ClientsFormulaResult
24.5=INT(GEOMEAN(A2:A4))4
35.5=INT(GEOMEAN(A3:A4))5
46.5=INT(GEOMEAN(A4:A4))6

In this example, we’re calculating the GEOMEAN of the number of clients and then return the integer part of the result using the INT function.

### Example 13

Purpose: To calculate the GEOMEAN of the number of orders and return the result raised to the power of 2.

ABC
1OrdersFormulaResult
24=POWER(GEOMEAN(A2:A4), 2)24.337
35=POWER(GEOMEAN(A3:A4), 2)29.444
46=POWER(GEOMEAN(A4:A4), 2)36

In this example, we’re calculating the GEOMEAN of the number of orders and then returning the result raised to the power of 2 using the POWER function.

## Part 3: Tips and Tricks

• Be careful when using the GEOMEAN function with ranges that may include zero or negative numbers, which will result in an error.
• You can use the GEOMEAN function with other functions like IF or VLOOKUP to perform more complex calculations.
• Remember that the GEOMEAN function ignores text, logical values, and empty cells in the range.
• Use the GEOMEAN function when dealing with rates of change or growth rates, as it gives a more accurate mean value for such data.