subtotals

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On an excel worksheet, IHow can I do some columns with subtotals of sum and other columns with subtotals of average?
 
One way, do a regular subtotal (data>subtotal) where the default is sum
select all columns where you want this (add subtotal to), click OK,
Now select the data in the columns where you want average instead of sum
(hold down ctrl while selecting with the mouse)
Press F5, select special, select formulas (this assumes that the raw data
are not formulas)
click OK and you will get something like

=SUBTOTAL(9,Range)

Now high light the 9 in the formula and change it to 1, press Ctrl + Enter
Done! If your raw data are formulas you need to manually select all subtotal
formulas
and then do the change from

=SUBTOTAL(9,C2:C5)

to

=SUBTOTAL(1,C2:C5)

--

Regards,

Peo Sjoblom


paint said:
On an excel worksheet, IHow can I do some columns with subtotals of sum
Click to expand...
and other columns with subtotals of average?
 
Stop! I take that back, I should have tested first, a simpler and better
solution would
be to select the columns you want the average and do edit>replace, find what

=SUBTOTAL(9

replace with

=SUBTOTAL(1

that will do it. The first solution will screw up references, I apologize
for that
 
Back
Top