This is to put the final touches of the Gas money splitting tool. The overengineered calculator can be enhanced further but it already served the purpose to demonstrate some Excel concepts. Experienced spreadsheet user are not going to find something new in this post. However, if you are one such, you can check the advanced techniques category.
What was achieved so far
While building the calculator the following concepts were used:
- I showed how to extract unique (short) list of entries so you can pick-up an option easy without having to deal with everything in the long list.
- Then, we saw how to construct a named range on the fly so it fits perfectly the size of the unique items as they grow or decrease in number.
- The infamous INDEX/MATCH allowed us to get the technical specifications of the vehicles.
- Then, by employing conditional =LOOKUP() I demonstrated how to get the latest fuel pricing from a table (and skip any blanks in the process). Further, the =CHOOSE() function was used to pick the figures for the right fuel.
Putting final touches
The following actions are meant to close the project so we can arrive at a state of a working tool.
One-way or return trip
If you go places, chances are that you would like to come back. However, you might have driven your companions to the sea and then split. With simple drop-down we can list the options:
Getting the distance between start and end points
On the Routes sheet we put the distances for the trips. However, due to short memory or pure laziness we can enter the same route more than once. In theory this can be mitigated by using proper checks and signalling with conditional formatting. Errors can still occur. In the following situation one route is present 2 times:
There are many ways to get the 110,21 km figure from that table. Some of them (for example using =SUMIFS()) will fail you in case of duplicated route. Others, like using INDEX/MATCH with multiple criteria, will work but are a bit complicated. Moreover, if the input distances were not the same — most methods would end up with one of the figures (which might be just enough for you).
In situations like this one I often use =AVERAGEIFS():
- It is standard function;
- Has simple syntax;
- And will average if different values are present so on average (!) I should be right in my estimate.
In the =AVERAGEIFS():
- (1) — We pass the column with distances as the first argument.
- (2) and (3) — The columns with start/end points are paired with the user-selected values to drive the conditions.
The rest of the formula is:
- (A) — An error handler that will return 0 if in the routes table there is no match of both the origination and destination. In that case, =AVERAGEIFS() will return #DIV/0.
- (B) — Distance multiplication in case of return trip.
We need data about the passengers on the trip:
The only thing to note here is that both fields are limited in terms of inputs by using the data validation feature in Excel.
At long last we get the bill:
It is simple math. The only thing that is more fancy is the second row name — it will change dynamically to incorporate the figure input in the previous step.
So there you have it — the calculator is ready. Get it here:
Was this all necessary? Absolutely not. However, a lot of us are guilty of turning to Excel for the simplest of tasks. We could keep building features and make optimizations but at one point it all has to end. Otherwise, we would not have time for other exciting projects or ours.
I built such file as promised to friends of mine. Turns out, now that I have it, it revert to it from time to time. Having something ready is another tool in the box. And having one is good.