E wrote:
At work, I have an assignment to make summaries of spreadsheet data. It's
excel 2002 that I work with.

I'll show you want I'm trying to do by showing the format of these
spreadsheets:

................| 8/03 | 8/04| 8/05| 8/06 | Average | Sum
10:00am| 9 0 3 2
11:00am| 5 8 6 1
12:00am| 2 3 2 7
1:00pm | 1 3 8 9
Average
Sum


When I try to make the averages going down and going across, it's fine
(since you just highlight the area and click "ave" under the formula menu).
Yet when I click the sum and just highlight the spreadsheet data and the sum
perimeter (without highlighting the average perimeter), I find it doesn't
give the right sums on on side. Instead I'll have to manually correct the
areas to be summed (because it will do something like sum areas A8 to A15
when it was supposed to sum A9 to A15.

I have many spreadsheets to do with the same format as described and I'm
looking for a way to make this process automatic instead of needing to
manually input formulas. I tried doing a pivot chart in excel 2007 at home,
but that didn't work with the excel 2002 program at work. I don't know what
to do here but there must be a way to make the process easier.

If you are stuck with this layout I can't think of an easy, maintainable
solution, short of hacking out some fallible VBA code. You will have
to write the =SUM formulas yourself, both in the rows and in the
columns. At least, the formulas can each be written once, and filled
down/right. The problem becomes maintenance, because as you add new
row/column categories, you will have to redo the formulas. Multiply that
by a number of worksheets/workbooks and you will need quite a few
aspirin--and possibly therapy.

I would take a different approach for storing the raw data, if at all
possible. This layout is what one would term "normalized" and
facilitates very easy updates and analysis:

In cols A:C --

Date Time Value
08/03/2008 10:00am 9
08/03/2008 11:00am 5
08/03/2008 12:00am 2
08/03/2008 1:00pm 1
08/04/2008 10:00am 0
08/04/2008 11:00am 8
08/04/2008 12:00am 3
08/04/2008 1:00pm 3
08/05/2008 10:00am 3
08/05/2008 11:00am 6
08/05/2008 12:00am 2
08/05/2008 1:00pm 8
08/06/2008 10:00am 2
08/06/2008 11:00am 1
08/06/2008 12:00am 7
08/06/2008 1:00pm 9


With the data in this configuration you can create a pivot table against
columns A:C, arrange the Date and Time categories as you like, and
obtain both the sum and average of the Value field across the two
categories automatically. After adding new data, the /only/ thing that
you need to do to obtain a summary is refresh the pivot table (one click).

Does this help?