Calculate a dynamic average from a table in excel

Question

I have this table of currency rates:

I would like to create a formula that receives the begin date and end date + currency, and in return will calculate the average for every month inside the range.

For example, if the currency is GBP, begin date is 15/09/2015 and end date is 20/11/2015, I would like to calculate averages for 30/09/2015 + 31/10/2015 + 30/11/2015.

I tried to solve it with a simple Average + Vlookup + Match functions, but it wouldn't get me the range of cells but only the edges of the range. I also thought of using AverageIF function, but I'm not sure how to combine between the criterias of date and currency.

Can you think of an easy way to solve that?

Are you matching on currency, month and year basically ?

Tom Sharpe
– Tom Sharpe

2016-03-27 09:03:14 +00:00
Commented Mar 27, 2016 at 9:03 — Tom Sharpe
– Tom Sharpe, Commented Mar 27, 2016 at 9:03

Tom Sharpe · Accepted Answer · 2016-03-27 09:39:16Z

1

I suggest using an index/match to get a reference to the beginning of the range of currencies then another one to get the end of the range and combining them like this:-

=AVERAGE(INDEX($B$2:$H$10,MATCH(D13,A$2:A$10,0),MATCH(1,(MONTH(E13)=MONTH($B$1:$H$1))*(YEAR(E13)=YEAR($B$1:$H$1)),0)):
INDEX($B$2:$H$10,MATCH(D13,A$2:A$10,0),MATCH(1,(MONTH(F13)=MONTH($B$1:$H$1))*(YEAR(F13)=YEAR($B$1:$H$1)),0))
)

Where the currency name is in D13, start date in E13 and end date in F13, and the table is in A1:H10.

Result: 1.5203

Must be entered as an array formula using CtrlShiftEnter

answered Mar 27, 2016 at 9:39

Tom Sharpe

35.1k5 gold badges29 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lev · Accepted Answer · 2016-03-27 10:12:28Z

0

Found a way to solve this, by using the AVERAGEIF function. Enjoy, and thanks for helping!

answered Mar 27, 2016 at 10:12

Lev

1131 gold badge4 silver badges13 bronze badges

1 Comment

Tom Sharpe Over a year ago

Yes good but I think you need to put 6/8/2015 in as a date(2015,8,6) like you did with date(2016,1,31).

Collectives™ on Stack Overflow

Calculate a dynamic average from a table in excel

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related