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

Improve this question
2
  • The left function returns text....
    – Solar Mike
    Commented Dec 18, 2017 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
    Commented Dec 18, 2017 at 16:11

2 Answers 2

Reset to default
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.

Improve this answer
1
  • Thank you. This was the solution with the error check suggested by barry houdini.
    – user823527
    Commented Dec 19, 2017 at 1:37
3

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

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .