GEOMEAN Function in Excel
Calculate geometric mean of positive numbers. Analyze investment returns, growth rates, and proportional data with the GEOMEAN function.
Excel
Google SheetsExcelGoogle Sheets=GEOMEAN(number1, [number2], ...)Quick Answer
GEOMEAN function GEOMEAN function is a statistical function in Excel and Google Sheets that calculates the geometric mean of a set of positive numbers. It returns the nth root of the product of n values and is commonly used for calculating average growth rates, investment returns, and analyzing proportional data.
=GEOMEAN(number1, [number2], ...)- number1 - the first positive number or range to include in the calculation (required)
- number2 - optional additional numbers or ranges (up to 255 arguments)
Practical Examples
Basic Investment Return Calculation
Calculate average annual return for a portfolio over 3 years
Compound Annual Growth Rate (CAGR)
Calculate CAGR for revenue growth from $100K to $150K over 4 years
Portfolio Performance with Multiple Assets
Calculate average return across different asset classes
Advanced: Array Formula with Error Handling
Calculate geometric mean with error handling for invalid data
Geometric vs Arithmetic Mean Comparison
Side-by-side comparison showing why GEOMEAN matters for volatile returns
Common Errors and Solutions
GEOMEAN returns #NUM! error
One or more values in the range are zero, negative, or result in an invalid calculation. GEOMEAN can only calculate the geometric mean of positive numbers because it involves taking roots of products, and negative numbers would produce complex (imaginary) results.
1. Check all input values are positive numbers greater than zero 2. Use IFERROR to identify problematic cells: =IFERROR(GEOMEAN(A1:A10),"Check data for zeros or negatives") 3. Filter out non-positive values: =GEOMEAN(IF(A1:A10>0,A1:A10)) 4. Convert percentages correctly: -5% return = 0.95 multiplier (not -0.05) 5. Use conditional formatting to highlight values ≤0: Home > Conditional Formatting > Highlight Cell Rules > Less Than or Equal To 0 6. For losses greater than 100%, ensure you're using multipliers correctly
Before using GEOMEAN, validate data with =COUNTIF(A1:A10,"<=0") to check for zero or negative values. Always convert percentage returns to growth multipliers: add negative returns to 1 (e.g., -30% loss = 0.70 multiplier).
Example:
GEOMEAN returns #VALUE! error
The range contains text values that cannot be converted to numbers, or the range reference is invalid. While GEOMEAN automatically ignores text in ranges, certain text formats or invalid references can trigger this error.
1. Remove or replace text entries with valid numbers 2. Use ISNUMBER to identify non-numeric cells: =ISNUMBER(A1) 3. Clean data with VALUE() function for number-like text 4. Use TRIM() to remove extra spaces that may prevent number recognition 5. Check for hidden characters using LEN() and CLEAN() functions 6. Verify range references point to valid cells or named ranges
Use Data Validation to restrict input to numbers only: Data > Data Validation > Allow: Decimal, Minimum: 0.01. Apply =ISNUMBER(A1) in adjacent column to audit data types before calculating GEOMEAN.
Example:
GEOMEAN returns #DIV/0! error
The range is completely empty, contains only text, or after filtering, no valid numbers remain. GEOMEAN requires at least one positive number to calculate. This can also occur if all cells are blank or if filters have hidden all data.
1. Verify range contains at least one number greater than zero 2. Check if range reference is correct and points to populated cells 3. Ensure filtered data hasn't hidden all values (clear filters temporarily) 4. Use COUNTA to verify data exists: =IF(COUNTA(A1:A10)>0, GEOMEAN(A1:A10), "No data") 5. Use COUNT to verify numeric values: =IF(COUNT(A1:A10)>0, GEOMEAN(A1:A10), "No numbers") 6. Check for merged cells that may affect range selection
Add data validation before calculation: =IF(COUNT(A1:A10)=0,"Enter positive numbers",GEOMEAN(A1:A10)). Document expected data format and validate input ranges before using GEOMEAN.
Example:
Advanced Tips and Best Practices
Converting Percentages to Multipliers
Always convert percentage returns to growth multipliers before using GEOMEAN. For positive returns, divide by 100 and add 1 (e.g., 10% = 1.10). For negative returns, divide by 100 and add 1 (e.g., -5% = 0.95). This ensures accurate geometric mean calculation and prevents #NUM! errors.
Combining GEOMEAN with Array Formulas
In Excel 365 or Google Sheets, combine GEOMEAN with dynamic arrays to calculate geometric means across multiple categories simultaneously. Use GEOMEAN with FILTER or UNIQUE for powerful category-based analysis without helper columns.
GEOMEAN for Volatility-Adjusted Returns
Use GEOMEAN to calculate the volatility-adjusted average return, which is more conservative and realistic than arithmetic mean for volatile investments. This is especially important for risk assessment and portfolio optimization, as it accounts for the mathematical drag of volatility on returns.
Cannot Calculate Geometric Mean of Mixed Signs
GEOMEAN cannot handle negative numbers because geometric mean of negative values involves complex numbers. If your data includes losses greater than -100% (multipliers less than 0), you must convert all values to positive growth multipliers. For example, -30% loss = 0.70 multiplier, +20% gain = 1.20 multiplier.
GEOMEAN and Compound Interest Relationship
GEOMEAN is mathematically equivalent to the compound annual growth rate (CAGR) when used with growth multipliers. The relationship: CAGR = GEOMEAN(growth_factors) - 1. This makes it perfect for financial modeling, investment analysis, and business planning where compound growth is the norm.
Alternative Calculation Method
For advanced users, GEOMEAN can be calculated using natural logarithms: =EXP(AVERAGE(LN(values))). This method is mathematically equivalent and can be useful when combining with other logarithmic calculations or for educational purposes to understand the underlying mathematics.
Need Help with GEOMEAN Function in Excel?
Stop struggling with formula syntax. Use AskFormulas to generate validated formulas instantly with our AI-powered tool.
Example Excel formula:
Related Formulas
Master the AVERAGE function with practical examples and error solutions. Learn to calculate mean values and analyze data efficiently in Excel and Sheets.
ExcelSheetsMaster the AVERAGEA function to calculate averages including text and logical values. Learn when text counts as 0, TRUE as 1, and best practices.
ExcelSheetsMaster the MEDIAN function to find the middle value in datasets. Learn syntax, practical examples, and error solutions for statistical analysis.
ExcelSheetsThe STDEV.P function calculates standard deviation for entire populations. Learn how to measure data variability and analyze statistical distributions.
ExcelSheets