Average Euro to Dollar Exchange Rate over date range

[Marc Enzi]

I have to sort out average exchange rates Euro to Dollar between any given set of dates.

I have my data set up as follows
Tab.1.Rates.......
ColumnA.ColumnC
Date.......Price....
1/1/2009 $1.4043
1/2/2009 $1.3978
1/3/2009 $1.3925
1/4/2009 $1.3925
1/5/2009 $1.3923
1/6/2009 $1.3756
1/7/2009 $1.3497
1/8/2009 $1.3579
1/9/2009 $1.3642


I have another tab where I need to sort out the exhange rate for the duration of the trip.
Column K is the one I am solving for. I have the start and the end dates, and I want to call on the Tab1 Rates data for the entire year to calculate the average exchange rate for the duration of the trip.

.Tab2.Travel History
ColumnI..ColumnJ..ColumnK
Start.....End........AvgExchg
1/2/09...1/7/09....$1.3834

Here is what I did have, but it seems to only return the value of the exchage rate on the first day in the lookup range and I can't figure this out.

AVERAGE(LOOKUP(ROW(INDIRECT(I3&":"&J3)),Rates!A$3:A$367,1+Rates!C$3:C$367))-1)

Thanks so much.

-Marc

P.S. I don't understand macros or VBA, but do understand array formulaes (sort of)

 

[barry houdini]

Why do you need +1 and -1? That formula should work but it's an "array formula" which you need to confirm with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar.

Alternatively this should work

=AVERAGE(IF(Rates!A$3:A$367>=I3,IF(Rates!A$3:A$367<=J3,Rates!C$3:C$367)))

also confirmed with CTRL+SHIFT+ENTER

 

[Marc Enzi]

Thank you Mr. Houdini,

Your alternative worked perfectly.

I am very grateful.

-Marc