do a calculation if a date range matches

Originally Posted by kistev

hello

I am new at this and have searched the forum but unfortunately not found anything that will help

I have a spreadsheet where all our stock purchases are recorded. The purchases are in Euro and have to be converted to Sterling at the rate valid for the month when the payment was authorised.

See the attached spreadsheet :testform.xls - zipped

I want to be able to do the calculation automatically:

for example

Order x1234 was approved for payment in July.

That is check the date in column N falls in July and if it does multiply the value in column H by the relevant exchange rate in column X (July = 1,42 and place the resulting value in column I.

Using IF statements 7 is the limit, so I am stuck as I assume there are 2 ranges that need to be checked.

Any help would be greatly appreciated

Thanks

Hi,

the easiest way for this is a table of Date - Rates

whilst the table could be anywhere, a separate sheet is recommended, Rates, in column A the date the rate starts, in column B the rate from that date.

For the cell where you want the rate to appear, and assuming the 'date' is in column N

=VLookup(N2,Rates!A:B,2,True)

will set the rate.

Make sure the sheet Rates!A dates are in correct date sequence.

hth
---

Originally Posted by kistev

Hi

Thanks for the help, it worked first time. I had to put it in an IF statement to do the calculation I wanted.

I had read in Excel help that your solution was the way to go, I just couldn't get it to work.

thanks again

good to see, and thanks for the response.
---