Dates, averages and headaches

maxouventura

New Member
Joined
Feb 10, 2005
Messages
2
:(

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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

P Sitaram

Well-known Member
Joined
Jun 24, 2003
Messages
1,974
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.
 

maxouventura

New Member
Joined
Feb 10, 2005
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,494
Messages
5,602,004
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top