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.

2

2 Answers

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.

1

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

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy