It Takes a Village to Fix a Spreadsheet

August 10, 2020

It Takes a Village to Fix a Spreadsheet

Ben Zoma said: “Who is wise? He who learns from every man, as it is said: ‘From all who taught me have I gained understanding’ (Psalms 119:99)”
Pirkei Avot (The Wisdom of Our Fathers) 4:1

This is a meditation on inclusion, inclusiveness, being open-minded, and recognizing how anyone can help you—no matter who the “you” or the “they” are in that statement. No matter your relative levels of knowledge, experience, or intelligence. No matter the difference in ages. No really, no matter any of that.

Sit, child, and hear my story.

For over three years, I’ve been responsible for publishing the times for prayers and associated observances for my synagogue. If you know what that is, you are probably already recoiling in horror. But for those who don’t, let me give you a taste.

  • Start with nautical sunrise
  • Also make sure you have nautical sunset
  • A “day” is the total available daylight, or the time from sunrise to sunset

.. so far, so good, right?

  • “Dawn” (a murky concept, but necessary as it marks the time to say certain prayers) is sunrise minus 1/10 of the day, or
    • sunrise-((sunset-sunrise)/10)
  • Meanwhile, “night” (another murky concept) is 45 minutes (regular ones) after sunset
  • An hour of Jewish time is 1/12 of the total available daylight, or
    • (sunset-sunrise)/12
  • BUT my synagogue has a different tradition, so OUR hour is calculated as
    • (night-dawn)/12
  • Afternoon prayers can be said 6.5 “Jewish hours” after dawn

..and so on…

The upshot is prayer times change not only day to day and from town to town, but even from one side of a single zip code to the other (since each movement east or west has a small incremental effect on when the sun rises or sets. And yes, for many orthodox Jews, it matters). Calculating those times requires enough non-stop mathematical finagling to constipate Einstein*. And the job is all mine—day by day, week by week, calculating and announcing the times.

Of course, not for nothing have I worked in IT for 30 years. Believe me, I’ve leveraged my technical prowess to apply tools, automation, and sheer force of will to reduce the overall effort. Like most highly complex custom IT projects, this mostly boils down to having a massive spreadsheet (albeit kick-ass, if I do say myself).

A quick glance at those calculations shows the critical piece is sunrise and sunset. Once you’ve got those, the rest of the calculations fall into place. For as long as I’ve been doing this, getting those two key times meant plugging the exact latitude/longitude of the synagogue into an online “date and time” site and manually typing in the results. And any red-blooded IT practitioner knows “manual process” is another way of saying “you’re a chump.”

Sure, those sites had an API, but getting a spreadsheet to call an API usually requires a macro. And if I was going to write a macro, I might as well just code up the entire thing in PHP and make it a self-generating webpage. This was time (not to mention the expertise required to keep my sanity intact) I simply did not have.

But recently (meaning “About 11:30 last night,” in relation to when I’m writing this) I learned about the “webservice()” spreadsheet function in LibreOffice Calc. This would allow me a code-free option to call the API and get those times for my location. By 12:30 a.m., I had the webservice function call working; I’d figured out how to extract the values I needed from the data series it was returning; and I’d populated the “sunrise” and “sunset” columns in my spreadsheet. I was delirious with joy (and also exhaustion, and possibly one too many cups of coffee). Those two critical columns were populated for months, without me typing a single number!

And everything else was a big series of “Err:502” results.

OK, something was clearly wonky in my formulas. I just had to figure out what it was. At 2 a.m., I’d made ZERO progress. To be sure, I had tested a lot of things that didn’t work but was no closer to knowing why it wasn’t working. I packed things in.

The next day, I made barely any additional progress. The small piece I’d sussed out was the numeric value for the timestamp was different if I hard-coded the time (just wrote “9:05 a.m.” into a cell) versus the value I was pulling from the webservice. Hard coded was a positive number. The webservice was giving me a negative number. But I was completely baffled as to why.

