Average Function

[JJ]

Hi group,
I have a formula in a cell that takes the average Sales of
the 12 rows (for Jan to Dec)

like so: =Average(A1:A12)The problem is that for example,
since therea are no sales figures from August to Dec, the
average is underestimated because it averages it over the
entire range of cells (though they are zero from Augus to
Dec).

How can I make this Average function to be smarter and
only average those months that the sales is not zero
(August to Dec is Not BLANK but its zero)

 

[Harald Staff]

Hi

Average is a very simple thing: SUM/COUNT. So
=SUM(A1:A12)/12
should do for all years with 12 months in them.

HTH. Best wishes Harald

 

[Ron Rosenfeld]

Hi group,
I have a formula in a cell that takes the average Sales of
the 12 rows (for Jan to Dec)

like so: =Average(A1:A12)The problem is that for example,
since therea are no sales figures from August to Dec, the
average is underestimated because it averages it over the
entire range of cells (though they are zero from Augus to
Dec).

How can I make this Average function to be smarter and
only average those months that the sales is not zero
(August to Dec is Not BLANK but its zero)

=SUMIF(A1:A12,">0") / COUNTIF(A1:A12,">0")


--ron

 

[RagDyer]

Try this:

=SUM(A1:A12)/COUNTIF(A1:A12,"<>0")
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Hi group,
I have a formula in a cell that takes the average Sales of
the 12 rows (for Jan to Dec)

like so: =Average(A1:A12)The problem is that for example,
since therea are no sales figures from August to Dec, the
average is underestimated because it averages it over the
entire range of cells (though they are zero from Augus to
Dec).

How can I make this Average function to be smarter and
only average those months that the sales is not zero
(August to Dec is Not BLANK but its zero)

 

[Bernard Liengme]

If the Aug-Dec cells are empty, then Average will no count them

 

[Ron Rosenfeld]

Try this:

=SUM(A1:A12)/COUNTIF(A1:A12,"<>0")
--

Of course. Simpler than mine :-(


--ron