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**

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