Calculating 90 averages

[whynot]

I have several year's worth of data and i would to calculate average for 90 days. Is there a formula that would let me entering the starting point and end point to calculate the average of 90 days. This is what my data looks like. Thank you very much.

Date	EURUSD	Date	EURUSD
1/1/2018​	$1.2008​	1/1/2019​	$1.1432​
1/2/2018​	$1.2045​	1/2/2019​	$1.1355​
1/3/2018​	$1.2024​	1/3/2019​	$1.1392​
1/4/2018​	$1.2076​	1/4/2019​	$1.1410​
1/5/2018​	$1.2037​	1/5/2019​	$1.1410​
1/6/2018​	$1.2037​	1/6/2019​	$1.1410​
1/7/2018​	$1.2037​	1/7/2019​	$1.1462​
1/8/2018​	$1.1977​	1/8/2019​	$1.1442​
1/9/2018​	$1.1930​	1/9/2019​	$1.1511​
1/10/2018​	$1.1981​	1/10/2019​	$1.1521​
1/11/2018​	$1.2046​	1/11/2019​	$1.1478​
1/12/2018​	$1.2147​	1/12/2019​	$1.1478​
1/13/2018​	$1.2147​	1/13/2019​	$1.1478​
1/14/2018​	$1.2147​	1/14/2019​	$1.1468​
1/15/2018​	$1.2273​	1/15/2019​	$1.1433​
1/16/2018​	$1.2231​	1/16/2019​	$1.1395​
1/17/2018​	$1.2243​	1/17/2019​	$1.1380​
1/18/2018​	$1.2240​	1/18/2019​	$1.1367​
1/19/2018​	$1.2225​	1/19/2019​	$1.1367​
1/20/2018​	$1.2225​	1/20/2019​	$1.1367​
1/21/2018​	$1.2225​	1/21/2019​	$1.1366​
1/22/2018​	$1.2252​	1/22/2019​	$1.1356​
1/23/2018​	$1.2293​	1/23/2019​	$1.1379​
1/24/2018​	$1.2382​	1/24/2019​	$1.1354​
1/25/2018​	$1.2503​	1/25/2019​	$1.1398​
1/26/2018​	$1.2433​	1/26/2019​	$1.1398​
1/27/2018​	$1.2433​	1/27/2019​	$1.1398​
1/28/2018​	$1.2433​	1/28/2019​	$1.1435​
1/29/2018​	$1.2357​	1/29/2019​	$1.1415​
1/30/2018​	$1.2411​	1/30/2019​	$1.1426​
1/31/2018​	$1.2458​	1/31/2019​	$1.1475​
2/1/2018​	$1.2471​	2/1/2019​	$1.1468​
2/2/2018​	$1.2437​	2/2/2019​	$1.1468​
2/3/2018​	$1.2437​	2/3/2019​	$1.1468​
2/4/2018​	$1.2437​	2/4/2019​	$1.1428​
2/5/2018​	$1.2420​	2/5/2019​	$1.1410​
2/6/2018​	$1.2345​	2/6/2019​	$1.1379​
2/7/2018​	$1.2294​	2/7/2019​	$1.1356​
2/8/2018​	$1.2258​	2/8/2019​	$1.1328​
2/9/2018​	$1.2248​	2/9/2019​	$1.1328​
2/10/2018​	$1.2248​	2/10/2019​	$1.1328​
2/11/2018​	$1.2248​	2/11/2019​	$1.1276​
2/12/2018​	$1.2276​	2/12/2019​	$1.1299​
2/13/2018​	$1.2356​	2/13/2019​	$1.1288​
2/14/2018​	$1.2407​	2/14/2019​	$1.1275​
2/15/2018​	$1.2480​	2/15/2019​	$1.1263​
2/16/2018​	$1.2449​	2/16/2019​	$1.1263​
2/17/2018​	$1.2449​	2/17/2019​	$1.1263​
2/18/2018​	$1.2449​	2/18/2019​	$1.1307​
2/19/2018​	$1.2397​	2/19/2019​	$1.1335​
2/20/2018​	$1.2341​	2/20/2019​	$1.1349​
2/21/2018​	$1.2319​	2/21/2019​	$1.1348​
2/22/2018​	$1.2320​	2/22/2019​	$1.1340​
2/23/2018​	$1.2298​	2/23/2019​	$1.1340​
2/24/2018​	$1.2298​	2/24/2019​	$1.1340​
2/25/2018​	$1.2298​	2/25/2019​	$1.1348​
2/26/2018​	$1.2288​	2/26/2019​	$1.1359​
2/27/2018​	$1.2239​	2/27/2019​	$1.1383​
2/28/2018​	$1.2197​	2/28/2019​	$1.1387​
3/1/2018​	$1.2179​	3/1/2019​	$1.1388​
3/2/2018​	$1.2311​	3/2/2019​	$1.1388​
3/3/2018​	$1.2311​	3/3/2019​	$1.1388​
3/4/2018​	$1.2311​	3/4/2019​	$1.1321​
3/5/2018​	$1.2327​	3/5/2019​	$1.1306​
3/6/2018​	$1.2400​	3/6/2019​	$1.1318​
3/7/2018​	$1.2403​	3/7/2019​	$1.1232​
3/8/2018​	$1.2329​	3/8/2019​	$1.1235​
3/9/2018​	$1.2319​	3/9/2019​	$1.1235​
3/10/2018​	$1.2319​	3/10/2019​	$1.1235​
3/11/2018​	$1.2319​	3/11/2019​	$1.1224​
3/12/2018​	$1.2319​	3/12/2019​	$1.1275​
3/13/2018​	$1.2397​	3/13/2019​	$1.1310​
3/14/2018​	$1.2360​	3/14/2019​	$1.1305​
3/15/2018​	$1.2321​	3/15/2019​	$1.1326​
3/16/2018​	$1.2280​	3/16/2019​	$1.1326​
3/17/2018​	$1.2280​	3/17/2019​	$1.1326​
3/18/2018​	$1.2280​	3/18/2019​	$1.1338​
3/19/2018​	$1.2330​	3/19/2019​	$1.1347​
3/20/2018​	$1.2272​	3/20/2019​	$1.1352​
3/21/2018​	$1.2268​	3/21/2019​	$1.1361​
3/22/2018​	$1.2309​	3/22/2019​	$1.1283​
3/23/2018​	$1.2360​	3/23/2019​	$1.1283​
3/24/2018​	$1.2360​	3/24/2019​	$1.1283​
3/25/2018​	$1.2360​	3/25/2019​	$1.1323​
3/26/2018​	$1.2442​	3/26/2019​	$1.1281​
3/27/2018​	$1.2395​	3/27/2019​	$1.1252​
3/28/2018​	$1.2351​	3/28/2019​	$1.1235​
3/29/2018​	$1.2298​	3/29/2019​	$1.1229​
3/30/2018​	$1.2299​	3/30/2019​	$1.1229​
3/31/2018​	$1.2299​	3/31/2019​	$1.1229​
4/1/2018​	$1.2299​	4/1/2019​	$1.1221​
4/2/2018​	$1.2310​	4/2/2019​	$1.1191​
4/3/2018​	$1.2274​	4/3/2019​	$1.1230​

 

