gmooney
Active Member
- Joined
- Oct 21, 2004
- Messages
- 254
- Office Version
- 365
- Platform
- Windows
I have the below pivot table with 2 value columns that I need for any subtotals (in this example Romaine Leaves is the sub total of the 5 rows) to calculate the weighted average of the 5 rows (the math for this is the 3 columns to the right of the pivot table. Instead of 10.9 showing for Average of U/SW the correct answer should be 14.5. The 10.9 is simply the straight average of the 5 rows and that isn't the result that I want.
Any helpers out there?
[TABLE="width: 1223"]
<tbody>[TR]
[TD]
Row Labels[/TD]
[TD] Sum of [$][/TD]
[TD] Average of [U/S/W][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Romaine Leaves[/TD]
[TD="align: right"]$29,413,131[/TD]
[TD="align: right"]10.9[/TD]
[TD] Sum $ * Avg of U/S/W[/TD]
[TD]% of Total[/TD]
[TD] Weighted Avg[/TD]
[/TR]
[TR]
[TD]Brand A Washed Red Romaine Leaves[/TD]
[TD="align: right"]$4[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0.0[/TD]
[/TR]
[TR]
[TD]Brand A Washed Romaine Leaves[/TD]
[TD="align: right"]$3,874,598[/TD]
[TD="align: right"]13.2[/TD]
[TD="align: right"]51,284,182[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]1.6[/TD]
[/TR]
[TR]
[TD]Brand B Washed Leaves[/TD]
[TD="align: right"]$5,291,218[/TD]
[TD="align: right"]10.3[/TD]
[TD="align: right"]54,314,349[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]1.3[/TD]
[/TR]
[TR]
[TD]Brand B Washed Red Leaves[/TD]
[TD="align: right"]$7,925,547[/TD]
[TD="align: right"]13.6[/TD]
[TD="align: right"]107,890,470[/TD]
[TD="align: right"]26%[/TD]
[TD="align: right"]3.5[/TD]
[/TR]
[TR]
[TD]Brand B Washed Romaine Leaves[/TD]
[TD="align: right"]$12,321,764[/TD]
[TD="align: right"]16.4[/TD]
[TD="align: right"]202,138,544[/TD]
[TD="align: right"]49%[/TD]
[TD="align: right"]8.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]415,627,550[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]14.5[/TD]
[/TR]
</tbody>[/TABLE]
Any helpers out there?
[TABLE="width: 1223"]
<tbody>[TR]
[TD]
Row Labels[/TD]
[TD] Sum of [$][/TD]
[TD] Average of [U/S/W][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Romaine Leaves[/TD]
[TD="align: right"]$29,413,131[/TD]
[TD="align: right"]10.9[/TD]
[TD] Sum $ * Avg of U/S/W[/TD]
[TD]% of Total[/TD]
[TD] Weighted Avg[/TD]
[/TR]
[TR]
[TD]Brand A Washed Red Romaine Leaves[/TD]
[TD="align: right"]$4[/TD]
[TD="align: right"]1.0[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0%[/TD]
[TD="align: right"]0.0[/TD]
[/TR]
[TR]
[TD]Brand A Washed Romaine Leaves[/TD]
[TD="align: right"]$3,874,598[/TD]
[TD="align: right"]13.2[/TD]
[TD="align: right"]51,284,182[/TD]
[TD="align: right"]12%[/TD]
[TD="align: right"]1.6[/TD]
[/TR]
[TR]
[TD]Brand B Washed Leaves[/TD]
[TD="align: right"]$5,291,218[/TD]
[TD="align: right"]10.3[/TD]
[TD="align: right"]54,314,349[/TD]
[TD="align: right"]13%[/TD]
[TD="align: right"]1.3[/TD]
[/TR]
[TR]
[TD]Brand B Washed Red Leaves[/TD]
[TD="align: right"]$7,925,547[/TD]
[TD="align: right"]13.6[/TD]
[TD="align: right"]107,890,470[/TD]
[TD="align: right"]26%[/TD]
[TD="align: right"]3.5[/TD]
[/TR]
[TR]
[TD]Brand B Washed Romaine Leaves[/TD]
[TD="align: right"]$12,321,764[/TD]
[TD="align: right"]16.4[/TD]
[TD="align: right"]202,138,544[/TD]
[TD="align: right"]49%[/TD]
[TD="align: right"]8.0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]415,627,550[/TD]
[TD="align: right"]100%[/TD]
[TD="align: right"]14.5[/TD]
[/TR]
</tbody>[/TABLE]