Average when there are blank cells
I am trying to average a column that lists the projects duration......some of the cells in this colum are blank, how do I average this?
=AVG({Master Summary Duration}, IF(ISBLANK({Master Summary Duration}))?
So lost....
Answers

Perhaps =AVG({Master Summary Duration}:{Master Summary Duration})
The AVG formula does not take blank cells into account.
So say you have a column with 5 rows containing 1, 2, 3, blank, 5 the average will be 2.75. The average will thus be the average of 4 cells namely 1,2,3 and 5.
If your column with 5 rows contains 1, 2, 3, 0 and 5, the average will be 2.2. The average will thus be the average of 1,2,3,0 and 5.

not a proper solution, just a workaround...
the "=AVG( )" function doesn't count blanks; maybe try doing the average yourself... like =SUM(A2:A8)/(Count(A2:A8)+countif(A2:A8,""))  if I have A2:A8 populated with 50 ___ 100 ___ ___ ___ 75, the =AVG( ) will be 225/3=91.6666 , with my formula it counts the blanks in the range for the divisor so it's 225/7=32.1428
I'm sure there is a =countx( ) funtion that counts blanks in the range towards the divisor but I can't find it either...
Help Article Resources
Categories
Check out the Formula Handbook template!