Average In Excel Including Blank Cells

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.

 

Share The Knowledge

Random Posts

  • Area Function In Excel

    Area function is a very significant function when it comes to determine how many references have been selected in the […]

    Share The Knowledge
  • Find Trailing Space In Excel

    There are situation when we try to look up some value in excel but we do not get the desired […]

    Share The Knowledge
  • Split Words In Excel If Uppercase Character Is Found

    split word in excel when upper case character is found, text to column when a capital letter is found, split text string when an uppercase character is found, separate word when an uppercase character is found.

    Share The Knowledge
  • Case Insensitive String Comparison In VBA

    case independent string comparison VBA, excel vba to compare string case independently, make string comparison case independent in vba

    Share The Knowledge

Leave a Reply