Named Variables in Excel

Ishai bore the unenviable, oft-cursed title of Microsoft Support Engineer. Just about every user who ended up in his call queue was peeved from the start, having navigated half a dozen phone menu options and being stuck on hold for interminable wait times. It didn’t make for a productive support experience.

There was nothing Ishai could do about it. After years in the trenches, he was used to it; customer ire was like his own personal cosmic background radiation. But no matter how many times one thinks he’s seen everything, the universe always has something more to reveal to him.

His desk phone rang for the umpteenth time that day. “Tier three support, this is Ishai. How may I help you today?”

Instead of the mix of frustration and relief most people had upon reaching a human, this caller started off … haughty.

“I am Professor Benson. Got that? Professor Benson, not ‘Mr. Benson’ or ‘Sir’ or anything else you decide to call me. I’m a Computer Science professor at BigName University, and I’ve found a bug in the latest version of Excel.”

Get in line, Ishai thought. Happily, he was free to roll his eyes without risk. “Can you explain the problem, si—uh, Professor?”

“I’m performing a complex calculation.” Professor Benson rattled off the name of an obscure formula that took several variables as input, a formula Ishai had little familiarity with. “The results Excel gives back are wrong. I have no trouble working the calculation by hand, and when I do, I get the expected result.”

“I see,” Ishai said. “I’ll need some time to look into this. Can you provide your callback number? I’ll get back to you with a progress update within the next week.”

“The next week?” Professor Benson sniffed. “All right. I suppose I have no choice.”

Ishai obtained his contact information and gratefully ended the call, but his relief was not to last. The formula turned out to be extremely complex. He needed almost the full week just to figure out what it was supposed to do. Finally, he amassed enough understanding to attempt a few calculations by hand.

To his complete lack of surprise, he found his calculations matched Excel’s results every time.

“I’m sorry, Professor Benson,” Ishai spoke over the phone a short while later. “It looks like everything’s working as expected.”

“Well, yes,” Professor Benson replied. “In the simplistic cases you no doubt tested with, it works fine. But in more complex scenarios, it doesn’t.”

Ishai frowned. “Maybe you could send me a representative sample to test with?”

“Well, all right,” the professor huffed. “If you think you can handle it.”

“I’ll give it my very best shot,” Ishai half-growled back.

Ishai received a sample workbook from Professor Benson a short while later. Upon opening it, he had to bite back a sob. The calculations were hard enough with integers. Here, the good professor was using exponents and other values out to 7 decimals. The sort of thing that was next to impossible to calculate by hand.

It took another long week of struggling, but Ishai finally completed the calculations. To his shock, Professor Benson was right. Excel was giving the wrong answer—or so it seemed at first. Since part of the formula involved raising variables to the power of other variables, every single digit counted. The professor had configured his workbook to only have 2-decimal digit precision—thus, numbers like 2.0103235 were being truncated down to 2.01 before any mathematical operations were performed. Once Ishai changed the decimal precision to 10, Excel began providing the correct answer.

Relieved, and not without some measure of evil glee, he called Professor Benson and explained the issue.

Instead of the slightest bit of gratitude, Professor Benson’s tone of voice froze over. “And I assume you believe this is fixed now?”

“Well, nothing’s actually broken,” Ishai explained. “Excel’s behaving correctly. You just—”

“’I just’ nothing!” the professor cried. “If I didn’t know about changing the precision, how can you expect anyone else to? Your implementation of the formula is flawed! I should be getting a refund from you people, or at least a medal!”

“I’m sorry, but there’s no bug here,” Ishai pleaded.

After another 30 minutes of unproductive bickering, Ishai finally got Professor Benson off the phone—but not before Benson had threatened to complain to his manager, his manager’s manager, all the way up to Bill Gates if he had to.

Ishai slumped over his desk, nursing his temple. “Man, I gotta become a developer,” he muttered. “I bet they don’t deal with crap like this.”

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!