Friday, July 19, 2019

Rounding and totals

Minutes and decimals don't always play nicely together, since minutes are a denominator of 60 when converted to decimal.

As a result, someone will occasionally catch what appears to be a math error.

Consider these totals, and the three flights that make it up that a pilot sent me this past week:

At first glance, this looks incorrect, since 1.37+1.70+1.32=4.39, not 4.38.  But it's actually the best math that I can do, given that I want to keep per-minute accuracy.
In this case, both 4.38 hours (=262.8 minutes) and 4.39 hours (=263.4 minutes) both round to 263 minutes, which then converts to 4.383333333 hours, which displays as 4.38 since I round to two decimal places.

So the good news is that the rounding is only ever off by a hundredth of an hour here or there, and it cancels out statistically.  

Actually, I can make a stronger statement: I deliberately maintain to-the-minute accuracy at all stages - including in totals - but I don't promise to maintain sub-minute accuracy.

Here's where it's worth getting into some of the esoterica, but the important thing is that I'm actually optimizing for the math to always work in the hours:minutes space over the decimal space.

In order to do this, I convert the decimal to hours/minutes before adding. So, for example, 1.32 hours is 79.2 minutes.  I round that to 79 (nearest minute) and then divide back by 60 to get the hours input to the totals, which is 1.31666667 hours.

So in the example above,

Flight, As Logged    Flight, rounded to nearest minute
1.37 1.366666667
1.7 1.7
1.32 1.316666667
Total, Logged:   Total, rounded:
4.39 4.383333333


The left column above is raw addition of the values as entered.  The right column has each of those numbers multiplied by 60 (to get minutes), rounded, and then converted back to hours.  That sums up to 4.38333, or 4.38 hours.  Because each of the numbers in the right column represents a precise minute count, the sum represents a precise minute count, so no minutes are lost or added; this is why I can make the claim that all values are accurate to-the-minute.

Doing it this way keeps the math working regardless of whether you use hh:mm (hours:minutes) or decimal hours. 

It's worth noting that if you're using decimal and only use one decimal place (which is 6 minutes), then there's no rounding issue whatsoever, since 6 minutes precisely equals 0.1 hour.  In hh:mm format, though, you need at least two decimal places to capture all 60 possible minutes in an hour.  But conversely, 2 digits actually gives you 100, not just 60, possible slices of an hour, so there's inevitably some rounding that happens somewhere.

Computers and databases only store so many digits of precision - I chose 2 digits because it's enough to accurately handle minutes in hh:mm.   The conversion I do above (multiplying by 60 and then rounding to get whole minutes) restores as many digits of precision as the computer can handle, which will always be enough for any actual logbook to not have any rounding errors.

Some logbooks store all times under-the-covers as minutes and divide by 60 in the display; that works fine (it's exactly the same as doing the right-hand column above without the division by 60), but it too would have rounding issues.  After all, imagine if you entered 1.31 hours (=78.6 minutes which becomes 79 if you're storing minutes in the database).  When reading from the database and converting for decimal display, that would display as 79 / 60 = 1.3166667 = 1.32 hours.

Yet another alternative is to store lots of digits of precision in the database, but that has its own problems: the math works fine, but putting long decimals into fields you can edit can be cumbersome for you, the user, and leads to other problems as well.  E.g., that 1.316666667 number above is too large to fit in many fields on a screen so it would need to get truncated to, say,
1.3167 - and if you then read that field back (for example, when updating a flight) then presto, you've lost the very precision you were trying to preserve.

So it's an interesting anomaly of decimal display that sometimes the hundredth's-place digit in totals appears to be off.  But yes: since I can promise that all numbers - both at the individual flight level and in totals - are accurate to the minute, then I can also promise that any accumulated rounding error - using to-the-minute flights - is also less than one-sixtieth of an hour.  And since .02 is more than one-sixtieth, and I only have two decimal places, any such rounding discrepancy (I shouldn't even call it "error") should thus actually be limited to 0.01.  And in hh:mm display, there will be no such rounding discrepancy.

UPDATE AUG 2023: I've just taken out an option to let you explicitly use hundredths-of-an-hour.  Same rules apply: all numbers are rounded to the nearest 100th of an hour before being added.  

Note that if you choose this options, then the "error" will go the other way.  

For example, imagine you have 3 40-minute flights.  That's 2/3 of an hour.  You can enter them as 0.67 each, or 0:40 in hh:mm format; it doesn't matter - they're stored as 0.67 under the covers.  

Using per-minute math, these get converted to ROUND(0.67 x 60) / 60 = ROUND(40.2) / 60 = 40 / 60 = 0.6666666666667.  Add these up and you get 2.00 - which makes sense, since 3 x 40 minutes = 120 minutes.

Now switch to hundredths of an hour you look at your totals.  ROUND(0.67 * 100) /100 is just ... 0.67.  And 0.67 + 0.67 + 0.67 is 2.01.  So that will display as 2.01 using decimal, or (since 0.01 is 36 seconds) as 2:01 in hh:mm.

It's all a function of where you want the rounding to occur.

I still default to per-minute math because I think it makes more sense - after all, if you look at your watch or your phone to see "what time is it", you're typically not looking at the seconds, so you're truncating to the nearest minute anyhow.  I.e., your 40 minute flight was probably not 0:40:00...

But now you can change it.