The logarithm function provides an imaginary number as a result for negative numbers and isn’t defined for them in Excel. You’ll need to go around that restriction if you want to get meaningful results.
Method 1 – Utilizing the IF Function to Take a Log of Negative Numbers
- Use the following formula in the D5 cell.
=LOG(C5)C5 is the Distance from Sea Level(m) of Check Point 1.
- Press ENTER to find the output as 3979.
- 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.
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. A natural log function makes complex numbers invalid.
We can solve this issue by using an IF function.
- Use the following formula in the D5 cell.
=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. It will return normal positive value.
- Press ENTER and use Fill Handle to find all the outputs.
Method 2 – Using the IMLOG10 Function to Take Log of Negative Numbers
- Use the following formula in the D5 cell.
=IMLOG10(C5)10 refers to the base of the LOG function.
- Press ENTER and use Fill Handle to find the other outputs like this.
Method 3 – Using the Minimum Value to Find a Comparative Log
- Find the minimum value among cells C5:C15 in C17:
=MIN(C5:C15)
- Press ENTER.
We need to take the positive value of that minimum value. Here it is 7.6.
- In the C18 cell, use this formula:
=ABS(C17)+1
- Press ENTER.
- Copy the value in the D Column (by reference or value).
- Use the following formula in the E5 cell to get the sum of C5 and D5
=D5+C5
- Press ENTER to find the output as 11.1 and use Fill Handle to get all the outputs in the E6:E16 columns.
See that all the values in Column E are positive although the values are different from those in Column C. The base values are changed, and we’ll find the comparative log of the values.
- Use the following formula in the F5.
=LOG(E5)E5 is the sum of the cells C5 and D5.
- Press ENTER and use Fill Handle to get all the outputs in the F Column like this.
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.
- 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 the Practice Workbook
Related Articles
- How to Calculate Log in Excel
- How to Calculate Log Base 2 in Excel
- How to Log Transform Data in Excel
- How to Plot Log Scale in Excel
- How to Plot Log Log Graph in Excel
- Excel Logarithmic Scale Start at 0
- How to Do Inverse Log in Excel
<< Go Back to Excel LOG Function | Excel Functions | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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