This post demonstrates how to calculate average of numbers in excel taking blank cells also into considerations.
For example, if we have 2 cells, one is empty and the other is 100, we want the average to be 50, rather than having a normal average of 100 as Normal average in excel does not consider blank values.
Below is an example showing both the normal average and average that takes blanks also into consideration.
The normal average in cell C2 is simple average formula with range A2:A7 i.e. 234+3432+324+4=3994 and divided by 4 which is 998.5 as correctly shown in the C2.
Now we will see here how to calculate the average of values taking blanks into consideration as shown in cell C4.
Enter the formula =SUMPRODUCT(AVERAGE(0+A2:A7)) in cell C4.
This formula forces the blank values to be converted to 0 before taking the average and hence we get the desired result 234+3432+324+4=3994 and divided by 6(4 values+2 blanks) which is 665.66 as correctly shown in the C4.
Hope this helped.
