SUM / COUNT formula

  • Thread starter Thread starter ss
  • Start date Start date
S

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.
 
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.
 
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.
Click to expand...


Thanks Ken that works fine.
 
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.
Click to expand...


Thanks Ken that works fine.
 
Back
Top