(Wrap-up) The gas money splitting tool

This entry is part 7 of 7 in the series The gas mon­ey split­ting tool series

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:

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:

One-way or return journey drop-down list.
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.
Excel AVERAGEIFS() to get the distance between points.
=IFERROR(AVERAGEIFS(RoutesList[Distance (km)];RoutesList[From];From_User_Choice;RoutesList[To];To_User_Choice)*IF(Return_Flag=“Return”;2;1);0)

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:

Passengers data.

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:

Fuel cost - The gas money calculator.

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.

Series Nav­i­ga­tion« =CHOOSE() from a list of val­ues in Excel