One of my favourite computer-type activities is to automate stuff. I’ll very happily spend hours crafting something that will shave seconds off a task. It’s the thrill of mastery, I think. Being able to do something just because I can. I guess it’s what ultimately drives most learning.
I’ve often written before on apps that helps me automate tasks: TextExpander types things for me, Keyboard Maestro handles repetitive tasks, Hazel keeps my computer tidy, Default Folder helps me get to the right folder easily in Open and Save dialog boxes.
My latest challenge though was a spreadsheet. I use Numbers for spreadsheet tasks.
I’ve recently taken on some work that involves me driving to a particular set of local properties and doing some tasks there, such as letting in a tradesperson or doing a bit of cleaning.
What I’m finding though is that there’s more travel than I expected and the financial rewards are less than I expected. I’m just not sure it makes economic sense for me to do this work.
I decided to set up a spreadsheet to track my travel distance and time, activities and recompense. What I’m paid has a complex structure involving commissions, fixed rates, some travel recompense and bonus amounts that depend on factors beyond my control. I also have to reckon in the overhead of daily checking schedules, mail, tickets and a forum.
I started with two sheets: one lists the properties, the one-way and return distances, and one-way and return travel times (according to Google Maps).
Note: my spreadsheets have been replicated with fake names, addresses and other data for privacy.
This sheet uses simple formulas to calculate return time and distance from the one-way numbers. Note the column called
Short name. The purpose of this column is to make things easier for the sheet I’m about to write about.
The next sheet logs travel and reckons the cost in two ways: one is simply how much the petrol costs (not shown), while the other reckons the amount if I claim $0.73 cents per kilometre for use of my vehicle. Of course, there are more costs when running a car than just petrol. The cents per Km allows for wear and tear, insurance, replacement and so on. It mounts up quickly
This second sheet does two interesting things:
- It uses a pop-up field to make it easy (and consistent) for me to enter the name of the destination, because
- I later use this column to look up the distance travelled and enter it automatically.
When I want to reckon the cost of travel to a particular destination I would need to switch to my reference sheet and look up the distance then enter it. But why should I do that when the computer can do it for me?
So, here’s how the Lookup function works: in this second sheet I have a formula in the
Km Return column that goes to the sheet where the Destination information is stored and finds the information in a specific column, according to what is in a particular row. Here’s how that formula looks:
The Lookup formula is structured like this:
In English, my formula says: notice what is in Column C on this table, then go to the Sheet named
Destinations (the search-for place) and look in Column B, rows 2 to 9 for the thing that matches. Then bring back the information you find in the corresponding row in column D.
It works out like this: I’m in Row 2 of my Travel Events table. In Column C of Row 2 is the word
Kitten. Now go to the Sheet called Destinations and look in Column B for
Kitten. If you find it then look across whatever row you found it in till you get to Column D. In this case it finds
53.8. and brings that back to enter into the cell where the formula appears.
After verifying for myself that the formula has worked correctly I then ‘filled’ the formula down to the other rows on my sheet.
After creating these two sheets I went on to make a third that is event-based and calculates the various payments and bonuses etc. That sheet again uses the Lookup function to fill certain values automatically from a reference table. One of them even uses a nested IF function like this: if Column 2 of this row contains the word
in then assign a time value of 0.33, if instead it contains the word
out then assign a time value of 0.33, otherwise assign a time value of
Here’s how that formula looks:
Another cool function I’ve used is one to derive the
out used in the above formula. You see, in Column B I have a 6-digit code number (usually) followed by a space and then a few letters.
But wait! That’s in Column B, so why does my IF function check against Column D? Well, Column D is hidden in my spreadsheet and serves a single purpose: it takes the 6 digits followed by a space in Column B and removes them, leaving only the remaining letters. To do that I used this formula in Row 2:
In English, this says: In cell B2, starting with the first character, replace 7 characters with nothing (the value after the final comma).
I had enormous fun devising this spreadsheet and coming to grips with the formulas and functions I’ve explained here. Lookup and Replace were new to me, while nesting an IF was an interesting challenge.
I had some help to achieve these things, most notably a 5 minute video published in 2012: Using the Lookup Function In Numbers (MacMost Now 762). It wasn’t the first video I watched for help, but it was definitely the clearest and most helpful.