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.