I recently spent some time traveling around Europe. I developed a spreadsheet in AppleWorks GS that allowed me to enter currency amounts required or spent in different countries, that gives me a total in dollar value, and also summarizes specific amounts of each currency required. This greatly eased planning for how much of each currency to buy, and how much my bank account would be depleted.
I first built an exchange rate table found in the range P2 to S9. In the first column, list the abbreviation for each currency. In the second, I wrote a description, nothing that is required for the spreadsheet. In the third, the amount of the currency equal to one dollar (at the time).
$ U.S Currency 1.00
AS Austria Schilling 12.00
BF Belgian Franc 35.00
DM German Mark 1.64
FF French Franc 5.80
HFL Dutch Gilder 1.90
SFR Swiss Franc 1.48
ú British Pound 0.58
Selecting the entire matrix, and using Data Sort ascending, rows by column, ensured that the first column was sorted properly for a lookup function.
I then made the front section of the spreadsheet my input section, range A1 to N52. I used the first column to list activities, the third column to list my name, the fifth to list my Wife╒s name, the seventh to list my first child, and so on. I included my parents also. Now, under tour, you would list the tour name, and under that any meals and extra costs, like souvenirs. The second column is reserved to indicate what kind of currency will be used on that line. In the columns under each name, I list the actual or estimated cost of each event in the defined currency. In the fourth column, I used the following equation:
B5 is where the currency type is entered, and C5 is the amount. If either cell is empty, the rest of the equation is ignored and no entry is made in this column. Once the type and amount are entered, it takes the amount in C5 and divides it by the exchange rate for the type indicated. The Vlookup function matches the type in the table starting in cell P2, and knows the table goes down to P11. When it finds the match, it reads two columns over to find the exchange rate.
The $ sign in front of the B and P entries allow me to copy this equation across into all even columns without the references to type and the exchange matrix being adjusted. The $ sign in front of the 2 and 11 allow me to copy the equation down through the column without adjusting the location of the matrix.
The spreadsheet now looks like this example;
Tours or Drive Jack Joan
Rhine River $ 49.00 $49.00 49.00 $49.00
Lunch DM 15.00 $9.15 15.00 $9.15
Dinner DM 15.00 $9.15 15.00 $9.15
Souvenirs DM 50.00 $30.49
I used the following to accumulate totals, divided according to my family or my parents;
=Sum(D5..D50) + Sum(F5..F50)
This sums all dollar amounts under Jack and Joan, providing an assessment of total cost.
Now, all that╒s left is to summarize the amount of each type of currency. I did this by first sorting amounts by line to the right of the spreadsheet, then adding totals at the bottom. I like to move over to column AA to start, where users are unlikely to go. On a line even with column titles, I enter the type, so in AA4 I enter $, in AB4 I enter AS, in AC4 I enter BF, and so on until the ú is entered. To divide between two families, I repeated this further to the right again. Under the $ sign for Jack and Joan, I used this equation;
=If($B5=AA$4,$C5+$E5,╥╙)
This checks to see if cell B5 equals AA4, and if so, enters the total amount in C5 and E5. Remember, the odd columns are where we are entering the actual currency used. By using the $ sign in front of B, C, and E, I can copy this equation across under the other type symbols without losing the correct cell references, and the $ infront of 4 allows me to copy down without losing the correct reference to line 4, where the currency type for that column is entered.
Last, at the bottom of the spreadsheet in the range A54 to G62 I enter each type of currency, and use the following to sum, as an example, the amount of Austrian Schillings required.
=Sum(AB5..AB50)
where AS was totaled for Jack in column AB, starting in line 5 and going through line 50. The result looks like this.
FOREIGN CURRENCY JACK
Austria Schilling 440.00
Belgian Franc 2200.00
German Mark 320.00
French Franc 430.00
Dutch Gilder 140.00
Swiss Franc 115.00
British Pound 66.00
I now have a spreadsheet that allows me to enter tour cost, meals, souvenirs, etc., in any currency, and quickly have a summary of how much it will cost in dollars, as well as how much of each currency I should buy at the bank.
