Trying to make both averages AND sums of one spreadsheet of data

[E]

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.

 

[Bob Phillips]

Make the pivot chart in 2002 at home then take it into 2007. See if that
works.

 

[smartin]

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?

 

[jamescox]

Excel's formula menu is helping you with the Average because you are in
a cell that has numbers immediately adjacent to the cell (either in a
row, if you are at the right of the block of cells, or in a column if
you are at the bottom of the block of cells). It's assuming that you
want to average the cells and therefore is defaulting in the range of
cells to the left or above.

When you try to do the Sum, Excel is still trying to help, but now
you've added a new row or column to the block of cells and Excel isn't
capable of knowing that you really don't want to include the calculated
averages in the sum.

The good news is that you don't have to rely on the formula menu to
create the formulas for you.

Click on the cell you want the Sum to be in, and type the following:

=Sum(

Then click and hold on the top (or left-most cell whose value you want
included in the sum) and drag the cursor down to the bottom (or
right-most) cell you want included in the sum.

Then just press the Enter key and Excel will add the range of cells you
selected and a closing parenthesis) to the cell formula and you will
have your sum.

Note that once you get the Average and Sum formulas set up at the
bottom of a column or at the left of a row, you can copy the two cells
that hold the formulas and paste them into the rest of the columns (or
rows) that you need the Average and Sum of. (You have to copy and paste
the column formulas on the columns - then copy the row formulas and
paste them on the rows. If you tried copying the column formulas for
the average and sum and pasted them on at the right side of the block of
numbers in the rows, you wouldn't get the right answers).

Hope this helps...