User found the perfect formula to make Excel misbehave
- Reference: 1777618811
- News link: https://www.theregister.co.uk/2026/05/01/on_call/
- Source link:
This week, meet a reader we'll Regomize as "Albert" who told us about his time working for a substantial French consulting company's outpost in the north of England.
"I was a software engineer working supporting various Oracle ERP integrations," Albert told On Call, before explaining that one integration saw data from Big Red's Payroll app piped into an Excel spreadsheet.
[1]
The integration had worked for more than a year when an urgent support ticket landed in Albert's queue: The sheet's calculation of billable hours worked by employees had suddenly and mysteriously started producing inaccurate results that were off by around a third – a ratio he felt was somehow important.
[2]
[3]
Albert opened the file provided by the user and quickly confirmed the sums were wrong.
He decided the integration had broken somehow, and so he started to investigate.
[4]To fix this Wi-Fi network, we'll need a crane
[5]Support tech caught by 'Technician Aura': the bug that only hides when you're watching
[6]Tech support chap's boss got him out of jail so he could finish a job
[7]Contractor quaffed his way through Y2K compliance while the client scowled
"After spending many hours digging through long PL/SQL functions on the database I could see nothing wrong with the logic," he told On Call. "Even more confusingly, the files I generated myself showed the correct times."
His next step was a chat with the chap who filed the ticket, which was when he learned the user wasn't using the standard spreadsheet.
[8]
"Our files were output to their specifications, which called for billable time to be expressed in minutes," Albert told On Call. "The user had divided the number by 100 as it looked 'too large' and they wanted the number in hours."
It turned out that the user had therefore manually edited the spreadsheet to produce the number they wanted.
"I had to explain to the user that to get billable time in hours you must divide by 60 not 100," Albert recalled. And that's why the numbers were out by about a third!
[9]
Have you had to teach your users basic mathematics? If so, why not add your story to the On Call mailbag by [10]clicking here to send us an email. We promise to handle your story carefully by Regomizing your identity so it won't subtract from your career prospects, but will multiply the fun your fellow readers experience when reading a future edition of On Call. ®
Get our [11]Tech Resources
[1] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/applications&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=2&c=2afR5wzpV0i7zotHdK-7kHgAAA5U&t=ct%3Dns%26unitnum%3D2%26raptor%3Dcondor%26pos%3Dtop%26test%3D0
[2] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/applications&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=4&c=44afR5wzpV0i7zotHdK-7kHgAAA5U&t=ct%3Dns%26unitnum%3D4%26raptor%3Dfalcon%26pos%3Dmid%26test%3D0
[3] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/applications&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=3&c=33afR5wzpV0i7zotHdK-7kHgAAA5U&t=ct%3Dns%26unitnum%3D3%26raptor%3Deagle%26pos%3Dmid%26test%3D0
[4] https://www.theregister.com/2026/04/24/on_call/
[5] https://www.theregister.com/2026/04/17/on_call/
[6] https://www.theregister.com/2026/04/10/on_call/
[7] https://www.theregister.com/2026/04/03/on_call/
[8] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/applications&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=4&c=44afR5wzpV0i7zotHdK-7kHgAAA5U&t=ct%3Dns%26unitnum%3D4%26raptor%3Dfalcon%26pos%3Dmid%26test%3D0
[9] https://pubads.g.doubleclick.net/gampad/jump?co=1&iu=/6978/reg_software/applications&sz=300x50%7C300x100%7C300x250%7C300x251%7C300x252%7C300x600%7C300x601&tile=3&c=33afR5wzpV0i7zotHdK-7kHgAAA5U&t=ct%3Dns%26unitnum%3D3%26raptor%3Deagle%26pos%3Dmid%26test%3D0
[10] mailto:oncall@theregister.com
[11] https://whitepapers.theregister.com/
Re: Maths
I remember explaining to an accountant that if a tax rate had gone up by 5% and had then come back down again you couldn't get back to where you started by reducing your price by 5%.
Re: Maths
Was his name Lutkin, by any chance? I know of a guy who thinks percentages might work like that…
Re: Maths
When I saw "What about the Sieve of Eratosthenes?" I remembered being taught about that aged 8 or 9... So, to me, anyone not knowing about that ancient Greek seive is a bit odd... I must have had an outlier primary school!
Re: Maths
The good old Sieve of Erastothenes. This must be used in so many introductory computer science courses, if only to show that the notion of an algorithm predates any programming language or digital computer by millennia. I am not sure whether mathematics courses use this as often. Still quite surprising the maths teacher didn't know it.
Re: Maths
I remember a ZX Spectrum BASIC programming book from the 1980s casually mentioning that musical notation was probably one of the oldest programming languages .....
Re: Maths
Applied for a dev job many years ago and, amongst the many programming tests, was one to find the first 100 prime numbers. They were expecting a 'Sieve of Eratosthenes' implementation. However, I pointed out the prime numbers were very well known and hundreds of thousands, possibly millions, had already been discovered. Further, this set of primes was not going to change. My solution just loaded a file with the first 10k known primes and took the first 100. They were not happy, though very impressed at how well I 'cheated'. I didn't get the job.
Analogue clocks
The wife is a primary school teacher. She complained that the children cannot read clocks. So I pointed out that if the two hands are confusing them, show them how to read time with only the short hand - the long hand is not really necessary - ignore it.
There are indeed 500 yo clocks around here with one hand, or two separate dials for hours and minutes. The concentric driven hands were invented more recently.
Re: Analogue clocks
We do make it hard for kids. First, it's counting in decimal, we drum 1-to-10 into them, they have 10 fingers, off they go.
And then at school we say, "here's something called time that it's going to be really, really important to be able to count". The kids are like "ok, so I can count, no dramas, what's the big deal?". And then the teacher says (in effect) "Well we're going to count it in sexagesimal because
Re: Analogue clocks
But the first number isn't one..... It's zero....
Re: Analogue clocks
So hours in the day go from 0 to 12 twice. That's 26 total numbers
Re: Analogue clocks
There was a brilliant article on the BBC just recently about the origins of the hour. It seems it goes back all the way to the Sumerians who did basically work in a base 12 system.
The explanation was that you count the 3 joints on the 4 fingers (not the thumb) to get your 12. And for 60 you basically fold down a finger (and the thumb) on the other hand after each count to 12, in order to count to 60 for the number of minutes in an hour.
The system has basically lasted ever since. Although the French did apparently try to decimalise time at once point. It apparently only lasted about a year, before even they gave up and went back to standard hours and minutes...
Re: Analogue clocks
re: It apparently only lasted about a year,
So about 3.6 decyears.
Re: decimal system
The drive to decimalise everything is based on the false premise that it makes everything easier.
While that's true in some cases, particularly for people who can't count past ten without removing their footwear, where the decimal system falls down is divisibility. 100 can only be exactly divided by 2, 4, 5, 10, 20, 25 and 50 so if you have 100 items to distribute between 40 recipients, you're going to have a problem.
Consider the number of degrees in a circle & how many ways you can divide that; you might think "so what" but then consider using a compass to navigate on a sea.
Re: decimal system
"consider using a compass to navigate on a sea."
A compass (or sextant) graduated in milli-radians might be a bit more interesting than useful.
Re: Analogue clocks
Similarly our seven-day weeks have names derived from the planets or the gods associated with them thanks to the Sumerians. Incredibly influential astronomers.
Re: Analogue clocks
Dave Allen has entered the chat (30 years ago).
https://www.youtube.com/watch?v=0QVPUIRGthI
Re: Analogue clocks
You're not wrong about the complexity, but I did find out relatively recently that there is a good reason that they're called seconds. It is down to the division of the hour in two levels. The first is 'pars minuta prima' (minute) which means the first small part, and 'pars minuta secunda' (second) which means the second small part.
When I first heard that I had one of those really wonderful Aha! moments as a small but constant part of my life suddenly made more sense.
Re: Analogue clocks
Thanks for that.
Re: Analogue clocks
Today is a good day, something new learned after all these years (and months, weeks, days, hours, minutes and seconds)...
Indeed thanks for that, have a deserved upvote.
Re: Analogue clocks
Indeed, something new learned! Fascinating topic when you dig into it a bit further. Have one on me ----->
After watching waaay too many VRChat trolling videos lately...
... the phrase "Oracle ERP" means something far, far worse to me than it does to you!
As long as the users F**k up the data *after* its left The Warehouse thats ok.
Its when they do it on the way in its a problem.
"It was fine when it left our router".
I saw a headline that said Excel and fun. Just no, that's a special kind of masochist. Yes, been there before, the staff concerned couldn't count and I ended up locking all the calculations with a password to stop them putting in the wrong data manually.
The number of people who've extracted data from one of my reports, averaged the percentages and then told me my that final numbers are wrong, coincidentally matches the number of times I've had to relay my patio.
One I've had occasionally, and had to explain in detail to users, is averages of averages* in Excel pivot tables.
People assume that if you have several entries for Average Price you can just take an average of that, which is only true if they all have the same sales quantity.
I'm sure some of them still don't understand but do Sum of Value / Sum of Qty just to humour me!
*Something I learnt about very early in my mathematical studies and have been wary of ever since!
Average of averages, bane of my life. Trying to explain to sales staff that just because they get an average of 40% profit from retail customers and 20% from wholesale customers doesn't mean their average profit will be 30% if 90% of their custoemers are wholesale.
So in this case, does the concept of a weighted average indicate the heft of the 2x4 or baseball bat you need to use to get the concept across?
My definition of stress "The inability to strangle some idiot who really deserves it!"
Maths
Not an IT one but...
Was working in a private school as an IT manager.
Over the summer the playground had all new tarmac and markings, including a 100-square. The head had ordered that over the year EVERY subject teacher must find a usage for those markings, even if it was only once, with the kids.
There was lots of moaning (I mean, sure, French, Science, I can think of all kinds of uses, but Religious Studies?) but one of them was the maths teacher, who was moaning to me, the IT guy about it. It was weeks later and he needed to get his lesson plans in order and they had to show usage of the markings.
He was okay for the younger kids but couldn't think of anything to do with his older kids (Y7/Y8). This, in itself, was quite funny to me.
"What about the Sieve of Eratosthenes?" I asked.
He looked at me dumbfounded. I mean, fair enough, the name is quite obscure. So I explained:
"You know... put a counter on all the multiples of 2, then all the multiples of 3, then all the multiples of 4, 5, etc. until you get to 10."
"And then what?!"
"Then... the numbers that remain will all be prime."
"WILL THEY!?!??!!"
He was flabbergasted. This was a guy with a degree in maths, a teaching qualification, teaching children maths, in a private school.
"And why 10?" he asked me at one point.
"Because that's the square root of 100, and it's a 100-square?"
He ran off and started preparing his lesson.
I mean, sure, I had a maths degree, but... I'm the IT guy!!! I shouldn't know that, and you NOT know that.