Hi Every one, I have been working on a pivot table. It has the following structure:
- ROW Labels: The material name
- COLUMN Labels: Month
- Report Filter: Year
- Values: Sum of Quantity
What this is is doing is creating at pivot table, where I can see the usage of each material (the number of materials can vary) per month (the number of months can varies, could be 3, 6 or 9 or 12). So, my month display the amount of quantity that was used for that particular month. After the month column labels, I have the grand total field which is part of the pivot table.
I would like to create two extra columns or field next to the grand total. I want the average usage per month and the std dev of that material. I cannot use the formula and create a field using the pivot desing features because it would only give me the average usage for each month, instead per month. Let me clarify with the following table (the pivot table with the fileds that I have described before)
So with the data generate with the pivot table I want to add the column Average per month, and Std Dev of the material which is
Average (bASKET BALL)= Average(2 +3 + 0) or ((2+3+0)/3) and
STD DEV (Basket ball) = STDDEV(2+ 3+ 0)
This is for each material.
Please any help or suggestion would be appreciate. Thanks!!
<tbody>
</tbody>
The hard part of this is that, the length of material would always varies everytime that I try to run the macro, also the number of month varies. This pivot table could be run for every quarter, or every two quarters, and this is the reason of why months could vary, as well for the material, since material are not sell in the period that I have selected. I think that I need to create a VBA that would create the column of Average in the available column and so forth.
I have no idea how to start to write the code. I am very new to VBA. I have been writing code for creating my pivot table. As I said before, any help or suggestion would be appreciate.
Thank you guys!!!
- ROW Labels: The material name
- COLUMN Labels: Month
- Report Filter: Year
- Values: Sum of Quantity
What this is is doing is creating at pivot table, where I can see the usage of each material (the number of materials can vary) per month (the number of months can varies, could be 3, 6 or 9 or 12). So, my month display the amount of quantity that was used for that particular month. After the month column labels, I have the grand total field which is part of the pivot table.
I would like to create two extra columns or field next to the grand total. I want the average usage per month and the std dev of that material. I cannot use the formula and create a field using the pivot desing features because it would only give me the average usage for each month, instead per month. Let me clarify with the following table (the pivot table with the fileds that I have described before)
So with the data generate with the pivot table I want to add the column Average per month, and Std Dev of the material which is
Average (bASKET BALL)= Average(2 +3 + 0) or ((2+3+0)/3) and
STD DEV (Basket ball) = STDDEV(2+ 3+ 0)
This is for each material.
Please any help or suggestion would be appreciate. Thanks!!
| Material Name | Jun | Jul | Aug | Grand Total Avg per Month Std |
| Basket ball | 2 | 3 | 0 | 5 |
| Volley ball | 1 | 0 | 0 | 1 |
| Soccer ball | 4 | 5 | 7 | 16 |
| Soft ball | 0 | 0 | 1 | 1 |
| Golf ball | 2 | 3 | 4 | 7 |
| Base ball | 1 | 0 | 6 | 7 |
<tbody>
</tbody>
The hard part of this is that, the length of material would always varies everytime that I try to run the macro, also the number of month varies. This pivot table could be run for every quarter, or every two quarters, and this is the reason of why months could vary, as well for the material, since material are not sell in the period that I have selected. I think that I need to create a VBA that would create the column of Average in the available column and so forth.
I have no idea how to start to write the code. I am very new to VBA. I have been writing code for creating my pivot table. As I said before, any help or suggestion would be appreciate.
Thank you guys!!!
