Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Take Log of Negative Numbers in Excel

If you are looking for how to take log of negative numbers in Excel, then you are in the right place. The logarithmic function is a basic function in mathematics. Frequent mathematical formulas use logarithmic functions. In Excel, it is easy to take a log of positive numbers. But there is no specific formula for but taking the log of negative numbers. In this article, we’ll try to discuss how to take the log of negative numbers.


Download Practice Workbook


3 Ways to Take Log of Negative Numbers in Excel

Excel offers some combinations of functions and some easy ways to find the log of negative numbers. To show this we have made a dataset named Data for Computer Simulation of a Project which has column headers as Check Point Number and Distance from Sea Level (m). The dataset is like this.

how to take log of negative number in excel

Let’s see the ways to take the log of negative numbers.


1. Utilizing IF Function to Take Log of Negative Numbers

Initially, we’ll see what happens to negative numbers when we use a normal LOG function.

  • Firstly, write the following formula in the D5 cell like this.
=LOG(C5)

Here, C5 is the Distance from Sea Level(m) of Check Point 1.

Utilizing IF Function to Take Log of Negative Numbers

  • Secondly, press ENTER to find the output as 3979.

  • Thirdly, use Fill Handle by holding the mouse to the right-bottom corner of the reference D5 cell and sliding the cursor downwards like the picture below to find other outputs from cells D6 to D15.

Immediately, we’ll get the output as shown in the following image.
More importantly, we can see that the outputs of the cells D6, D9, D10, D12, and D15 are #NUM! Which means the output is invalid. This happens because the reference values of these output cells are negative, and the log of any negative number becomes a complex number. And natural log function makes complex numbers invalid.

Eventually, we can solve this issue by using an IF function. It is mainly a combination of IF, COMPLEX, and LOG functions. This combined use of the functions gives an output of an imaginary number when we take a log of negative numbers.
To solve this issue, write the following formula in the D5 cell like this.

=IF(C5<0,COMPLEX(LOG(ABS(C5)),PI()),LOG(C5))

Formula Breakdown

  • LOG(C5)→ Returns log of 2.5
  • COMPLEX(LOG(ABS(C5))→ Returns the absolute value if the log(2.5) is a complex number.
  • IF(C5<0,COMPLEX(LOG(ABS(C5)),PI()),LOG(C5))→ Returns if the value of C5 i.e. is less than 0 then LOG(2.5) is a complex number and IF function will return the absolute value of it. Otherwise, it will return normal positive value.

using IF function to take log of negative numbers

Secondly, press ENTER and use Fill Handle to find all the outputs like this.

using IF function to take log of negative numbers

Now, we can see that the log of negative numbers is no more #NUM!, rather they are shown as imaginary numbers as you can see i, the symbol of imaginary numbers, in the above output.

Read More: Excel Formula If Cell Contains Negative Number (6 Examples)


Similar Readings


2. Using IMLOG10 Function to Take Log of Negative Numbers

We can also simply use the IMLOG10 function to find the log values of negative numbers. It gives the same output as the usage of the IF function.

  • Firstly, write the following formula in the D5 cell like this.
=IMLOG10(C5)

Here, 10 refers to the base of the LOG function.

Using IMLOG10 Function to Take Log of Negative Numbers

  • Secondly, press ENTER and use Fill Handle to find the other outputs like this.

Read More: How to Put a Negative Number in Excel Formula (4 Easy Methods)


3. Using Minimum Value to Find Comparative Log

We can also use the minimum value in a set of data to find a comparative log of negative numbers.

  • Firstly, to find the minimum value among cells C5:C15, select the cells and write the following formula in the C17
=MIN(C5:C15)

Using Minimum Value to Find Comparative Log

  • Secondly, press ENTER to get the output as -7.6.

Using Minimum Value to Find Comparative Log

Now, we need to take the positive value of that minimum value. Here it is 7.6.

  • Thirdly, in the C18 cell write the formula like this.
=7.6+1

Here, we want to take the value as 1 more than 7.6. That’s why we have added 1.

  • Fourthly, press ENTER to find the output as 6.

  • Fifthly, we need to place that 6 in the D Column.
  • Sixthly, write the following formula in the E5 cell to get the sum of C5 and D5
=D5+C5

  • Seventhly, press ENTER to find the output as 4 and use Fill Handle to get all the outputs in the E6:E16 columns.

Eventually, we’ll see that all the values in the E Column are positive although the values are different from those in C Column. Basically, the base values are changed, and we’ll find the comparative log of the values.

  • Eighthly, write the following formula in the F5
=LOG(E5)

Here, E5 is the sum of the cells C5 and D5.

using LOG function to take log of negative numbers

  • Finally, press ENTER and use Fill Handle to get all the outputs in the F Column like this.

using LOG function to take log of negative numbers

Read More: How to Change Positive Numbers to Negative in Excel (6 Ways)


Things to Remember

  • If we want to avoid complex numbers as the output of a log of negative numbers, we need to use the comparative log. Because this comparative log method brings the output without complex numbers.
  • We can use the LN function instead of the LOG function by simply inserting LN in the places of LOG in the formulas of Method 1 and Method 2 to get the ln

Conclusion

We can fin the log of any negative numbers if we study this article properly. Please feel free to visit our official Excel learning platform ExcelDemy for further queries.


Related Articles

Towhid

Towhid

Hello, myself Shajratul Alam Towhid. Basically, I am a Naval Architect who wants to expand knowledge in the field of Microsoft Excel. I wish all of my articles will be beneficial for the readers.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo