Good Afternoon All,
I've spent the last few hours searching for this issue with no luck, so I apologize if this has been posted already.
If I add a subtotal to a pivot table and make the subtotal calculate as "Average", I'm wondering if there is a way to adjust how excel calculates the average. Below is a snippet of sample data, similar to my pivot table. I have a few sales teams where I show each individuals total, then there is a roll up into the team total. I also have it set up so that I can see the team average. However my data set feeding the table has a line of data for each day of the sales cycle. As a reference, TEAM A has 383 lines of data in the date set. It appears that when I add in the subtotal Average, excel is calculating the average based on the number of lines of data in the data set and not the number of employees on the team. The numbers in Red font to the right of the table are my expected resultes (Taking the sum divided by 6). Is there anyway I can acheive this with in the table, and not use formulas in external columns.
I've spent the last few hours searching for this issue with no luck, so I apologize if this has been posted already.
If I add a subtotal to a pivot table and make the subtotal calculate as "Average", I'm wondering if there is a way to adjust how excel calculates the average. Below is a snippet of sample data, similar to my pivot table. I have a few sales teams where I show each individuals total, then there is a roll up into the team total. I also have it set up so that I can see the team average. However my data set feeding the table has a line of data for each day of the sales cycle. As a reference, TEAM A has 383 lines of data in the date set. It appears that when I add in the subtotal Average, excel is calculating the average based on the number of lines of data in the data set and not the number of employees on the team. The numbers in Red font to the right of the table are my expected resultes (Taking the sum divided by 6). Is there anyway I can acheive this with in the table, and not use formulas in external columns.
| Book1 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 3 | Row Labels | Sum of Leads | Sum of Contacted | Sum of Sale | |||||
| 4 | North | 1062.00 | 678.00 | 265.00 | |||||
| 5 | TEAM A | ||||||||
| 6 | Employee 1 | 77.00 | 58.00 | 23.00 | |||||
| 7 | Employee 2 | 96.00 | 90.00 | 35.00 | |||||
| 8 | Employee 3 | 83.00 | 73.00 | 21.00 | |||||
| 9 | Employee 4 | 124.00 | 73.00 | 39.00 | |||||
| 10 | Employee 5 | 96.00 | 81.00 | 21.00 | |||||
| 11 | Employee 6 | 81.00 | 51.00 | 18.00 | |||||
| 12 | TEAM A Sum | 557.00 | 426.00 | 157.00 | |||||
| 13 | TEAM A Average | 1.45 | 1.11 | 0.41 | 93 | 71 | 26 | ||
| 14 | TEAM B | ||||||||
| 15 | Employee 1 | 175.00 | 79.00 | 38.00 | |||||
| 16 | Employee 2 | 93.00 | 58.00 | 32.00 | |||||
| 17 | Employee 3 | 91.00 | 48.00 | 18.00 | |||||
| 18 | Employee 4 | 87.00 | 49.00 | 19.00 | |||||
| 19 | Employee 5 | 59.00 | 17.00 | 1.00 | |||||
| 20 | Employee 6 | 0.00 | 1.00 | 0.00 | |||||
| 21 | TEAM B Sum | 505.00 | 252.00 | 108.00 | |||||
| 22 | TEAM B Average | 1.84 | 0.92 | 0.39 | 84 | 42 | 18 | ||
| 23 | Grand Total | 1062.00 | 678.00 | 265.00 | |||||
Sheet3 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| E13 | =B12/6 | |
| E22 | =B21/6 | |
| F13 | =C12/6 | |
| F22 | =C21/6 | |
| G13 | =D12/6 | |
| G22 | =D21/6 | |
