Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 appreciated. TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Calculations in Subtotal
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 macro. 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. Regards, Tom Ogilvy "CST" wrote in message om... 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 appreciated. TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Calculations in Subtotal
Hi,
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 appreciated. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Calculations in Subtotal
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), _ .UsedRange).Cells 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" Next End With End Sub Regards, Tom Ogilvy "Con Tang" wrote in message ... Hi, 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 appreciated. *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Calculations in Subtotal
Thank you! I will give it a try.
*** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pvt Tables - Custom Calculations - HELP ME!! | Excel Worksheet Functions | |||
getpivotdata custom subtotal | Excel Worksheet Functions | |||
getpivotdata custom subtotal | Excel Worksheet Functions | |||
How do I setup a custom subtotal? | Excel Worksheet Functions | |||
Custom Calculations | Excel Discussion (Misc queries) |