That’s when my wife came up to see why I hadn’t left my office in several hours.

Now, I need to explain that my wife is an amazing person in a lot of ways—and I’m not just saying that because we just celebrated our 32nd wedding anniversary. She is a gifted ophthalmic medical technician; she is one of the best cooks I’ve ever met; she has an intuition about raising children and managing a family that defies explanation; she’s a financial wizard; and on top of all that she’s been an amazing friend for almost four decades. But, with all of that said, she is hopelessly inept when it comes to computers and technology.

It’s one of our little jokes. Without her, I would be starving, naked, and destitute. Without me, she wouldn’t be able to print recipes or get onto the internet at a friend’s house.

So, if someone like her sat down and asked if they could help figure out why a webservice function call to a remote API wasn’t working, a lot of folks would be kind, but dismissive, saying something like, “Oh, I appreciate the offer, but honestly this is just really complicated. I’ll figure it out eventually.”

But she’s not only my friend, she is my duck. And since this isn’t our first rodeo, in terms of solving a problem, we didn’t quibble about whether she could or couldn’t help. We just got down to work. She would ask questions—sometimes simple, sometimes questions that seemed off topic or tangential, and sometimes questions appearing to indicate she was confused about what was even going on.

My job in these scenarios is to answer every single one of her questions until she understands it to her (not my) satisfaction. It turns out one of those “not understanding what is even going on” questions that was the key.

She pointed to the webservice return values on the screen: “How can sunset be at 1:45 a.m.?”

I explained the times are being returned in UTC. So, our sunset for that day is 1:45 a.m. UTC, and then I make the adjustment for our time zone (subtracting 4 hours).

“So, you’re subtracting it into the day before?” she asked.

My response was to blink stupidly at her, and then at the screen. Then to dance around the room and give her a hug (there may have been kissing involved as well).

There it was.

My point in all of this is not to say, “get yourself a duck” (although that’s not a bad idea). Or that having a spouse who is also a best friend and is willing to sit with you and talk you through things is a total life saver (although it 100% is).

I’d like to offer a lesson a little deeper than that: I want to shine a light on how we as IT professionals (and honestly, most humans) fall into the trap of thinking people we perceive as “better” than us—wiser, smarter, more experienced, more educated, more certified, etc.—are the only ones who can help us with our problems. And the corollary is we should always know (or be able to quickly discover) answers to questions asked by those we perceive to be “less good.”

Neither of those statements are even remotely true, and we do ourselves an enormous disservice when we burden ourselves and those around us with these beliefs. Experts often make simple mistakes and fail to identify or correct those mistakes even after going over their work multiple times. So-called “newbies” often have flashes (or even longer periods) of brilliance. Students often provide new insights to the teacher.

The lessons I’d like to share here is the importance of inviting people from across the spectrum of our communities to the table, and to extend those invitations in every phase of our work as IT practitioners. When we are scoping a project; or performing a post-mortem; or planning a sprint; or brainstorming a new product—in all those cases we’re poorer in every measurable way if we do not listen to the perspectives of folks who think of themselves as non-technical; who don’t have pull or influence on the project or in the company; who won’t interact with the technology at all.

If we remain stuck in the false belief of a hierarchy when it comes to experience, insight, relevance, or clout, we miss out on the chance to connect and grow—both as IT practitioners and as people.

“And this is what Rabbi Chanina said: ‘I have learned much from my teachers and even more from my friends, but from my students I have learned more than from all of them.’” (Tractate Ta’anit, 7a)

* hat tip to Cecil Adams for this delightful turn of a phrase.

Leon Adato is a Head Geek and technical evangelist at SolarWinds, and is a Cisco® Certified Network Associate (CCNA), MCSE and SolarWinds Certified Professional (he was once a customer, after all). His 25 years of network management experience spans financial, healthcare, food and beverage, and other industries.