4

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.

#DIV/0! Error

  • 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
8

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
2

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

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.