and things went downhill from there

One of my co-workers called on Friday afternoon. Partly he was just looking to chat, but he also needed to vent a bit after a trying conversation with our senior director.

She had called him because she had received a complaint from one of our executives over a couple of new line items we recently added to our executive dashboard. The line items in question were part of our cost metrics, giving a breakdown of cost-per-car for yard switching. The formula for this is very straight-forward. All you need to know is the total expenditure in wages that we paid to the crews (their hours times their wages plus any overtime hours and wages), and the number of cars that were handled in by switchers in the yard that day. If you divide the total payroll by the number of cars handled, it will give you an average cost per car.

Obviously this is only approximate, since it does not include the cost of fuel and maintenance, but it is a reasonable metric they can pull out during negotiations with customers to justify charging them a premium when they request custom switches.

"We want the fourth car in from the west end of that track."
"Right. So that would involve handling four cars, including yours, for a total charge of $160."

What this executive was complaining about, and what Fearless Leader was having some trouble wrapping her head around was this:

Cost Per Car (CPC) = Total Wages (TW) / All Cars Handled (ACH). One of our illustrious executives reasoned that if CPC = TW/ACH, then TW*ACH should = CPC. That is actually a reasonable assumption to make, but when he multiplied them together, they came up short. He immediately got on the phone and started flapping his meathole at our senior director about how the numbers were wrong. She, in turn, got on the phone to my co-worker and demanded to know what he could do to fix the numbers.

If our leaders and directors are very good at one thing, it is accepting blame on someone else's behalf. There is some degree of reticence on the part of our middle managers when it comes to talking to people above them in the company. Part of that comes from self-serving spinelessness, but there is also a culture that is oozing down from the top where they are more interested in results than they are in explanations. When one of her masters told her to fix the dashboard, she bent over backward to deliver results, not explanations.

Thus, even though my co-worker spent the next twenty minutes trying to explain the concept of rounding errors to her, she tuned out everything that did not sound like "I'll fix it." He explained that the unit cost number was being truncated to two decimal places at the request of the same executives who were now questioning its accuracy. They asked for it to be truncated because they (reasonably) did not want their dashboard cluttered up with numbers stretching off to five or six decimal places.

She did not get it. Clearly he was just making excuses, rather than changing the fundamental laws of arithmetic.

He walked her slowly through it. If the total cost was, say, $34,126.54 (these numbers are all made up), and the total number of cars handled on that day day was 913, then the cost per car was $37.3784665 (and a few more digits for good measure). Since the executive asked us to truncate it (not round) to two digits, we showed it as $37.37 on the dashboard. Thus, if somebody then multiplied $37.37 * 913, they would only get $34,118.81 and come up short of the actual wage figure. As long as we were truncating to two decimal places, it would always come up short.

She still did not get it.

Obviously the $37.37 figure must be wrong or else when it was multiplied against the total units, it would yield the total cost. He explained it again, showing her the underlying figures, and explaining how the moment we cut it off at two decimal places - whether through rounding or truncation - we were stripping critical data from the number, and unless we happened to luck into figures that divided out evenly, the maths would never work out evenly if the terms were reversed. Given the size of the numbers involved, we would need to publish about six decimal places in order for the equation to work in reverse.

He was not convinced that she entirely understood - or was even willing to understand - but she eventually stopped pressing him for further explanation. My suspicion is that she explained it to the executive as a software limitation. That's a good enough IT answer, I suppose.

When does -40 = -41? When you copy a formula over from version 3.1 of some software to version 4.0 of the same.

I not write the formulas that created those numbers, and if I had, I'd have written them differently anyway. The fact remains that the software that we are using to produce this report has changed the way it handles its rounding from one version to the next.

The formula is taking a performance metric from one year and subtracting it from the previous year to show the variance year over year. When I expanded the two base numbers, this is what I got:

This year: 14.450
Last year: 54.941

When you subtract last year from this year, you get -40.491

The current version of the software correctly rounds that down to 40 when we strip away the decimal places. But wait, when we round all three numbers we get 14 - 55 = -40. In the old version it yielded -41.

What this tells me is that the old version of the software was probably rounding the numbers before it did the subtraction, rather than doing it at the end. In their own, stupid ways, both versions of the software are correct.

And I am left with a report that yields different results from the same source data, depending on which program I use to produce it.

It will be labour intensive, but the only solution I can see at the moment is to redo the formulae and truncate each term, rather than truncating at the end. That should only take most of tomorrow.