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.


Download Practice Workbook


6 Methods to Calculate Log in Excel

To calculate log in Excel, there are pre-defined formulas in Excel which are called functions. We can use them in 6 different ways.


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, ba = x, then Logb x = a

For example,

52 = 25 , so Log5 25 = 2

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

how to calculate log in excel

Steps: 

  • First, click on cell D5 and type the following formula.
=LOG(B5,C5)

how to calculate log in excel

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

how to calculate log in excel

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

how to calculate log in excel

Read More: Excel Logarithmic Scale Start at 0 (A Detailed Analysis)


2. Applying LOG10 Function 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.
=LOG10(B5)

how to calculate log in excel

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

how to calculate log in excel

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 (With 3 Examples)


3. Implementing LN Function in Excel

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.
=LN(B5)

how to calculate log in excel

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

how to calculate log in excel


4. Exploring Function Library

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. Availing of Insert Function Option

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 (3 Simple Methods)


6. Employing the VBA Code

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

how to calculate log in excel

  • 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.

how to calculate log in excel

Read More: How to Log Transform Data in Excel (4 Easy Methods)


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.

how to calculate log in excel

So, the steps are as below:

  • At first, select cell D5 and write down the formula as below, and press ENTER.
=-(LOG(C5*POWER(10,-6),10))

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

how to calculate log in excel

  • 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.

how to calculate log in excel

  • Afterward, click on cell E5 and type the formula as follows, and press ENTER.
=IF(D5<7,"The solution is Acidic",IF(D5>7,"The solution is Alkaline","The solution is Neutral"))

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.

how to calculate log in excel

  • 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.

how to calculate log 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.

how to calculate log in excel

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.

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.

Please visit our website ExcelDemy to explore more.


Related Articles

Shahriar

Shahriar

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc in Engineering from the Bangladesh University of Engineering & Technology. I am a Naval Architecture and Marine Engineering graduate with a great interest in research and development. I love reading books & traveling. Always try to gather knowledge from various sources and implement them effectively in my work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo