(Wrap-up) The gas money splitting tool

This is to put the final touch­es of the Gas mon­ey split­ting tool. The overengi­neered cal­cu­la­tor can be enhanced fur­ther but it already served the pur­pose to demon­strate some Excel con­cepts. Expe­ri­enced spread­sheet user are not going to find some­thing new in this post. How­ev­er, if you are one such, you can check the advanced tech­niques category.

What was achieved so far

While build­ing the cal­cu­la­tor the fol­low­ing con­cepts were used:

• I showed how to extract unique (short) list of entries so you can pick-up an option easy with­out hav­ing to deal with every­thing in the long list.
• Then, we saw how to con­struct a named range on the fly so it fits per­fect­ly the size of the unique items as they grow or decrease in number.
• The infa­mous INDEX/MATCH allowed us to get the tech­ni­cal spec­i­fi­ca­tions of the vehicles.
• Then, by employ­ing con­di­tion­al =LOOKUP() I demon­strat­ed how to get the lat­est fuel pric­ing from a table (and skip any blanks in the process). Fur­ther, the =CHOOSE() func­tion was used to pick the fig­ures for the right fuel.

Putting final touches

The fol­low­ing actions are meant to close the project so we can arrive at a state of a work­ing tool.

One-way or return trip

If you go places, chances are that you would like to come back. How­ev­er, you might have dri­ven your com­pan­ions to the sea and then split. With sim­ple drop-down we can list the options:

Getting the distance between start and end points

On the Routes sheet we put the dis­tances for the trips. How­ev­er, due to short mem­o­ry or pure lazi­ness we can enter the same route more than once. In the­o­ry this can be mit­i­gat­ed by using prop­er checks and sig­nalling with con­di­tion­al for­mat­ting. Errors can still occur. In the fol­low­ing sit­u­a­tion one route is present 2 times:

There are many ways to get the 110,21 km fig­ure from that table. Some of them (for exam­ple using =SUMIFS()) will fail you in case of dupli­cat­ed route. Oth­ers, like using INDEX/MATCH with mul­ti­ple cri­te­ria, will work but are a bit com­pli­cat­ed. More­over, if the input dis­tances were not the same — most meth­ods would end up with one of the fig­ures (which might be just enough for you).

In sit­u­a­tions like this one I often use =AVERAGEIFS():

• It is stan­dard function;
• Has sim­ple syntax;
• And will aver­age if dif­fer­ent val­ues are present so on aver­age (!) I should be right in my estimate.

In the =AVERAGEIFS():

• (1) — We pass the col­umn with dis­tances as the first argument.
• (2) and (3) — The columns with start/end points are paired with the user-select­ed val­ues to dri­ve the conditions.

The rest of the for­mu­la is:

• (A) — An error han­dler that will return 0 if in the routes table there is no match of both the orig­i­na­tion and des­ti­na­tion. In that case, =AVERAGEIFS() will return #DIV/0.
• (B) — Dis­tance mul­ti­pli­ca­tion in case of return trip.

Passengers

We need data about the pas­sen­gers on the trip:

The only thing to note here is that both fields are lim­it­ed in terms of inputs by using the data val­i­da­tion fea­ture in Excel.

Fuel cost

At long last we get the bill:

It is sim­ple math. The only thing that is more fan­cy is the sec­ond row name — it will change dynam­i­cal­ly to incor­po­rate the fig­ure input in the pre­vi­ous step.

Ta-Dah

So there you have it — the cal­cu­la­tor is ready. Get it here:

Final thoughts

Was this all nec­es­sary? Absolute­ly not. How­ev­er, a lot of us are guilty of turn­ing to Excel for the sim­plest of tasks. We could keep build­ing fea­tures and make opti­miza­tions but at one point it all has to end. Oth­er­wise, we would not have time for oth­er excit­ing 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. Hav­ing some­thing ready is anoth­er tool in the box. And hav­ing one is good.