Another Immovable Spreadsheet

Steve had been working as a web developer, but his background was in mathematics. Therefore, when a job opened up for internal transfer to the "Statistics" team, he jumped on it and was given the job without too much contest. Once there, he was able to meet the other "statisticians:" a group of well-meaning businessfolk with very little mathematical background who used The Spreadsheet to get their work done.

The Spreadsheet was Excel, of course. To enter data, you had to cut and paste columns from various tools into one of the many sheets, letting the complex array of formulas calculate the numbers needed for the quarterly report. Shortly before Steve's transfer, there had apparently been a push to automate some of the processes with SAS, a tool much more suited to this sort of work than a behemoth of an Excel spreadsheet.

A colleague named Stu showed Steve the ropes. Stu admitted there was indeed a SAS process that claimed to do the same functions as The Spreadsheet, but nobody was using it because nobody trusted the numbers that came out of it.

Never the biggest fan of Excel, Steve decided to throw his weight behind the SAS process. He ran the SAS algorithms multiple times, giving the outputs to Stu to compare against the Excel spreadsheet output. The first three iterations, everything seemed to match exactly. On the fourth, however, Stu told him that one of the outputs was off by 0.2.

To some, this was vindication of The Spreadsheet; after all, why would they need some fancy-schmancy SAS process when Excel worked just fine? Steve wasn't so sure. An error in the code might lead to a big discrepancy, but this sounded more like a rounding error than anything else.

Steve tracked down the relevant documentation for Excel and SAS, and found that both used 64-bit floating point numbers on the 32-bit Windows machines that the calculations were run on. Given that all the calculations were addition and multiplication with no exponents, the mistake had to be in either the Excel code or the SAS code.

Steve stepped through the SAS process, ensuring that the intermediate outputs in SAS matched the accompanying cells in the Excel sheet. When he'd just about given up hope, he found the issue: a ROUND command, right at the end of the chain where it didn't belong.

All of the SAS code in the building had been written by a guy named Brian. Even after Steve had taken over writing SAS, people still sought out Brian for updates and queries, despite his having other work to do.

Steve had no choice but to do the same. He stopped by Brian's cube, knocking perfunctorily before asking, "Why is there a ROUND command at the end of the SAS?"

"There isn't. What?" replied Brian, clearly startled out of his thinking trance.

"No, look, there is," replied Steve, waving a printout. "Why is it there?"

"Oh. That." Brian shrugged. "Excel was displaying only one decimal place for some godforsaken reason, and they wanted the SAS output to be exactly the same."

"I should've known," said Steve, disgustedly. "Stu told me it matched, but it can't have been matching exactly this whole time, not with rounding in there."

"Sure, man. Whatever."

Sadly, Steve was transferred again before the next quarterly run-this time to a company doing proper statistical analysis, not just calculating a few figures for the quarterly presentation. He instructed Stu how to check to fifteen decimal places, but didn't hold out much hope that SAS would come to replace the Excel sheet.

Steve later ran into Stu at a coffee hour. He asked about how the replacement was going.

"I haven't had time to check the figures from SAS," Stu replied. "I'm too busy with The Spreadsheet as-is."

[Advertisement] Utilize BuildMaster to release your software with confidence, at the pace your business demands. Download today!