hi everyone.
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?
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?