[KRice]

There is, but what are your plans for the start date and end date? Does this mean you'd like to specify two dates that are already 90 days apart? Or do you want to specify a much larger range and have a formula split that range into 90 day groups, each of which would be averaged? What happens if the larger date range is not a multiple of 90 days, e.g., 12/15/2021 through 4/30/2022...how are the boundaries of the 90-day range to be determined?

Or are you talking about a walking 90-day average, where for each day, a formula would look back and aggregate the last 90 days and compute an average? I suspect this is not what you want since you mentioned start and end dates.

 

[whynot]

Thank for your response. I want to designate start and end date. The time frame is always 90 days.

 

[portews]

Is all the data in just two columns (all date data in A and all numbers in B), or is it in a separate column pair for each year? Are the dates actually formatted as dates and the currency as currency (without hard coded dollar signs)? Is it always one entry per day with no missing days or duplicate days?

Either way, it might be better to select the start day and have it determine when 90 days ends.

 

[whynot]

Data in just two columns. Date formatted as date and currency as currency. One entry per day. Thank you.

 

[portews]

See how this works for you. Select the start date and run the script, it will run and give you a messagebox with the dates and average. If less than 90 days of data, it will tell you how many days it's averaging.

Sub run_avg()
Dim rng As Range
Dim avg, LastRow, inc, x, y, z As Variant

'https://www.mrexcel.com/board/threads/calculating-90-averages.1206642/

'set how many days to look ahead
inc = 90

'Message box reminding you to select the date column
If Not Selection.Column = 1 Then
    y = MsgBox("Select starting Date column only")
    End
End If

'find the last row
LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

'check if there's "inc" days worth of data
If Selection.Row + inc > LastRow Then
    'include number of days if less than "inc" days
    z = "End of data after " & LastRow - Selection.Row + 1 & " days." & vbCrLf & _
    "The average " & Selection & " to " & Cells(LastRow, 1)
Else
    z = "The average " & Selection & " to " & Cells(Selection.Row + 90, Selection.Column)
End If

'set the date range
Set rng = Range(Selection.Cells, Cells(Selection.Row + inc - 1, Selection.Column))

'get the average of the currency column
avg = FormatCurrency(WorksheetFunction.Average(rng.Offset(0, 1)))

'opens a Message box with the result
x = MsgBox(z & vbCrLf & avg, vbInformation, "Average")

End Sub

 

[whynot]

Will do. Thank you very much.

 

[whynot]

Thank you very much for the help!!!!