How to Calculate a Logarithm in Excel (6 Methods)

What is a Logarithm?

A logarithm is a mathematical operation that quantifies how a specific number, known as the base, grows by repeatedly multiplying itself to reach another number. Logarithms find practical applications in various real-world scenarios, such as measuring sound intensity (in decibels), assessing earthquake magnitude (using the Richter scale), describing star brightness, and determining pH levels in chemistry.

Method 1 – Using LOG Function

The LOG function allows you to calculate logarithms with a specified base.

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

For example, if 5² = 25, then Log₅ 25 = 2.

Below is a dataset that we’ll use to apply the LOG function:

Steps

• Click on cell D5.
• Enter the formula:

• Press ENTER.
• This will give you the logarithm value: 2 for 100.

• Use the Fill Handle tool to copy the formula to other cells.

Read More:  Excel Logarithmic Scale Start at 0 

Method 2 – Using the LOG10 Function

The LOG10 function calculates logarithms with a fixed base of 10.  You don’t need to manually select the base; it’s always 10.

Steps

• Enter the formula in cell C5:

• Press ENTER.
• Use the Fill Handle tool to get logarithm values for 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

Method 3 – Using the LN Function (Natural Logarithm)

The LN function computes the natural logarithm (base (e)) of a number.  The mathematical constant (e) is approximately 2.71828.

Steps

• Enter the formula in cell C5:

• Press ENTER.
• Use the Fill Handle tool to obtain the logarithm values for other cells.

Read More: How to Calculate Natural Logarithm in Excel 

Method 4 – Exploring Function Library

You can access these functions via the Function Library:

• Select cell D5.
• Go to Formulas > Math & Trig > LN / LOG / LOG10 (choose the desired function).

• Select the LOG function from the drop-down menu.
• In the Function Argument pop-up dialog box, select the appropriate cells for the arguments (number (cell B5) and base (cell C5)).

• Click OK to get the logarithm value in cell D5.
• Use the Fill Handle tool to extend the formula to other cells.

Method 5 – Using the Insert Function Option

Alternatively, use the Insert Function option:

The steps are as follows:

• Select cell D5.
• Click the Insert Function symbol.
• Search for LOG and select it.

• Enter the number and base in the Function Arguments dialog box.
• Click OK to see the logarithm value in cell D5.

• Use the Fill Handle tool to populate other cells.

Read More: How to Do Inverse Log in Excel 

Method 6 – Using Excel VBA Code to Calculate Logarithms:

We can utilize Visual Basic for Applications (VBA) to craft shortcuts for computing logarithms within Excel. Let’s compute the logarithm of 125 with a base value of 5. Follow the steps outlined below:

• Creating the VBA Code
  • Open your Excel workbook and navigate to the sheet containing the data for the VBA code.
  • Right-click on the sheet name and select View Code.

• • In the VBA editor, right-click on Sheet7 (VBA Code) under Toggle Folders and choose Insert > Module.

• • In the Code Module box, enter the following VBA code:

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

• • This code defines a custom function LogAny that calculates logarithms with a specified base and a subroutine Log_Ex that displays the result in a message box.

• Running the VBA Code
  • Run the Log_Ex subroutine by selecting it in the VBA editor and clicking the Run button.
  • You’ll see a message box displaying the logarithm value (e.g., 3 for log base 10 of 100).

Read More: How to Log Transform Data in Excel 

Example – Calculating pH Using the LOG Function in Excel

Let’s apply the LOG function to determine the pH of solutions based on their concentration. Assume we have a dataset of 6 solutions with concentrations in μmol/liter. We want to find out whether they are acidic, alkaline, or neutral.

• Calculating pH
  • Select cell D5 and enter the following formula:

Here, we multiply the concentration value in cell C5 by 10^-6 to convert from μmol/liter to mol/liter.

• • Press ENTER to get the pH value for solution A in cell D5.
  • Use the Fill Handle tool to copy the formula to other cells.

• Determining Solution Type
  • Click on cell E5 and enter the following formula:

• =IF(D5<7,"The solution is Acidic",IF(D5>7,"The solution is Alkaline","The solution is Neutral"))

Formula Breakdown

• This formula uses the IF function to categorize solutions based on their pH values.
• If the pH value is less than 7, it displays “The solution is Acidic.”
• If the pH value is greater than 7, it displays “The solution is Alkaline.”
• Otherwise, it shows “The solution is Neutral” in cell E5.

• • Use the Fill Handle tool to extend the formula to other cells.

Read More: How to Calculate Logarithmic Growth in Excel

Common Errors While Calculating Logarithms in Excel

Be aware of these common errors when using the LOG function:

1. #VALUE! Error

Occurs if the number or base argument is non-numeric.

2. #NUM! Error

Arises when the number or base argument is zero or negative.

3. #DIV/0! Error

This error occurs when the base argument is set to 1.

Things to Remember

• The Number argument in the LOG function must be a positive real number (cannot be zero or negative).
• If the Base argument is omitted, it defaults to 10. Feel free to apply any of these methods based on your preference!

Download Practice Workbook

You can download the practice workbook from here:

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