Excel LOG Function: Calculate Logarithms with Custom Bases
=LOG(number, [base])The LOG function in Excel is a powerful mathematical tool that calculates the logarithm of a number to a specified base. Whether you're working with financial analysis, scientific calculations, or data normalization, understanding logarithms is essential for advanced spreadsheet work. Logarithms are the inverse of exponential functions and are widely used in fields like engineering, statistics, and economics to transform data that spans multiple orders of magnitude into more manageable scales. The LOG function is particularly valuable when you need to work with growth rates, decay patterns, or when comparing values across different scales. Unlike the LN function which uses the natural logarithm (base e) or LOG10 which uses base 10, the LOG function offers flexibility by allowing you to specify any base you need. This makes it indispensable for specialized calculations where standard logarithmic bases don't suffice. With support across Excel 2007 through Excel 365, this function remains a cornerstone of Excel's mathematical capabilities.
Syntax & Parameters
The LOG function uses the syntax =LOG(number, [base]) where both parameters play distinct roles in your calculation. The 'number' parameter is required and represents the positive value for which you want to calculate the logarithm. This must be a positive number greater than zero; attempting to use zero or negative numbers will result in a #NUM! error. The 'base' parameter is optional and specifies the base of the logarithm you're calculating. If you omit the base parameter, Excel defaults to base 10, making LOG equivalent to LOG10 for common logarithmic calculations. You can specify any positive number as the base except 1, since a logarithm with base 1 is mathematically undefined. For example, LOG(100) returns 2 because 10² equals 100, while LOG(8,2) returns 3 because 2³ equals 8. Understanding this relationship—that LOG returns the exponent needed to raise the base to reach the number—is crucial for applying this function correctly in real-world scenarios. When working with bases, remember that the base must be a positive number different from 1 to avoid calculation errors.
numberbasePractical Examples
Financial Analysis: Calculating Investment Growth Rates
=LOG(10000/1000,2)/LOG(2,2)This formula calculates how many times the investment doubled by dividing the logarithm of the ratio by the logarithm of 2. Alternatively, =LOG(10000/1000)/LOG(2) uses natural logarithm conversion to find the number of doublings.
Scientific Research: pH Level Calculation
=-LOG(0.0001,10)For a hydrogen ion concentration of 0.0001 moles per liter, this formula calculates the pH value by taking the negative logarithm base 10 of the concentration.
Data Science: Normalizing Wide-Range Data
=LOG(A2,10)Applied to each sales figure in column A, this formula converts the raw sales data into logarithmic scale (base 10), compressing the wide range into a more manageable scale for analysis.
Key Takeaways
- LOG(number,[base]) calculates logarithms with flexible base specification, defaulting to base 10 when omitted.
- The number parameter must be positive; negative numbers or zero produce #NUM! errors.
- LOG is ideal for normalizing wide-range data, calculating growth rates, and working with exponential relationships.
- Combine LOG with other functions like SLOPE, POWER, and IF to create sophisticated financial and scientific analyses.
- Understanding logarithmic relationships—where LOG returns the exponent needed to raise the base to reach the number—is crucial for proper application.
Pro Tips
Use LOG for data normalization when your dataset spans multiple orders of magnitude. Applying LOG transformation makes statistical analyses more reliable and visualizations more interpretable.
Impact : Improves model accuracy, reduces bias from extreme values, and makes patterns in exponential data immediately visible.
Combine LOG with IF statements to handle edge cases: =IF(A1<=0,"Invalid",LOG(A1,10)). This prevents #NUM! errors and provides user-friendly feedback.
Impact : Creates robust, production-ready formulas that handle unexpected data gracefully and communicate errors clearly.
Remember that LOG(base,base) always equals 1, and LOG(1,base) always equals 0 for any valid base. Use these properties to validate your formulas and create shortcuts.
Impact : Speeds up formula creation and provides quick mental checks to verify calculations are correct.
For financial applications, use LOG to calculate the number of periods needed to reach a target value: =LOG(target/initial)/LOG(1+rate). This is more efficient than iteration.
Impact : Enables quick financial forecasting and scenario analysis without complex array formulas or circular references.
Useful Combinations
Change of Base Formula: Converting Between Logarithmic Bases
=LOG(number,base1)/LOG(base2,base1)This combination converts logarithms between different bases using the mathematical change of base formula. For example, =LOG(100,10)/LOG(2,10) converts a base-10 logarithm to base-2. This is useful when you need to compare calculations using different logarithmic bases or when working with systems that use non-standard bases.
Exponential Regression Analysis: Combining LOG with SLOPE
=SLOPE(LOG(y_range),x_range)This combination uses LOG to linearize exponential data, then applies SLOPE to find the rate of exponential growth. By transforming y-values logarithmically, you convert exponential relationships into linear ones, allowing SLOPE to calculate the growth coefficient. Essential for trend analysis in finance, biology, and epidemiology.
Decibel Calculation: Combining LOG with Multiplication
=20*LOG(voltage_ratio,10)This formula calculates decibels for audio and signal processing by combining LOG base 10 with a scaling factor. The multiplier (20 for voltage, 10 for power) converts the logarithmic ratio into the standard decibel scale used in engineering and audio applications.
Common Errors
Cause: Attempting to calculate LOG of a negative number, zero, or using base 1 or a negative base. For example, =LOG(-5,10) or =LOG(100,1) both produce this error.
Solution: Ensure the number parameter is positive and greater than zero. Verify the base is positive and not equal to 1. Use ABS() function if you need to work with negative values: =LOG(ABS(number),base)
Cause: Providing non-numeric values as parameters. For instance, =LOG("text",10) or =LOG(100,"base") will trigger this error.
Solution: Verify that both parameters are numeric values or cell references containing numbers. Check for hidden spaces or formatting issues in your data. Use ISNUMBER() to validate inputs before calculation.
Cause: While rare with LOG directly, this occurs when LOG is used in a division formula where the denominator becomes zero. For example, =LOG(A1)/LOG(A2) when A2 equals 1.
Solution: Add error checking using IFERROR: =IFERROR(LOG(A1)/LOG(A2),"Invalid"). Verify your base values are not equal to 1 before performing division operations with logarithmic results.
Troubleshooting Checklist
- 1.Verify the number parameter is positive (greater than 0). Negative numbers and zero produce #NUM! errors.
- 2.Confirm the base parameter is positive and not equal to 1. Base must be different from 1 and greater than 0.
- 3.Check that both parameters are numeric values or valid cell references containing numbers, not text.
- 4.Ensure you're not dividing by LOG results that equal zero (which occurs when base equals the number in the divisor).
- 5.Test with simple known values first: LOG(10,10) should equal 1, and LOG(100,10) should equal 2 to verify your formula structure.
- 6.Use IFERROR or ISNUMBER functions to catch and handle errors before they propagate through dependent calculations.
Edge Cases
Using LOG(1,base) for any valid base
Behavior: Always returns 0, because any number raised to the power of 0 equals 1 (base⁰ = 1).
This is mathematically correct and expected behavior. Useful for validating formulas and as a baseline in calculations.
Very small positive numbers approaching zero, like LOG(0.0000001,10)
Behavior: Returns large negative values (-7 in this case). As the number approaches zero, the logarithm approaches negative infinity.
Solution: Use ABS() if you need the magnitude: =ABS(LOG(0.0000001,10)) returns 7. Be aware of this behavior in division operations.
This is mathematically correct but can cause unexpected results if not anticipated in your formulas.
Fractional bases like LOG(16, 0.5)
Behavior: Returns -4 because 0.5⁻⁴ = 16. Fractional bases produce negative exponents as results.
Solution: This is valid but rarely used. Ensure your formula logic accounts for negative results when using fractional bases.
While mathematically valid, fractional bases are uncommon in business applications. Most use cases involve bases greater than 1.
Limitations
- •The number parameter must be positive; LOG cannot calculate logarithms of negative numbers or zero, making it unsuitable for certain mathematical operations without preprocessing.
- •The base parameter cannot equal 1 or be negative, restricting the function to standard logarithmic calculations and preventing some theoretical mathematical explorations.
- •LOG has limited precision with extremely large numbers (beyond 10^308) or extremely small positive numbers (below 10^-308), potentially causing rounding errors in edge cases.
- •The function returns approximate decimal values rather than exact symbolic results, which may accumulate rounding errors when LOG results are used in subsequent calculations or complex formulas.
Alternatives
Compatibility
✓ Excel
Since 2007
=LOG(number,[base]) - Fully supported in Excel 2007, 2010, 2013, 2016, 2019, and Excel 365 with identical syntax and behavior.✓Google Sheets
=LOG(number,[base]) - Identical syntax and functionality to Excel, fully compatible.Google Sheets maintains complete compatibility with the LOG function. All examples and formulas work identically in Google Sheets.
✓LibreOffice
=LOG(number,[base]) - Fully supported in LibreOffice Calc with same syntax, though some edge case behaviors may vary slightly.