# Excel returns #DIV/0! for simple AVERAGE calculation

Hi Excel is unable to do a simple AVERAGE calculation with values that come from a text truncation.

• C19 is calculated as =LEFT(C18,1)
• C24 is calculated as =LEFT(C23,1)
• C29 is calculated as =LEFT(C28,1)
• Quality Avg (C30) is calculated as

=IF(NOT(AND(ISBLANK(C19),ISBLANK(C24),ISBLANK(C29))),AVERAGE(C19,C24,C29),"")

The error checking shows that each value in the AND function is the correct number. But Excel is unable to calculate the average and returns a #DIV/0!. How can a division by 3 return that error? This is in Excel 2013.

Also, why do the blank columns return #DIV/0!? The IF statement checks for blanks.

• The left function returns text.... – Solar Mike Dec 18 '17 at 0:55
• "why do the blank columns return #DIV/0!? The IF statement checks for blanks." Do those blank columns still have the LEFT function formulas in place? If so then those formulas may return what looks like a blank but ISBLANK will return FALSE. You might want to use IFERROR function in conjunction with teylyn's suggestion, e.g. `=IFERROR(AVERAGE(C19,C24,C29),"")` – barry houdini Dec 18 '17 at 16:11

change the cells with the LEFT function to convert the text to numbers.

``````=LEFT(C18,1)+0
``````

or

``````=LEFT(C18,1)*1
``````

Now the results are numeric and you can work with them as numbers.

• Thank you. This was the solution with the error check suggested by barry houdini. – user823527 Dec 19 '17 at 1:37

This should work:
`=IF(NOT(AND(ISBLANK(C19),ISBLANK(C24),ISBLANK(C29))),AVERAGE(VALUE(C19),VALUE(C24),VALUE(C29)),"")`