I have created a budget file with multiple sheets, each sheet has rows by category and columns for each month.
The expenses I wish to track can be in one of the following currencies: $, , ₪, I would like to enter the cost is the currency I have paid at, let's say so could always know the original currency. now when I sum the columns I wish to multiply each relevant amount with its rate so the sum of the column will be in $ (the currency I manage my budget). I have a table of rates on the last sheet.
I have looked and looked and couldn't find the right formula to give me the desired result.
here is the table:
<colgroup><col><col><col span="5"></colgroup><tbody>
</tbody>
Total (line 19) formula is =SUM(B7:B12,B14:B16,B18:B21)
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
So the sum i want to calulate:
B7 identifies it is in euro and multiply by the right rate on E2 (another sheet)+B8 identifies it is in shekel and multiply by the right rate on E3+B9 identifies it is Doller so no need to do anything.
I'm using excel 2016
I hope it is clear and that someone here can help me,
Thanks in advance
The expenses I wish to track can be in one of the following currencies: $, , ₪, I would like to enter the cost is the currency I have paid at, let's say so could always know the original currency. now when I sum the columns I wish to multiply each relevant amount with its rate so the sum of the column will be in $ (the currency I manage my budget). I have a table of rates on the last sheet.
I have looked and looked and couldn't find the right formula to give me the desired result.
here is the table:
A | B | C | D | ||||
4 | Jan-18 | Feb-18 | Mar-18 | ||||
5 | Budget | Actual | Budget | Actual | Budget | Actual | |
6 | SOFTWARE | ||||||
7 | Design/photo editing (e.g. Photoshop, Illustrator, InDesign) | 10.00 | - | 0.00 | 0.00 | 0.00 | 0.00 |
8 | Adobe license (Creative directore) | ₪ 70.00 | ₪ 70.00 | ₪ 70.00 | ₪ 70.00 | ₪ 70.00 | ₪ 70.00 |
9 | Animation (e.g. After Effects) | $ 10.00 | $ - | 0.00 | 0.00 | 0.00 | 0.00 |
10 | Wireframing (e.g. Balsamiq) | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
11 | Prototyping (e.g. InVision) | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
12 | Project management (e.g. Basecamp) | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
13 | HARDWARE | ||||||
14 | Graphics-optimized computer (e.g. MacBook Pro) | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
15 | HD display | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
16 | SD cards/external hard drives | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
17 | EQUIPMENT RENTALS / PURCHASES | ||||||
18 | Camera | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
19 | Tripod | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
20 | Microphone | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
21 | Lighting | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 | 0.00 |
22 | TOTAL | $ 90.00 | $ 70.00 | $ 70.00 | $ 70.00 | $ 70.00 | $ 70.00 |
<colgroup><col><col><col span="5"></colgroup><tbody>
</tbody>
Total (line 19) formula is =SUM(B7:B12,B14:B16,B18:B21)
A | B | C | D | E |
1 | From | to | date | Exchange rate |
2 | EUR | USD | 29/07/2018 | 1.17 |
3 | ILS | USD | 29/07/2018 | 0.273 |
<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>
So the sum i want to calulate:
B7 identifies it is in euro and multiply by the right rate on E2 (another sheet)+B8 identifies it is in shekel and multiply by the right rate on E3+B9 identifies it is Doller so no need to do anything.
I'm using excel 2016
I hope it is clear and that someone here can help me,
Thanks in advance