The LOG function in Excel returns a number’s logarithm to a specified base. If you omit the base, it uses 10.
A logarithm answers an exponent question: what power of the base produces this number? That is useful when calculating how many growth periods separate two values.
In this article, I’ll show you how to use different logarithm bases, compare LOG with LN and LOG10, and calculate periods needed to reach a target.
LOG Function Syntax in Excel
The LOG function returns the exponent to which a base must be raised to produce a given number.
=LOG(number, [base])
- number (required) is the positive real number whose logarithm you want to calculate.
- base (optional) is the logarithm base. If you leave it out, Excel uses 10.
For 1000, =LOG(1000) and =LOG(1000,10) both display 3. The first uses the default, while the second states the base.
When to Use LOG Function
- Convert values spanning a wide range into a more compact scale.
- Find how many times a value must double, halve, or grow by another fixed factor.
- Calculate the number of periods needed to move from a starting value to a target.
- Work with a custom base stored in a cell or calculated by another formula.
- Compare base 10 logarithms with natural logarithms.
Example 1: Find Base 10 Logs for a Column
Let’s start with a column whose values cover a very wide range.
Below is the dataset. Column A lists eight advertising channels, and column B contains their monthly impressions.
We want one formula to return the base 10 logarithm for every impression count.
Here is the formula:
=LOG(B2:B9)
Because the base is omitted, Excel uses 10. The one formula in C2 spills through C9.
For 9,420,000 Facebook Ads impressions, the formula returns 6.974. For 5,800 Local Radio Spot impressions, it returns 3.763.
When the base will always be 10, =LOG10(B2:B9) makes that choice clear. Use LOG when the base can change.
Example 2: Use an Explicit Base With LOG
Now let’s use base 2 to count rounds in single-elimination tournaments.
Below is the dataset. Column A lists eight tournaments, and column B contains team counts from 4 through 512.
We want to calculate how many times each team count can be halved before reaching 1.
Here is the formula:
=LOG(B2:B9,2)
The second argument sets the base to 2. The formula returns 2 rounds for a 4-team tournament and 9 rounds for a 512-team tournament.
These counts are whole numbers because every team total is a power of 2.
A 12-team bracket gives LOG(12,2) as 3.585, which rounds up to 4 rounds. The 4 spare slots, 16 minus 12, become byes.
Example 3: Compare LOG, LOG10, and LN
Here’s a side-by-side look at Excel’s three logarithm functions.
Below is the dataset. Column A contains eight positive numbers from 1 through 1,000.
We want to compare base 10 and natural logarithms for the same numbers.
First, here is LOG with its base omitted:
=LOG(A2:A9)
The formula uses base 10. It returns 0.000000 for 1, 1.000000 for 10, and 3.000000 for 1,000.
Next, here is the purpose-built base 10 function:
=LOG10(A2:A9)
LOG10 displays the same values as LOG with no base. The rows for 2 and 500 show 0.301030 and 2.698970 in both columns.
Now, here is LOG with e supplied as the base:
=LOG(A2:A9,EXP(1))
EXP(1) supplies e without typing an approximation. The formula returns 0.693147 for 2 and 6.907755 for 1,000.
Finally, here is the natural logarithm function:
=LN(A2:A9)
LN displays the same six-decimal values as LOG with base e. The values for 5 and 100 are 1.609438 and 4.605170.
Use LOG10 when the base is always 10 and LN when it is always e. Use LOG for other bases or a base stored in a cell.
Example 4: Calculate Periods Needed to Reach a Target
Let’s use LOG to solve a practical growth problem.
Below is the dataset. It lists seven locations, current rentals, target rentals, and expected monthly growth rates.
We want to calculate how many months each location needs to reach its target.
Here is the LOG formula:
=LOG(C2:C8/B2:B8,1+D2:D8)
C2:C8/B2:B8 calculates each target-to-current ratio. 1+D2:D8 turns each monthly growth rate into its growth factor.
The growth model is start*(1+rate)^n=target. Dividing by start gives (1+rate)^n=target/start.
Solving for n gives n=LOG(target/start,1+rate). The formula returns 10.47 months for Austin and 19.03 months for Tampa.
A partial month is not enough to reach the target, so we need to round each result up.
Here is the ROUNDUP formula:
=ROUNDUP(E2:E8,0)
ROUNDUP converts the results to usable whole months. Austin needs 11 months, Denver needs 15, and Raleigh needs 9.
For a financing-style model, NPER expresses the period calculation more directly: =NPER(D2:D8,0,-B2:B8,C2:C8). LOG still shows the math behind the answer.
RRI solves the related problem in the opposite direction. It finds the rate when you already know the starting value, target, and number of periods.
Example 5: Handle LOG Errors With IFERROR
Finally, let’s see what happens when the number or base is not valid.
Below is the dataset. Columns A through C contain eight test cases, their numbers, and their bases. The final base cell is deliberately blank.
We want to expose the raw LOG results before replacing the errors with a helpful message.
Here is the formula:
=LOG(B2:B9,C2:C9)
The valid rows return 3.000 for 1000 with base 10, 6.000 for 64 with base 2, and 4.000 for 81 with base 3.
Zero and negative numbers return #NUM!. Base 0 also returns #NUM!, while base 1 returns #DIV/0! because its natural logarithm is zero.
The blank base cell also returns #NUM!. A referenced blank becomes 0, which is different from leaving the optional base argument out.
Now, let’s replace those errors with a helpful message:
=IFERROR(LOG(B2:B9,C2:C9),"Check the number and base")
The valid results remain 3.000, 6.000, and 4.000. The five error rows display “Check the number and base” instead.
Pro Tip: Omitting the base makes LOG use 10. Referencing a blank base cell passes 0 instead, so Excel returns #NUM!.
Tips & Common Mistakes
- The number must be greater than zero. The base must also be greater than zero and cannot equal 1.
- Make sure the cells below a spilling LOG formula are empty. Any blocked output cell causes a #SPILL! error.
- Do not type
@before a range-based LOG formula. It applies implicit intersection and reduces the formula to one result. - Excel 2019 and earlier do not support spilling. Enter
=LOG(B2)in the first result cell and fill the formula down instead. - Use LOG10 for a fixed base of 10 and LN for a fixed base of e. LOG is best when the base varies.
- Excel returns #VALUE! when it cannot read the number or base as numeric input. Booleans and text numbers can still be converted.
- Base 1 returns #DIV/0!, not #NUM!, because Excel effectively divides by the natural logarithm of 1, which is zero.
- VBA’s
Log()function calculates a natural logarithm. It does not behave like the worksheet LOG function with its default base of 10.
LOG is most useful when you need a custom base or want to solve for an unknown exponent.
Start with the default base 10, then supply the second argument whenever your calculation needs another base.
Related Excel Functions / Articles:
