subtotals appear in the wrong place after running macro

shayb

Board Regular
Joined
Apr 21, 2004
Messages
81
hi everyone. :rolleyes:

I hope i can explain this well to you all. I start by recording a macro. To get the Monthly Order Reduction Opportunity, first I have to get the Groups Average Order Size (Groups Total Sales divided by Groups Total Order Count). Then I come up with the target number of orders they should be placing, based on Groups Avg Order Size (Total Sales divided by Groups Avg Order Size). To get the Monthly Order Reduction, I take the Target Order Count minus the Actual Order Count and divide that by 6 (for 6 months).

First I take out all of the positive numbers (everything 0 and greater) because that means they're right on target or exceeding their target. Now I have the negative numbers left.

I ctrl+F all the '-' and replace them with blanks, so now I have whole numbers. Then I make a copy of this spreadsheet and move it over into the same book.

After that I remove the subtotals for the other columns and then do a sum just on the Monthly Order Reduction Column. Then I just copy that column, with the subtotals, to the original spreadsheet and it lines up. The problem is when I stop the macro and run it again, the subtotals are not right and are placed a few spaces under where they should be. Here is an example. I don't know where the '38' comes from nor the '0's'. '46' is supposed to be in the NY Total and 50 in the Grand Total.

Can anyone help?

subtotals.jpg
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

shayb

Board Regular
Joined
Apr 21, 2004
Messages
81
anyone have any idea as to what i can search for on the board?

i've already used 'subtotal' and 'subtotals' and still can't find anything.
 

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
As i understand from the exhibit, you already have separate columns for the various stages of the calculations. Now you want the sum of only the negative numbers for each group in column J. If this is correct, there are two ways of achieving this:

1. In the formula used to derive Column J, change the structure of the formula to bring up only negative numbers. If you are not able to do this, post your existing formula for suggestions.

2. If you need to show all values (positive, negative, zero) in Column J but have the sum of the negative values only, use a suitable sumif formula e.g., SUMIF(A1:A100,"<0",A1:A100)
 

Watch MrExcel Video

Forum statistics

Threads
1,129,553
Messages
5,636,976
Members
416,953
Latest member
broexc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top