User found the perfect formula to make Excel misbehave

For once, Oracle ERP wasn’t the problem

On Call Fridays can be a drag, but The Register has a formula to inject a little fun by delivering a new instalment of On Call – the reader-contributed column in which we share your tech support stories.

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.

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.

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.

"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.

"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!

Have you had to teach your users basic mathematics? If so, why not add your story to the On Call mailbag by 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. ®