Dates, averages and headaches

[maxouventura]

Hello everyone,

I wonder if anyone can offer a formula which I may use to assist me in a spreadsheet on which I'm currently working. I have a list of 16 clients, against which are a list of dates and values. The date represents the date a client was seen by a member of staff and the value represents the outcome of that meeting (outcomes have been coded 1-9). So for example I might have something like...

Client Date Outcome

1 12/03/04 5
1 13/03/04 4
1 15/03/04 3

and so on...

What I need to do is create graphs for each client, depicting the average outcome value per actual calendar week. Some clients were seen maybe only once in a week, whereas others were seen several times in a week.

Is there any way I can ask Excel to search a range of data and calculate the average outcomes for each week commencing (Monday) date? Failing that, is there any way I can ask Excel to search a range of dates based upon week commencing dates, extract the values (outcome codes) and then I'll calulate the averages myself?

I'd be grateful for any advice.

Thanks

Chris Elliott

 

[P Sitaram]

1. Setup a table of week beginning dates (Mondays) with week numbers (on the same sheet or different sheet). This should be easy, as you need to enter only the first Monday and keep adding 7. The week number needs to be increased by 1 for every week.

2. In your data, add a column which will VLOOKUP the date in this date and fetch the corresponding week number.

3. The rest can be handled by a Pivot table.

 

[Aladin Akyurek]

Pivot Tables would enable you to create chart per client. An intro to pivot tables if you're interested...

http://www.peltiertech.com/Excel/Pivots/pivottables.htm

 

[maxouventura]

Hi again,

Thanks for your advice - I'll have a look at using a pivot table - something new for me, but it sounds very useful.

Thanks again,

Chris Elliott