Average of difference between numbers in two sets of columns between two specific date ranges

[aik_05]

Hi all,

I would like to calculate the average of the difference between numbers in two sets of columns between two defined date intervals. In other words, a formula that does the following:
Average of (Price 2 minus Price 1) where Price 2 and Price 1 fall between Start date and End date.


In short, I would like a formula that calculates the value in H7 without having to add the "Difference" column and that uses the dates I set as lower and upper bound for the calculation.

I hope I was clear!!

Thank you!

 

[aik_05]

		Sorry, don't know how to upload an xls file		Start date	20/01/2015
				End date	15/06/2015
				Desired average	0.100
Date	Price 1	Price 2	Difference
07/01/2015	0.3350	0.435	0.100
14/01/2015	0.3380	0.435	0.097
19/01/2015	0.3414	0.435	0.094
26/01/2015	0.3418	0.435	0.093
30/01/2015	0.3294	0.435	0.106
12/02/2015	0.3379	0.435	0.097
23/02/2015	0.3329	0.435	0.102
04/03/2015	0.3409	0.435	0.094
16/03/2015	0.3399	0.435	0.095
24/03/2015	0.3368	0.435	0.098
30/03/2015	0.3301	0.435	0.105
07/04/2015	0.3397	0.435	0.095
23/04/2015	0.3408	0.435	0.094
07/05/2015	0.3363	0.435	0.099
15/05/2015	0.3294	0.435	0.106
05/06/2015	0.3127	0.435	0.122
17/06/2015	0.3080	0.435	0.127
22/06/2015	0.3188	0.435	0.116
29/06/2015	0.3243	0.435	0.111
02/07/2015	0.3347	0.435	0.100
08/07/2015	0.3376	0.435	0.097
13/07/2015	0.3398	0.435	0.095
17/07/2015	0.3248	0.435	0.110
23/07/2015	0.3177	0.435	0.117
29/07/2015	0.3247	0.435	0.110
24/08/2015	0.2998	0.435	0.135
20/06/2016	0.4771	0.490	0.013
19/07/2016	0.4840	0.490	0.006
26/07/2016	0.4776	0.490	0.012
04/08/2016	0.4845	0.490	0.005
12/08/2016	0.4768	0.490	0.013
23/09/2016	0.5284	0.490	- 0.038
28/09/2016	0.5461	0.490	- 0.056
04/10/2016	0.5394	0.490	- 0.049
12/10/2016	0.5380	0.490	- 0.048
26/10/2016	0.5395	0.490	- 0.050
09/11/2016	0.5394	0.490	- 0.049
30/11/2016	0.4876	0.490	0.002
07/12/2016	0.4932	0.490	- 0.003
04/01/2017	0.5180	0.605	0.087
17/01/2017	0.5069	0.605	0.098
27/01/2017	0.5060	0.605	0.099
21/02/2017	0.5264	0.605	0.079
07/03/2017	0.4866	0.605	0.118
16/03/2017	0.4773	0.605	0.128
18/05/2017	0.4121	0.605	0.193
26/05/2017	0.3975	0.605	0.208

<tbody>
</tbody>

 

[aik_05]

Anyone knows how to upload an xls doc?

 

[Peter_SSs]

Welcome to the MrExcel board!

Anyone knows how to upload an xls doc?

You can't. Have a good look at the Forum Use Guidelines