MS Excel 2003: Sum each month's data applying an exchange rate
Question: In Microsoft Excel 2003/XP/2000/97, I would like to create a conditional formula. I've entered in my Excel spreadsheet, the interests received on investments every month under the appropriate column (the months are the titles of the headrow) and I would like to have under my "total column", the sum of the year's interests in US dollars. I entered the interest in Canadian dollars and I want it to appear like that in the cells, but I want the total in US dollars using the appropriate exchange rate of each month and not an average.
| January | February | March | Total | Exchange rates : |
|---|---|---|---|---|
| 50$ cnd | 75$ cnd | 33$ cnd | ?sum $us | Jan. 1.37 |
| Feb. 1.40 | ||||
| March 1.38 |
How can I do a formula like that?
Total should be SUM (50$ x 1.37) + (75$ x 1.40) + (33$ x 1.38). This is my actual formula, but I need to improve it and not having to add the exchange rate every month.
Answer: We've included an example spreadsheet for you to download that will demonstrate how to apply the formulas.
Download Excel spreadsheet (as demonstrated below)
Based on our spreadsheet, we've entered each month's data into columns A to L. Next, we've entered the exchange rates for each month in column P. Then in the Total column, we've entered the following formula:
=(A4*$P$4) + (B4*$P$5) + (C4*$P$6) + (D4*$P$7) + (E4*$P$8) + (F4*$P$9) + (G4*$P$10) + (H4*$P$11) + (I4*$P$12) + (J4*$P$13) + (K4*$P$14) + (L4*$P$15)
This formula applies the exchange to each month. The $ symbol is used in the formula to indicate an absolute reference to the exchange rate. That way, the formula can be copied to other cells and still reference the correct exchange rates.
Learn more about relative vs. absolute referencing.
Advertisements