Formula for Average

[XANADU79]

Hello.

I'm looking for a formula to average data by week number.

For example: Week 23
06/01/2011= 99%
06/02/2011= 100%
06/03/2011= 100%

Week 24
06/06/2011= 100%
06/07/2011 = 95%

I need a formula to average the daily results within an assigned week number.

[XANADU79]

This would be ideal if it could be done in a pivot table also.

[teylyn]

Hello,

it can easily be done in a pivot table, if your source data is one contiguous table (no blank rows or columns). You can group your data by days in increments of 7 days. Or you can include a column in your data that shows the week number and use that as a row field in the pivot table.

With a sample file, it would be much easier showing you how to do it.

cheers,

[XANADU79]

I have attached a sample document. On the "STATS" sheet

Column A is the individual's name
Column B is the Date of the week
Column C is the Week Number
Column D is the Daily Total

I need to group by week and average the daily totals within a given week.

For example: 06/2 and 06/3 for "Michelle" - Week 23 99.5 + 99.75= 199.25/2= 99.62%. However, when I group by Week, the average is skewed.

[teylyn]

I have attached a sample document.

Can't see it. Care to try again?

[XANADU79]

Sorry about that. Here you go.,

[teylyn]

Hello,

swap the position of Week and Date in the pivot table, so your data is first grouped by week and then by date.

Also, use the field "Total" in the Values and set it to Average. Currently, you are using a calculated field "Daily Total", which does not produce the correct result. There are a few other calculated fields defined, most of them using the formula =100-Total. They don't make much sense. What are these for?

see attached for the correct daily averages.

cheers,

[XANADU79]

The "Total" column in the data represents deductions. Analysts receive deductions in increments of .25. Instead of "averaging" the daily result and then averaging those daily results by grouping by week is not what I need.

Instead, I need a formula that assumes that each individual starts with 100 points for the day. Then subtract the deductions which is the "Total" field (hence 100-TOTAL). I then need to average the daily results based on the point system and then group those results by week to determine the weekly average (based on the point system rather than the daily average).

Does that make sense? It seems complicated.

[teylyn]

With the pivot table in the structure Week > Day, you can re-apply your calculated fields.

As per the logic, I'm afraid you've lost me.

What you definitely can't do is have the day first and then the week (as in your attached file). That will break the flow of the data hierarchy.