Subtotal Function...

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

Guest

Hello All,

I have a macro that creates subtotals for a sheet of data. However, I then
insert rows below each subtotal to calculate percentages for each subtotal.
This works fine but afterwards, the grand total includes these percentages in
its figure. So, my grand total is off by a few dollars or cents depending
upon the percentages above it. Does anyone have any suggestions on how to
counter this?

Thanks for your time.

Trevor
 
Move the % calculation one column to the left or right of the subtotaled
column.
HTH,
Gary Brown
 
Or create a text format something like...
=TEXT(1/4,"0.00%")
which will show 25.00% but you could use references like A1/A2
HTH,
Gary Brown
 
The grandtotal should be built with a subtotal formula. A subtotal formula
will ignore any cell in it action range which also contains a subtotal
formula - so if you can build your percentages by using a subtotal formula,
you should be OK.

This works even if you make the subtotal portion a dummy. For example, in a
subtotal in column E, I put in a formula
=SUBTOTAL(9,A2)+1000000
(A2 was empty or contained text), the 100000 was ignored in the total
calculated by another subtotal formula.
 
Back
Top