Taking a pro rata average

[maudley]

I have a 'Start Date' and an 'End Date' Column, which contain a data range on one sheet. And I have a table on another sheet which contains a 'Date' column and a 'Value' column.

I would like to calculate the average value for the period between the 'Start Date' and an 'End Date'

For example:

Sheet 1

'Start Date', 'End Date'
28/02/15, 03/03/15

Sheet 2

'Date', 'Value'
28/02/15, 20
01/03/15, 30
02/03/15, 30
03/03/15, 30

Calculation I would like to perform

There are 4 days between 28/02/15 and 03/03/15:

'Day', 'Value'
1, 20
2, 30
3, 30
4, 30

Average Value for date range: ((3 x 30) + (20)) / 4 = 27.5

Can I get excel to carry out the calculation in red without me having to restructure my data, I have over 2000 records that I need to do this for. Hence, I'd like to automate it as far as possible. I'm open to using VBA if necessary.

Thanks in advance.

 

[steve the fish]

Hi. You don't mention the version of excel you are using but I believe the function below was available from 2007 onwards. You will have to change the cell refs and you can use cell refs instead of the dates ive manually typed if you want.

=AVERAGEIFS(B2:B10,A2:A10,">="&"28/02/2015",A2:A10,"<="&"03/03/2015")