Average

[S. Kissing]

I have 13 sheets, Jan - Dec and a total sheet. I want to average cells in
the Jan - Dec sheets. If I add my formula to the sheets then my average
counts each sheet (12) I know I can add if statements to divide by only the
shhets with valuses > 0 in the cells however it is becoming very very
long...is there a short cut I am missing? Thanks

 

[Shane Devenshire]

Hi,

I'm not sure what the problem is but the basic formula would be:

=AVERAGE(Sheet2:Sheet12!A1)

If cell A1 of each sheet contains a formula which evaluates to 0 maybe you
could modify those formulas to return "" which will not be averaged.

 

[Eduardo]

Hi,
try

=SUM(Sheet2:Sheet12!A1)/COUNTIF(Sheet2:Sheet12!A1,">0")

This will divided by the number of cells with values greater than 0, so if
you have a cell with no information it will not consider it