I need some help with eliminating a #DIV/0! error message. I am trying to consolidate and calculate an average from multiple spreadsheets within the same workbook using the following formula: =IF('Cass County'!G23=0,"",AVERAGE('Cass County:Marion County'!G23)). G23 is just making reference to one of the many cells I am calculating. I have approximately 12 spreadsheets (tabs). It calculates correctly if I have a value in the cell; however, if I have a 0 or blank it gives me the #DIV/0! error. Any help will be greatly appreciated.

  • Tried to replicate in Excel 2013, but I don't have the same problem - when I have blanks, or even text, the AVERAGE() ignores those values. When I have zeroes, it treats them as numbers and includes them in the calculation. What happens if you try to do the same thing in a basic form in a new workbook? ie =AVERAGE('Sheet1:Sheet3'!A1). Same problem? – Andi Mohr Mar 2 '17 at 18:21
  • I'm also not able to replicate in Excel 2010. What happens if you just have the AVERAGE function without the IF? – Werrf Mar 2 '17 at 19:41

Could you check if the cell format is 'General' and not 'Text'? I suspect that the contents of the cell is the text '0' instead of numeric 0, which causes the IF statement to calculate AVERAGE instead of just displaying "" (empty cell).

enter image description here

Your Answer

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

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