Custom Calculations in Subtotal



Hi All,

Is there a way to have custom calculations in a subtotal. Using Data
--> Subtotal gives you the standard (sum, avg, count, etc.).

Name Sales Hist Sales Sale +/- Hist
---- ----- ---------- -------------
A 5 6 -16.67
Sub Total 5 6 -16.67
B 4 5 -20.00
Sub Total 4 5 -20.00
C 3 2 -50.00
Sub Total 3 2 -50.00
D 1 2 -50.00
Sub Total 1 2 -50.00
Grand Total 13 15 -13.33

Sales and Hist Sales are summed using Excel's built in subtotaling
feature, however, I would like a custom calculation at each subtotal =
(sales-hist sales)/sales*100. All subtotals should be on the same
line. So, the subtotal for sales, hist sales, and sales +/- should be
on the same line. How can I accomplish this? Any help would be much




Tom Ogilvy

There is no way to modify what is offered.

You would probably need to write a macro that executes the subtotal command
and then adds the additional formulas, or just do the whole thing in a

Since you are showing only one line of detail for Name, it is hard to tell
what the definition of +/- Sales would be on the subtotal line.

I assume it would be a percentage for the subtotals, but it could be a sum
of the percentages although that makes less sense.

Tom Ogilvy

Con Tang


I know/understand that I will need to do this in a macro, however, I
don't know how to do this. I can record a macro and it shows me the
code, but I don't know how to modify it to allow for custom
calculations. Sorry for being such a noob, but any help would be much

Tom Ogilvy

After you do the Subtotals, you can run this macro:

Sub AddFormulas()
Dim rng As Range
Dim cell As Range
With ActiveSheet
Set rng = Intersect(.Columns(1), _
rng(1).Offset(0, 3).Value = " Sale +/- Hist"
Set rng = rng.Offset(1, 0).Resize(rng.Rows.Count - 1)
For Each cell In rng
cell.Offset(0, 3).Formula = "=(" & cell.Offset(0, 1).Address _
& " - " & cell.Offset(0, 2).Address & ")/" & _
cell.Offset(0, 1).Address & "*100"
cell.Offset(0, 3).NumberFormat = "#0.00"

End With
End Sub

Tom Ogilvy


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question