How to Calculate Log in Excel (6 Effective Methods)

A logarithm is a mathematical operation that determines how a particular number, referred to as the base, is increased with the aid of multiplying itself to attain some other number. There are plenty of examples of logarithm in our real life, like sound (decibel measures), earthquakes (Richter scale), the brightness of stars, and chemistry (pH measurement). In this article, we’ll show you 6 methods to calculate log in Excel.

1. Using LOG Function to Calculate Log in Excel

Before calculating the logarithm, you have to know what exactly a logarithm is. We are showing you a very easy and basic formula for a better understanding of this.

If, b^a = x, then Log_b x = a

For example,

5² = 25 , so Log₅ 25 = 2

Here, we’ve got a dataset for applying the LOG function on this and have the corresponding logarithm values.

Steps: 

• First, click on cell D5 and type the following formula.

• Secondly, press ENTER and as a result, you will get 2 as the logarithm value of 100.

• Finally, use the Fill Handle tool to copy the formula to the lower cells to get the results.

Read More:  Excel Logarithmic Scale Start at 0 

2. Applying LOG10 Function for Calculating Log in Excel

LOG10 function is mostly analogous to the LOG function. Just, here the base is set permanently as 10. So, we need not select the base manually.

Steps:

• At first, type the following formula in cell C5.

• Now, press ENTER to get the result and use the Fill Handle tool to achieve the logarithm values of other cells.

Note: Here, we can see that in cells B5 & C5, the values are the same as in method 1. In method 1, for the first row, we used the base as 10 and got the logarithm value 2. Here we also get the same value. So, we could be sure about the fact that in the LOG10 function, the base is already defined as 10.

Read More: How to Calculate Antilog in Excel

3. Inserting LN Function to Determine Log

LN function determines the natural logarithm value of a number. It takes the base as e which is a mathematical constant valued at approximately 2.71828.

Steps:

• First, type the following formula in cell C5.

• Now, press ENTER to the result and use the Fill Handle tool to obtain the other cell’s logarithm values.

Read More: How to Calculate Natural Logarithm in Excel 

4. Exploring Function Library to Compute Log

Furthermore, we can do the above-mentioned 3 methods by exploring them in Function Library. For doing this, we must follow the steps below:

• Firstly, select cell D5. Then, on the ribbon, click on Formulas > Math & Trig > LN / LOG / LOG10.(choose any function based on your demand).

• Let’s take the LOG function from the drop-down menu for example. We can see a pop-up dialog box named Function Arguments. In the Number option, select cell B5 and in the Base option, select cell C5. After filling up the arguments option, we can see our result in the pop-up dialog box. Now, click on OK.

• We can see our desired Logarithm value in cell D5. Finally, using the Fill Handle tool, get the values of other cells.

5. Using Insert Function Option to Determine Log

An alternative way of doing the same task is to avail of the Insert Function option. The steps are as follows:

• First, select cell D5 and click on the Insert Function symbol. We can see a dialog box named Insert Function. Type LOG in the Search menu and select Go. Now, select the LOG function from the drop-down list and select OK.

• Now we have a similar dialog box named Function Arguments like our previous method. Here input the Number and Base also in the blank options and click OK.

• Momentarily, we can see the logarithm value in cell D5. Finally, use the Fill Handle tool and get the other values as well.

Read More: How to Do Inverse Log in Excel 

6. Applying Excel VBA Code to Calculate Log

Also, we can use the Visual Basic for Applications (VBA) to create shortcodes to calculate logarithms in Excel. We’ll calculate the logarithm of 125 with the base value of 5. Follow the steps below:

• First, go to the sheet comprised of the data for the VBA code and right-click on the Sheet name. Next, click on the View Code option.

• Secondly, right-click on Sheet7 (VBA Code) from Toggle Folders. Then, select Insert > Module.

• Then, we will see a Code Module box. Now, type down the below codes in that box and select Run.

Function LogAny(base As Double, number As Double) As Double
     LogAny = Log(number) / Log(base)
End Function
Sub Log_Ex()
     MsgBox LogAny(10, 100)
End Sub

• Eventually, you’ll see that your logarithm value is returned as 3 in a message box which is the same as the result of method 1 to method 5.

Read More: How to Log Transform Data in Excel 

Example of Calculating Log in Excel

Now we will show you a real-life application of logarithm using the LOG function in Excel.

We have a dataset of 6 solutions with their concentration in (μ mol/liter) unit. From this, we’ll find out the pH of the solutions and whether they are acidic or alkaline, or neutral.

So, the steps are as below:

• At first, select cell D5 and write down the formula as below, and press ENTER.

Here we are multiplying the value of concentration in cell C5 by 10^-6 to get the unit in mol/liter from μ mol/liter.

• By pressing ENTER, you can get the pH value of solution A in cell D5. Use the Fill Handle option and drag down to copy the formula in other cells and get the desired result.

• Afterward, click on cell E5 and type the formula as follows, and press ENTER.

Formula Breakdown:

Here we’ve inserted a logic through the IF function. It’s like that-  if our pH value is less than 7, then it will show “The solution is Acidic”; if our pH value is greater than 7, then it will show “The solution is Alkaline” and if the above 2 logic isn’t met, it will show “The solution is Neutral” in cell E5.

• This will show the statement “The solution is Acidic” because the pH value is less than 7. Now, use the Fill Handle tool and drag it down to copy formulas in other cells to get the results.

Read More: How to Calculate Logarithmic Growth in Excel

Common Errors While Calculating Log in Excel

Sometimes, the LOG function returns the value as an error. There are some common types of error and their reasons are given below to protect you from occurring error.

1. #VALUE! Error

It happens if the number or base argument is non-numeric.

2. #NUM! Error

This kind of error occurs when the number or base argument is zero or negative.

3. #DIV/0! Error

This error arises when the base argument is inserted as 1.

Things to Remember

• The Number argument in the LOG function cannot be zero or a negative number. It should always be a positive real number.
• If the Base argument is omitted, it is assumed to be 10 by the system.

Download Practice Workbook

Conclusion

In the article, we’ve tried to discuss the methods to calculate log in Excel. Hope you are satisfied enough by these methods, explanations, and examples. Please leave a comment if you have any queries.

Related Articles

• How to Calculate Log Base 2 in Excel 
• How to Take Log of Negative Numbers in Excel
  
• How to Plot Log Scale in Excel
• How to Plot Log Log Graph in Excel 

<< Go Back to Excel LOG Function | Excel Functions | Learn Excel