SUM / COUNT formula

[ss]

I am using this formula

SUM(C3:C12)/COUNT(C3:C12)

Which in principle appears to work, it adds the cells with an entry and
divides to give an average. However if for example in cell C3 I have a
formula =A3+B3 but both cells A3 & B3 are empty then my formula takes it
that the formula is an entry and includes that in the maths.

I tried this but it didnt work:
SUM(C3:C12,"0")/COUNT(C3:C12)

Can this be altered so that cells with a formula but the result of the
formula is zero are not included in the count.

 

[Ken Wright]

From Excel 2007 onwards you could just use

=AVERAGEIF(C3:C12,"<>0",C3:C12)

Regards
Ken.........


"ss" wrote in message
I am using this formula

SUM(C3:C12)/COUNT(C3:C12)

Which in principle appears to work, it adds the cells with an entry and
divides to give an average. However if for example in cell C3 I have a
formula =A3+B3 but both cells A3 & B3 are empty then my formula takes it
that the formula is an entry and includes that in the maths.

I tried this but it didnt work:
SUM(C3:C12,"0")/COUNT(C3:C12)

Can this be altered so that cells with a formula but the result of the
formula is zero are not included in the count.

 

[ss]

From Excel 2007 onwards you could just use

=AVERAGEIF(C3:C12,"<>0",C3:C12)

Regards
Ken.........


in message I am using this formula

SUM(C3:C12)/COUNT(C3:C12)

Which in principle appears to work, it adds the cells with an entry and
divides to give an average. However if for example in cell C3 I have a
formula =A3+B3 but both cells A3 & B3 are empty then my formula takes it
that the formula is an entry and includes that in the maths.

I tried this but it didnt work:
SUM(C3:C12,"0")/COUNT(C3:C12)

Can this be altered so that cells with a formula but the result of the
formula is zero are not included in the count.

Thanks Ken that works fine.

 

[Ken Wright]

You're very welcome

"ss" wrote in message

From Excel 2007 onwards you could just use

=AVERAGEIF(C3:C12,"<>0",C3:C12)

Regards
Ken.........


in message I am using this formula

SUM(C3:C12)/COUNT(C3:C12)

Which in principle appears to work, it adds the cells with an entry and
divides to give an average. However if for example in cell C3 I have a
formula =A3+B3 but both cells A3 & B3 are empty then my formula takes it
that the formula is an entry and includes that in the maths.

I tried this but it didnt work:
SUM(C3:C12,"0")/COUNT(C3:C12)

Can this be altered so that cells with a formula but the result of the
formula is zero are not included in the count.

Thanks Ken that works fine.