No, “Iterative Calculations” is not the name of my new band
Product Marketing 101 (a class I am imagining, but definitely did not actually take) says “don’t sell the feature, sell the value”. Here at Resolution, we’ve taken this to heart, drowning you with examples, use cases, and solutions to an almost comically large collection of real-life problems.
However, people still often ask me what Resolution does. This is not a question that’s easy to answer with “value” unless you are really annoying and sales-y, and kind of dodge the specifics and re-direct towards some slimy discovery question like “wHaT aRe YoUr oRgAnIzAtIoNaL gOaLs?” So I won’t answer it that way. I’ll actually answer it!
Resolution is in most ways, a pretty limited version of a spreadsheet that does one completely insane thing spreadsheets cannot do — it allows you to treat any “cell” (their language, not mine) as a specific, user-set value and a formula-calculated value at the same time, all the time.
This isn’t the only thing that’s different about Resolution (it doesn’t use actual formulas at all, for instance, it automatically uses Units for things, etc.), but it’s definitely the thing that, once you get it, makes you realize that there are entirely types of work and problem solving you can do with it that you will literally never be able to do with Excel or Sheets or the like despite those applications having been around for decades and having seemingly infinite capabilities.
When I first started trying to do these things in my spreadsheets, to a person, basically every intermediate-type Excel person told me the thing I wanted to do was impossible, and that I just needed to bite the bullet and make a second spreadsheet.
(Side note: In fact, every intermediate Excel user always advised me to use a pivot table, and would excitedly offer to show me how to make one. It didn’t matter if a pivot table would solve my problem or not, or if my problem even had anything to do with what a pivot table does. Find someone who loves you as much as intermediate Excel users love pivot tables.)
Before really diving into building Resolution, I looked into it things a bit further. As it turns out, the answer is a little more complicated. It’s still basically “no, go make a second spreadsheet”, but in some ways, not for lack of trying. Believe it or not, spreadsheets really have tried to solve this problem.
… sort of.
Iterative Calculations and the Bizarre Power of Brute Force
One thing I’ve often mentioned about spreadsheets is that they haven’t really changed, fundamentally, in my lifetime, even though they’ve become relevant to more and more people in more and more roles. Things like Solver and Goal Seek in Excel (and their dopplegangers in the Google Sheets extension libraries that work in almost the exact same way) are great examples of this. These add-ons are fascinating to me because, rather than restructure how a spreadsheet thinks about data and relationships to allow it to answer certain kinds of questions, they basically just use a spreadsheet the way a person does. They literally test out different values — using the existing spreadsheet — and compare the results until they land on what they think is the best answer, which they then autopopulate for you. The only real thing they do is keep the results of a WHOLE bunch of scenarios, and then compare them for you.
This is… fine? You can answer a lot of questions with brute force like this, especially if you have a program to do it for you. But it’s not really a “feature” of Excel or Sheets, as much as an especially complex little macro that can use Excel or Sheets for you in a specific (read: insane) way. Solver is a more complex version of the same thing; you can allow auto-Excel here to test multiple variables in its hundreds, thousands, or millions of calculations, and it’ll give you the best solution it can find, using a method you prefer. You’ll even notice some Resolution-y elements in here, like being able to solve with certain values having a minimum or maximum to them.
From a pure problem solving perspective, I think this is actually pretty cool. It’s probably very helpful in some very specific power user scenarios, and it definitely fits into Excel-culture of “wait, no, technically we can do what we want if we just do something that seems insane but is not actually impossible”.
But there are a couple of enormous reasons why I’d never, in a million years, use this instead of Resolution in my everyday workflows.
1) It’s Slow
Spreadsheets aren’t built for this kind of brute force, and it shows. Even a basic “how do I get A5 to equal 10 by changing B5” goal seek function can get you a status bar and a spinning ball cursor. I don’t want to know how long it takes to handle multiple variables with cascading consequences. Part of the problem is that Goal Seek and Solver are both looking for specific, “correct” answers for you, and generating that through brute force just requires a ton of individual cell calculations. It’s fine if you are getting the ultimate answer to the question “what is the meaning of life” (p.s., it’s 42) with a single, sluggish Goal Seek calculation. But this by no means puts formula-derived cells on equal footing with manually entered value cells. You’ll know when you are using this process, because it’s really slow and…
2) It’s Annoying
“Technically, you actually can…” is always the beginning of a terrible, useless answer, and for 99% of my scenarios, that’s exactly what Solver and Goal Seek are. If you want to poke at a bunch of values and see an entire system flex and change when you do it, this is not what you want. If you want outputs and inputs to work the same way so you can define an input or work backwards from a desired output, this is absolutely not what you want. This is a cool workaround to “is it possible to…”, not a solution to the problems that come from it not being how spreadsheets are designed to work. You have to make a very deliberate choice to derive a value this way; your spreadsheet is not going to just do it because it makes sense in the context you’re working in.
3) It’s Basically a Hack
Hacks are cool. Hacks often come from clever people. Hacks make the world go ’round! But hacks are also exhausting, and cause chaos and unpredictability, and that’s exactly how Goal Seek and Solver work. These things aren’t even on by default! In Sheets, you have to find them in the Extensions store (Goal Seek is from Google, Solver is from some company) and turn them on, and then they run as little sidebar apps. The Excel scenario is even weirder; Solver is built into Excel but it’s not on by default either, and you have to go through this byzantine set of preferences to even make it available. Why? I have no idea, but maybe it’s because the UI that appears looks like this:
Look, we have many UI/UX things to improve in Resolution, no doubt. But this isn’t even an attempt to make this make sense. This is a Visual Basic script built for people who enjoy building Visual Basic scripts. And keep in mind, this all sits on top of Excel. It’s just basically using Excel for you, albeit in a pretty clever (and very persistent) manner. But that means your spreadsheet itself has no idea what you actually want here — it never does — and as a result some things are going to work in very weird ways. Undo, for instance, got all kinds of messed up in Google Sheets when I used Goal Seek. I haven’t played enough with the Excel implementation so maybe it’s smoother, but generally companies don’t build amazing intuitive data exploration features, leave them off by default, and not tell you about them unless they are a little rough around the edges.
4) It’s Opaque
This is both the most trivial and forgivable sin of iterative calculations, and somehow the one that bothers me the most and seems the most at odds with the ethos of Resolution. But the biggest challenge I always had with spreadsheets wasn’t necessarily getting specific answers to things. That was annoying, but just like Goal Seek and Solver show, it was also literally possible as long as you changed a value twenty billion times, or made a second spreadsheet, etc. and so forth. Iterative calculations give you value by somewhat clumsily automating that annoying work, and doing it at massive, beyond-human scale. So yeah, answers can be derived if you have the stomach for it.
The problem I really had with spreadsheets is that they were terrible at conveying any actual grasp of the numbers and situation at hand. I could give you my Marketing Leads spreadsheet, and you’d learn some stuff, but you’d also waste a lot of time trying to figure out where lots of numbers come from, whether certain things are calculated correctly, and whether some factors mattered a lot or a little. Or, more likely, you’d give up and either trust me or not trust me based on whether you thought I was intelligent, or my suit fit properly (it never did). For real understanding of the universe my spreadsheet was representing, you’d need to try things and work backwards and see all of the pushing and pulling I mentioned earlier.
You simply do not get that experience — at all — with any of these features, or with iterative calculation tech in general.
So… what?
After really digging into these iterative calculation features for a couple days, I have a few conclusions.
- The mechanic that Resolution doesn’t just do, but is actually built around — enough people want to do it that BIG SPREADSHEET has made an attempt
- They are doing it for a very different reason; it’s to spit out a correct answer, not to explore relationships between things
- It’s a pretty lame attempt, honestly, and feels like a macro that might break at any moment (and in Google’s case, apparently does, according to the reviews)
- There is just zero appetite from BIG SPREADSHEET to fundamentally expand how spreadsheets work or what they can handle… they aren’t coming for this corner anytime soon
All of that makes sense. Until I can get people to realize how awesome it is to learn through spreadsheet-like numerical relationships without the brittleness and technicality of actual spreadsheets, and do that in large numbers, why would they waste their time trying to solve this?
Onward we march, then, towards a glorious new day of easily manipulatable mathematical logic models.
