For example, given two numbers, 4 and 9, the long-hand calculation for the geometric mean is 6: The GEOMEAN function returns the same result: By contrast, the arithmetic mean is 6.5: The GEOMEAN function takes multiple arguments in the form number1, number2, number3, etc. up to 255 total. Arguments can be a hardcoded constant, a cell reference, or a range. Often, a single range or array is used instead of multiple arguments, as seen in the example worksheet.
Examples
In the example shown, GEOMEAN is used to calculate a compound annual growth rate. To do this we use the growth factor values in column D in the GEOMEAN function, then subtract 1. The formula in G7 is:
Notes
Arguments can be numbers, names, arrays, or references that contain numbers. Empty cells, and cells that contain text or logical values are ignored. If any provided values are less than or equal to zero, GEOMEAN returns #NUM!
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
