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
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.
- 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.
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.
- 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.
3. Implementing LN Function in Excel
- 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.
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.
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
- 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.
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.
=IF(D5<7,"The solution is Acidic",IF(D5>7,"The solution is Alkaline","The solution is Neutral"))
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.
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.
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.