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.
How to Take Log of Negative Numbers in Excel: 3 Ways
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.
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.
- 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.
Secondly, press ENTER and use Fill Handle to find all the outputs like this.
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: How to Calculate Natural Logarithm in ExcelÂ
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.
- Secondly, press ENTER and use Fill Handle to find the other outputs like this.
Read More: How to Calculate Antilog in ExcelÂ
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)
- Secondly, press ENTER to get the output as -7.6.
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+1Here, 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 8.6.
- Fifthly, we need to place that 8.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 11.1 and use Fill Handle to get all the outputs in the E6:E16 columns.
Eventually, we’ll see that all the values in Column E are positive although the values are different from those in Column C. 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.
- Finally, press ENTER and use Fill Handle to get all the outputs in the F Column like this.
Read More: How to Calculate Logarithmic Growth in Excel
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
Download Practice Workbook
Conclusion
We can find the log of any negative numbers if we study this article properly.
I have a question at point of positive of minimum value + 1, why the number 8.6 can become 8.9 ? that’s so confusing me.
Thanks a lot..
Dear Gunawan,
Thanks for your suggestions. We updated the article. You can check it now.
Regards
ExcelDemy