I am sure this is a simple one, but I am trying to add a calculation to a cell to compensate for that days exchange rate. This is for an travel expense report spreadsheet given to me by my company, so they already had formulas in place. Basically, I need to divide the total of all cells listed by the exchange rate, but I am unable to make it work.
For this instance:
H17 is the foreign currency amount P17 is the "Total" cell that I am trying to modify Q17 is the exchange rate cell
This is the pre-existing formula in the "Total" cell:
=IF(SUM(G17,H17,J17:N17)=0,0,SUM(G17,H17,J17:N17)-O17)
Thanks in advance!
Here is a sample of the table I am working with to hopefully help visualize things better:
| -- | -------- | ----------------- | --------------------- | ------------- | ----- | ----------------- | ----- | ----------------- | ----- | ----- | ----- | ----- | ------------- | ----- | --------- | --------------------- |
| -- | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R |
| -- | -------- | ----------------- | --------------------- | ------------- | ----- | ----------------- | ----- | ----------------- | ----- | ----- | ----- | ----- | ------------- | ----- | --------- | --------------------- |
| 14 | Date | Contract Number | Primary destination | Description | CC | Airfare / Train | Hotel | Mileage | Fuel | Meals | Taxi | Misc | Unallowable | TOTAL | X-Rate | Paid by Company Card |
| | | | | | | | | (Miles) (Amt) | | | | | | | | |
| -- | -------- | ----------------- | --------------------- | ------------- | ----- | ----------------- | ----- | ----------------- | ----- | ----- | ----- | ----- | ------------- | ----- | --------- | --------------------- |
| 15 | 09/11/24 | 1234567890123456 | Some Country | Uber | N | | | | | | | 36.15 | | | 36.15 | 0 | |
| -- | -------- | ----------------- | --------------------- | ------------- | ----- | ----------------- | ----- | ----------------- | ----- | ----- | ----- | ----- | ------------- | ----- | --------- | --------------------- |
| 16 | 09/11/24 | 1234567890123456 | Some Country | Flight | N | 1,000 | | | | | | | | | 1,000 | 0 | |
| -- | -------- | ----------------- | --------------------- | ------------- | ----- | ----------------- | ----- | ----------------- | ----- | ----- | ----- | ----- | ------------- | ----- | --------- | --------------------- |
| 17 | 09/11/24 | 1234567890123456 | Some Country | Hotel | N | | 900 | | | | | | | | 300 | 3.00 | |
| -- | -------- | ----------------- | --------------------- | ------------- | ----- | ----------------- | ----- | ----------------- | ----- | ----- | ----- | ----- | ------------- | ----- | --------- | --------------------- |
Just add the division to the end. You'll need to wrap the entire SUM operation and the subtraction in parentheses, because the division won't be done in the order you want without them - the division would be done before the subtraction because of operator precedence.
=IF(SUM(G17,H17,J17:N17)=0,0,(SUM(G17,H17,J17:N17)-O17)/Q17)