Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
subtotal calculation
(ignore previous post, I hit send before I was done...)
I have a spreadsheet similar to this: Year City State Amount 03 Albertville AL 300 04 Albertville AL 300 Variance 0 03 Alexander City AL 24 04 Alexander City AL 60 Variance 36 03 Andalusia AL 180 04 Andalusia AL 240 Variance 60 I am programatically inserting the Variance row and calculating the difference between the two years. This works well. Now, I want to provide a grand total of all the variances. I can do this in code as well, but I really want to use the subtotal function so that if a user filters on a particular state/city, etc. the subtotal will only represent the visible rows. I tried building a subtotal calculation programatically in code, but ran into a limit of how many cells could be included in the formula. Any other ideas on how to do this so that the users can filter the data and have the totals re-calculated? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
subtotal calculation
Why not use the built in Subtotal function?
Also, you said you are adding variances? I am not sure that is meaningful. -- Regards, Tom Ogilvy "Erin" wrote in message ... (ignore previous post, I hit send before I was done...) I have a spreadsheet similar to this: Year City State Amount 03 Albertville AL 300 04 Albertville AL 300 Variance 0 03 Alexander City AL 24 04 Alexander City AL 60 Variance 36 03 Andalusia AL 180 04 Andalusia AL 240 Variance 60 I am programatically inserting the Variance row and calculating the difference between the two years. This works well. Now, I want to provide a grand total of all the variances. I can do this in code as well, but I really want to use the subtotal function so that if a user filters on a particular state/city, etc. the subtotal will only represent the visible rows. I tried building a subtotal calculation programatically in code, but ran into a limit of how many cells could be included in the formula. Any other ideas on how to do this so that the users can filter the data and have the totals re-calculated? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
subtotal calculation
I couldn't get the built-in subtotal function to calculate
DIFFERENCES between two numbers (only sums). I need it to show the difference in customers ordered amount across the 2 years. Anyway... I've figured it out. I'm still calculating the variance rows myself, but then I'm also copying those off to some hidden columns to the right on the spreadsheet. My sub-totals at the bottom are then based on those hidden columns. So, when a user filters, it then re-calculates just perfectly! Thanks for the help anyway. -----Original Message----- Why not use the built in Subtotal function? Also, you said you are adding variances? I am not sure that is meaningful. -- Regards, Tom Ogilvy "Erin" wrote in message ... (ignore previous post, I hit send before I was done...) I have a spreadsheet similar to this: Year City State Amount 03 Albertville AL 300 04 Albertville AL 300 Variance 0 03 Alexander City AL 24 04 Alexander City AL 60 Variance 36 03 Andalusia AL 180 04 Andalusia AL 240 Variance 60 I am programatically inserting the Variance row and calculating the difference between the two years. This works well. Now, I want to provide a grand total of all the variances. I can do this in code as well, but I really want to use the subtotal function so that if a user filters on a particular state/city, etc. the subtotal will only represent the visible rows. I tried building a subtotal calculation programatically in code, but ran into a limit of how many cells could be included in the formula. Any other ideas on how to do this so that the users can filter the data and have the totals re-calculated? . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Subtotal To Include Item Description On Subtotal Line | Excel Discussion (Misc queries) | |||
Subtotal of Pivot "Max" calculation problem | Excel Discussion (Misc queries) | |||
Using Subtotal field of a pivot table in a calculation | Excel Discussion (Misc queries) | |||
% of subtotal custom calculation in a pivot table | Excel Discussion (Misc queries) | |||
pasting to subtotal lines without replacing hidden -non-subtotal l | Excel Discussion (Misc queries) |