I am in need of a pivot table that calculates the Weekly Average based on Daily Scores given the following information:
1. The Daily Score is based on a point system in .25 increments. If there are two scores for one day (I.E. 06/01/2011), the scores should NOT be averaged. Instead, if the Daily Score should be calculated by subtracting the number of point deductions from 100. (EXAMPLE: 06/01/2011 results are 99.5 and 99.25, so the final Daily Score for 06/01/2011 would become 98.75).
2. Once the final Daily Score is calculated correctly based on the point system, I need to group the final "Daily Score" by week number.
3. After the Daily Score is grouped by week number, I need to AVERAGE the final Daily Score for each date within that group for a weekly average.
4. This pivot table will be used calendar year and will have multiple Rep Names
1. The Daily Score is based on a point system in .25 increments. If there are two scores for one day (I.E. 06/01/2011), the scores should NOT be averaged. Instead, if the Daily Score should be calculated by subtracting the number of point deductions from 100. (EXAMPLE: 06/01/2011 results are 99.5 and 99.25, so the final Daily Score for 06/01/2011 would become 98.75).
2. Once the final Daily Score is calculated correctly based on the point system, I need to group the final "Daily Score" by week number.
3. After the Daily Score is grouped by week number, I need to AVERAGE the final Daily Score for each date within that group for a weekly average.
4. This pivot table will be used calendar year and will have multiple Rep Names
