javascript - Google Sheets : How to find and calculate the value in data table where date is between two dates

Question

I'm trying to make my account expense where I order the product from oversea that has different exchange rate.

I have two separate spread sheets 1.Currency EXC which I use to add my exchange rate and amount data.

Date        Amount      EXC Rate
07/01/2021  ? 200,000   0.0276
12/01/2021  ? 400,000   0.0275
15/01/2021  ? 103,600   0.0274
16/01/2021  ? 200,000   0.0274
22/01/2021  ? 100,000   0.0273

2.Purchase Order Which I use to record the item I bought.

Date        Product Item     Price     QTY
03/01/2021  item1            ? 14,900   10
12/01/2021  item2            ? 14,900   5
15/01/2021  item3            ? 33,000   3

I want to know the price I bought in my local currency according to the date I did money exchange. For example : I bought item 1 on January 3rd which I exchange the money at 0.0276 per ? 200,000. That means I bought item 1 at 411.24 x 10 = 4112.40. The total money in my pocket = 200,000 - 149,000 = 51,000 then I bought another item on January 12th but I still have the money left 51,000. The total expense for item2 were 74,500 that mean 51,000 x 0.0276 = 1407.60 and 23,500 x 0.0275 = 646.25

I want to auto calculate these data in another google spread sheets but I don't know how to make formula for this.

Thank you.

question from:https://stackoverflow.com/questions/65943145/google-sheets-how-to-find-and-calculate-the-value-in-data-table-where-date-is

Answer 1

Waitting for answers