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

A

aik_05

New Member
Joined
Jul 27, 2017
Messages
3
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!
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Sort by date Sort by votes
[TABLE="width: 652"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Sorry, don't know how to upload an xls file

[/TD]
[TD][/TD]
[TD]Start date[/TD]
[TD="align: right"]20/01/2015[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]End date[/TD]
[TD="align: right"]15/06/2015[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Desired average[/TD]
[TD] 0.100[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Date[/TD]
[TD]Price 1[/TD]
[TD]Price 2[/TD]
[TD]Difference[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/01/2015[/TD]
[TD] 0.3350[/TD]
[TD] 0.435[/TD]
[TD] 0.100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14/01/2015[/TD]
[TD] 0.3380[/TD]
[TD] 0.435[/TD]
[TD] 0.097[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19/01/2015[/TD]
[TD] 0.3414[/TD]
[TD] 0.435[/TD]
[TD] 0.094[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26/01/2015[/TD]
[TD] 0.3418[/TD]
[TD] 0.435[/TD]
[TD] 0.093[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/01/2015[/TD]
[TD] 0.3294[/TD]
[TD] 0.435[/TD]
[TD] 0.106[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/02/2015[/TD]
[TD] 0.3379[/TD]
[TD] 0.435[/TD]
[TD] 0.097[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23/02/2015[/TD]
[TD] 0.3329[/TD]
[TD] 0.435[/TD]
[TD] 0.102[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04/03/2015[/TD]
[TD] 0.3409[/TD]
[TD] 0.435[/TD]
[TD] 0.094[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/03/2015[/TD]
[TD] 0.3399[/TD]
[TD] 0.435[/TD]
[TD] 0.095[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24/03/2015[/TD]
[TD] 0.3368[/TD]
[TD] 0.435[/TD]
[TD] 0.098[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/03/2015[/TD]
[TD] 0.3301[/TD]
[TD] 0.435[/TD]
[TD] 0.105[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/04/2015[/TD]
[TD] 0.3397[/TD]
[TD] 0.435[/TD]
[TD] 0.095[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23/04/2015[/TD]
[TD] 0.3408[/TD]
[TD] 0.435[/TD]
[TD] 0.094[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/05/2015[/TD]
[TD] 0.3363[/TD]
[TD] 0.435[/TD]
[TD] 0.099[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15/05/2015[/TD]
[TD] 0.3294[/TD]
[TD] 0.435[/TD]
[TD] 0.106[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]05/06/2015[/TD]
[TD] 0.3127[/TD]
[TD] 0.435[/TD]
[TD] 0.122[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17/06/2015[/TD]
[TD] 0.3080[/TD]
[TD] 0.435[/TD]
[TD] 0.127[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22/06/2015[/TD]
[TD] 0.3188[/TD]
[TD] 0.435[/TD]
[TD] 0.116[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29/06/2015[/TD]
[TD] 0.3243[/TD]
[TD] 0.435[/TD]
[TD] 0.111[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]02/07/2015[/TD]
[TD] 0.3347[/TD]
[TD] 0.435[/TD]
[TD] 0.100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]08/07/2015[/TD]
[TD] 0.3376[/TD]
[TD] 0.435[/TD]
[TD] 0.097[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13/07/2015[/TD]
[TD] 0.3398[/TD]
[TD] 0.435[/TD]
[TD] 0.095[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17/07/2015[/TD]
[TD] 0.3248[/TD]
[TD] 0.435[/TD]
[TD] 0.110[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23/07/2015[/TD]
[TD] 0.3177[/TD]
[TD] 0.435[/TD]
[TD] 0.117[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29/07/2015[/TD]
[TD] 0.3247[/TD]
[TD] 0.435[/TD]
[TD] 0.110[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24/08/2015[/TD]
[TD] 0.2998[/TD]
[TD] 0.435[/TD]
[TD] 0.135[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20/06/2016[/TD]
[TD] 0.4771[/TD]
[TD] 0.490[/TD]
[TD] 0.013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19/07/2016[/TD]
[TD] 0.4840[/TD]
[TD] 0.490[/TD]
[TD] 0.006[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26/07/2016[/TD]
[TD] 0.4776[/TD]
[TD] 0.490[/TD]
[TD] 0.012[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04/08/2016[/TD]
[TD] 0.4845[/TD]
[TD] 0.490[/TD]
[TD] 0.005[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/08/2016[/TD]
[TD] 0.4768[/TD]
[TD] 0.490[/TD]
[TD] 0.013[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23/09/2016[/TD]
[TD] 0.5284[/TD]
[TD] 0.490[/TD]
[TD] - 0.038[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28/09/2016[/TD]
[TD] 0.5461[/TD]
[TD] 0.490[/TD]
[TD] - 0.056[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04/10/2016[/TD]
[TD] 0.5394[/TD]
[TD] 0.490[/TD]
[TD] - 0.049[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12/10/2016[/TD]
[TD] 0.5380[/TD]
[TD] 0.490[/TD]
[TD] - 0.048[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26/10/2016[/TD]
[TD] 0.5395[/TD]
[TD] 0.490[/TD]
[TD] - 0.050[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]09/11/2016[/TD]
[TD] 0.5394[/TD]
[TD] 0.490[/TD]
[TD] - 0.049[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]30/11/2016[/TD]
[TD] 0.4876[/TD]
[TD] 0.490[/TD]
[TD] 0.002[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/12/2016[/TD]
[TD] 0.4932[/TD]
[TD] 0.490[/TD]
[TD] - 0.003[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]04/01/2017[/TD]
[TD] 0.5180[/TD]
[TD] 0.605[/TD]
[TD] 0.087[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17/01/2017[/TD]
[TD] 0.5069[/TD]
[TD] 0.605[/TD]
[TD] 0.098[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27/01/2017[/TD]
[TD] 0.5060[/TD]
[TD] 0.605[/TD]
[TD] 0.099[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21/02/2017[/TD]
[TD] 0.5264[/TD]
[TD] 0.605[/TD]
[TD] 0.079[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]07/03/2017[/TD]
[TD] 0.4866[/TD]
[TD] 0.605[/TD]
[TD] 0.118[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16/03/2017[/TD]
[TD] 0.4773[/TD]
[TD] 0.605[/TD]
[TD] 0.128[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18/05/2017[/TD]
[TD] 0.4121[/TD]
[TD] 0.605[/TD]
[TD] 0.193[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26/05/2017[/TD]
[TD] 0.3975[/TD]
[TD] 0.605[/TD]
[TD] 0.208[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Column with Min/Average values at the end of Pivot Table
Replies
0
Views
129
Nurces
N
B
  • Solved
Averaging the difference between two dates and separating them into categories
Replies
5
Views
324
awoohaw
awoohaw
D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
130
Denny57
D
D
  • Question Question
weighted slope intercept adjustment between two different value sets
Replies
0
Views
421
Dspace
D
W
  • Solved
Determine if a month falls between 2 dates
Replies
4
Views
275
Joe4
Joe4

Forum statistics

Threads
1,223,893
Messages
6,175,249
Members
452,623
Latest member
Techenthusiast

Share this page

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
Back
Top