Sum up statistical results from 12 worksheets

I have 12 worksheets of certain data, that is one worksheet for every month
of the year (i.e. Jan-Dec). In each worksheet/month I have already calculated
the
following functions/formulae:
SUM
MIN
MAX
AVERAGE
STDEV
Data above the AVERAGE (e.g. =COUNTIF(W5:W64,">"&W68))
Data below the AVERAGE (e.g.
=ABS(COUNTIF(W5:W64,">"&W68)-COUNTIF(W5:W64,">0"))

So, I now have this 13th spreadsheet that lists the results from the 12
worksheets/months. For example:
Row C1:N1 Jan Feb Mar .... Dec lists the individual results from SUM
Row C2:N2 Jan Feb Mar .... Dec lists the individual results from MIN
Row C3:N3 Jan Feb Mar .... Dec lists the individual results from MAX
Row C4:N4 Jan Feb Mar .... Dec lists the individual results from AVERAGE
Row C5:N5 Jan Feb Mar .... Dec lists the individual results from STDEV
Row C6:N6 Jan Feb Mar .... Dec lists the individual results from Data above
the AVERAGE (e.g. =COUNTIF(W5:W64,">"&W68))
Row C7:N7 Jan Feb Mar .... Dec lists the individual results from Data below
the AVERAGE (e.g.
=ABS(COUNTIF(W5:W64,">"&W68)-COUNTIF(W5:W64,">0"))

My question is how I can summarize in Column M1:M7 the results I got
for each formulae/month. For example, SUM is pretty
straightforward as you only need to =SUM(C1:N1). But, my problem is how to
accurately sumarize the other results.

Thank you for your help.

Orlando

ORlando,

SUM use SUM
MIN use MIN
MAX use MAX

Average - use SUM (From above) / COUNT of all 12

Stdev, and some other populations statistics, require that the numbers be passed in their entirety
to the function.

Ideally, you should change the structure of your workbook and use ONE sheet of data, with a column
of identifiers (could be date, or month name), and then use a pivot table to get your data
statistics. Much easier to maintain, and certainly faster and less error prone.

HTH,
Bernie
MS Excel MVP


"OrlandoFreeman" <OrlandoFreeman@discussions.microsoft.com> wrote in message
news:0322356C-52AF-406A-88CF-1D9AD1581821@microsoft.com...
>I have 12 worksheets of certain data, that is one worksheet for every month
> of the year (i.e. Jan-Dec). In each worksheet/month I have already calculated
> the
> following functions/formulae:
> SUM
> MIN
> MAX
> AVERAGE
> STDEV
> Data above the AVERAGE (e.g. =COUNTIF(W5:W64,">"&W68))
> Data below the AVERAGE (e.g.
> =ABS(COUNTIF(W5:W64,">"&W68)-COUNTIF(W5:W64,">0"))
>
> So, I now have this 13th spreadsheet that lists the results from the 12
> worksheets/months. For example:
> Row C1:N1 Jan Feb Mar .... Dec lists the individual results from SUM
> Row C2:N2 Jan Feb Mar .... Dec lists the individual results from MIN
> Row C3:N3 Jan Feb Mar .... Dec lists the individual results from MAX
> Row C4:N4 Jan Feb Mar .... Dec lists the individual results from AVERAGE
> Row C5:N5 Jan Feb Mar .... Dec lists the individual results from STDEV
> Row C6:N6 Jan Feb Mar .... Dec lists the individual results from Data above
> the AVERAGE (e.g. =COUNTIF(W5:W64,">"&W68))
> Row C7:N7 Jan Feb Mar .... Dec lists the individual results from Data below
> the AVERAGE (e.g.
> =ABS(COUNTIF(W5:W64,">"&W68)-COUNTIF(W5:W64,">0"))
>
> My question is how I can summarize in Column M1:M7 the results I got
> for each formulae/month. For example, SUM is pretty
> straightforward as you only need to =SUM(C1:N1). But, my problem is how to
> accurately sumarize the other results.
>
> Thank you for your help.
>
> Orlando
>