LOG in Google Sheets

What is LOG Function in Google Sheets?

The LOG in Google Sheets returns the logarithm of a number a user gives to the specified base. While you may be familiar with the LN function in Google Sheets, it is not very flexible. It can’t evaluate a number’s logarithm to a base that isn’t an Euler’s number (e). For this, we use the LOG function.

The LOG function gives the logarithm of a number in any base. Mostly, we use it to calculate the logarithm of a number to base 10. In the example below, to calculate the logarithm of 64 with base 2, we use LOG as follows: =LOG(64,2).

The LOG function in Google Sheets calculates the logarithm of a given number. You can use it by supplying the value and you get the result based on the base of the logarithm you want to calculate.
The LOG function has the following syntax.
=LOG(number, [base])
Number: The number for which you want to find the logarithm.
base: The base of the logarithm.
If the base is not specified, it is assessed by default as 10.
You can use LOG to find the logarithm with various bases, such as 2, e (natural logarithm), 3, etc.
If the number or base argument is less than or equal to 0, the function will return an error.

Syntax

The syntax of the LOG function is as follows:

=LOG(LOG(value, [base])

It is a built-in function that takes two arguments: the number whose logarithm is to be computed and the base.

The LOG function has two arguments:

Value – (mandatory) It represents the number whose logarithm is to be determined. It should always be a positive real number.
Base – (optional) It is the base of the logarithm. If the base is not mentioned, it is assumed to be 10.

How to Use LOG Function in Google Sheets?

For the uninitiated, the logarithm is the power to which the given base should be raised so you may get the value, which is the first argument.

We can use it in two ways.

It can be entered manually

Through the Google Menu bar

Entering LOG()Manually

Step 1: First enter the two parameters in a sheet. Here, we enter 216 and 6 as the base. Select an empty cell to find the logarithm of number 216 with base 6.

Enter LOG Manually in Google Sheets - Step 1

Step 2: Enter the formula as shown below. Type the “=” sign followed by LOG(. Next, enter the cell reference of the value for which the logarithm is to be computed, followed by the cell reference of the base and close its parentheses. Press Enter.

Enter LOG Manually in Google Sheets - Step 2

Step 3: The logarithm of 216 with base 6 is three as observed above. It means that base 6 should be raised to power 3 to obtain the number 216. You can also enter the numbers directly as shown below,

Enter LOG Manually in Google Sheets - Step 3

Access from the Google menu bar

Choose an empty cell to get the output. Go to the “Insert” tab and go to the Function option.

From here, go to Math > LOG. Once you get the function in the c ell, you can enter the arguments and get the logarithm.

Thus, the LOG function can be accessed in two ways: manually or through the menu bar, as seen above.

Examples

Let us look at some scenarios where we may use the LOG function. It is widely used in finance, especially in calculating compound interest and plotting graphs.

Example #1

Let us look at an example where we use LOG in Google Sheets differently. Below is a table with the different values.

Step 1: Now, apply the LOG function in cell C1. The first formula is =LOG(A1) as we do not have any base value in B1.

LOG Function Google Sheets Example 1 Step 1

Press Enter. Enter the formula =LOG(A2, B2) in cell C2 and add the base.

LOG Function Google Sheets Example 1 Step 1.1

Now, drag the formula to C5. The following results are obtained, which we will discuss in detail.

LOG Function Google Sheets Example 1 Step 1.2

LOG Function with the Base Argument not Specified

The first result, “2,” shown in cell C1, is the logarithm of 100. Here, the second argument is not specified and, by default, is assumed to be 10.

LOG Function Google Sheets Example 1 Step 1.3

With decimal values as the Base Argument

The result in cell C2 shows a decimal number 9.65. Here, the logarithm of 50 with base 1.5, which can also be written as =LOG(50, 1.5), is calculated.

LOG Function Google Sheets Example 1 Step 1.4

With decimal values as the value Argument

The result indicates a decimal number can also be included in the second argument using the LOG function.

LOG Function Google Sheets Example 1 Step 1.5

When the LOG function’s first or second argument is Negative

LOG Function Google Sheets Example 1 Step 1.6

The results show an #NUM! Error when a negative value is used for the first or second argument.

Example #2 – Calculate compound interest or investment growth

The LOG function is very helpful in calculating the compound interest. Suppose a man invests an amount of $60000 at an annual interest of 6.5% compounded annually. At what time will his investment amount be $70000 and $80000?

The formula to calculate the time to reach those amounts can be done using the LOG function. The formula to calculate the amount at the end of a fixed time is as follows:

A_t = P.(1 + r/n)^nt

Here,

A_t represents the amount at the end of a tenure
P is the initial principal ($60000)
n is the number of times the bank compounds the interest per year(1)
t is the time (in years) for which the amount is calculated.
r indicates the annual interest rate (here, it’s 0.065)

From the above formula, after certain adjustments, you get the following formula with LOG, which we apply to calculate the time when you get the fixed amounts of $70000 and $80000.

=(LOG(A_t/60000)/(1*LOG(1 + 0.065/1))

Let us implement it in a cell in Google sheets. Below is a table with the details as shown.

Step 1: Enter the formula )in cell B6 to obtain the first amount’s ($70000) time. Press “Enter.”

=(LOG(70000/B1)/(1*LOG(1 + B2/B3)))

You get approximately 2.5 years, which is the time for the amount to become $70000.

LOG Function Google Sheets Example 2 Step 1

Step 2: Write the below formula in cell B8 to find when the loan amount might become $80000.

=(LOG(80000/B1)/(1*LOG(1 + B2/B3)))

When you press Enter, you get approx. 4.5, which is the number of years to reach $80000.

LOG Function Google Sheets Example 2 Step 2

Thus, Column B contains the time required for the two amounts calculated with the LOG formula.

Now, let us look at another interesting LOG in Google Sheets example.

Example #3

The half-life of a radioactive substance remaining can be found using the following formula. Let us calculate it for a radioactive substance.

T_1/2=λ⋅log(e)log(2)

Here,

T_1/2is the half-life of the substance.

λ is the decay constant (decay rate).

log(2) is the common logarithm (log base 10) of 2

log(e) is the natural logarithm base; it is around 0.4343

Let us apply this data to Google Sheets and calculate the time the radioactive substance takes to decay to its half-life.

Step 1: Suppose the decay constant λ is 0.07 per year. Then, we can calculate the half-life as follows:

LOG Function Google Sheets Example 3 Step 1

Step 2: Enter the formula =LOG(2)/(0.07*0.4343) in cell C2 to get the rounded value of the number of years required to reach the half-life of the radioactive substance. Here, it is approximately 9.9 years.

LOG Function Google Sheets Example 3 Step 2

Thus, we can easily use the LOG in Google Sheets to calculate values that change concerning time. In the above examples, we observed the change in the amount and the half life calculation concerning time using LOG functions.

Important Things to Note

Besides LOG, a LOG10 function also returns the logarithm of a number with base 10.
The #VALUE! error is obtained when the number or the base is non-numeric.
The LOG Function is the opposite of the POWER Function.
The first and the second argument in the LOG Function should always be a positive number.
The LOG function is useful when you deal with variables that change with respect to time.

Frequently Asked Questions (FAQs)

What mistakes should you avoid when using the LOG function?

The LOG function gives an error under the following conditions.
• #NUM! – This error is obtained when either of the two arguments of the LOG function is negative or zero. The function does not work with negative numbers since no positive number raised to any power gives a zero or negative number.

• #VALUE! – When the first or second argument of the LOG function is non-numeric, you get this error. When the first or second argument is non-numeric, you get an error.

• #DIV/0! – It occurs when the base argument equals 1. Logarithms which have a base of one are undefined. It is because any number raised to the power of zero is one. Since any number raised to the power of zero is one. Make sure your base is greater than one when specifying it in the LOG function.

Can LOG be combined with other functions?

You can combine LOG with other powerful functions to produce complex formulas. When you combine LOG with a function like AVERAGE, you can use it for complex calculations such as computing the average logarithmic value of a dataset.
You can also combine LOG with functions like IF and SUM to create dynamic spreadsheets to fulfil your specific requirements.

How is the LOG function different from the LN function in Google Sheets?

The LOG function calculates the logarithm of a number with any specified base. Its syntax is: =LOG(number, base).

The LN function calculates the natural logarithm to base 𝑒. Its syntax is =LN(number).

Download Template

This article must help understand LOG in Google Sheets with its formulas and examples. You can download the template here to use it instantly.

LOG in Google Sheets