Average when there are blank cells

Ashley Brown

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....

Jana Brits

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.

CAS the CSA

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...