Averaging and empty cells

QuestionEdit

I need to average the contents of 12 cells, but I want it to ignore empty cells. How do you do that? Currently, it counts them as zeros and calculates using all of them.

AnswerEdit

Use your formula like the below:-

Upto 2003:-

Assume that you are having the values from A1 to A12 cell. =SUM(A1:A12)/(COUNT(A1:A12)-COUNTIF(A1:A12,0))

If there is no values are present in A1 to A12 cell then it will result #DIV/0! Error. To get out of this error use your formula like the below =IF(ISERROR(SUM(A1:A12)/(COUNT(A1:A12)-COUNTIF(A1:A12,0))),"",SUM(A1:A12)/(COUNT(A1:A12)-COUNTIF(A1:A12,0)))

If you are using2007 use the Averageif formula like the below:- =AVERAGEIF(A1:A12,">0",A1:A12)

Change the cell range A1:A12 to your desired range, if required.