When you’re choosing from a long list with many options, it’s helpful to have filters to narrow down your search. This is a familiar concept on many websites—for instance, on the flight search website Kayak, you can start with a list of thousands of flights, and then filter down to the ones that meet your needs: “a nonstop flight, leaving before 11am, in economy class”.
What if you could apply this same concept of filtering to any problem in your life? Whether it’s deciding on a budget or planning a project, we all encounter situations where we’re considering a large number of options, and need to narrow down our search. In this note, we’ll show how to do that using filters in Ambsheet, our research prototype of a spreadsheet for modeling multiple scenarios.
In our previous note we introduced the basic idea of ambsheets using a simple budget. We showed how you could create some input cells which each contained multiple values—choosing between two cars and three apartments—and the spreadsheet would show you 6 amounts for the total sum, representing the possible combinations based on your choices.
Now, what if you want to narrow your focus to a subset of these possible scenarios? Our prototype has a filter menu on the left of the screen that enables you to do just that. For example, maybe you only want to consider scenarios that involve the cheaper car:
Now you can see that some of the values in cell B7 (TOTAL) are greyed out, indicating that they’re no longer possible. We can similarly filter on the apartments by selecting only the two higher-priced options, and we see that the set of possible values in cell B7 shrinks once again:
So far these conclusions have been fairly obvious. But filters in Ambsheets also support more interesting analyses because you can filter on any cell, including the outputs of computations.
To illustrate this, let’s imagine that your budget needs to be under $4200 per month, and you want to know which combinations of car and apartment are viable options given that constraint. There’s a nice pattern you can use for this: write a formula labeled “can afford” that evaluates to true or false, and then use it to filter down to only scenarios that we can afford:
We’ve filtered the output cell, and the ambsheet now shows us which inputs correspond to those outputs. We can see that the two more expensive apartments have been greyed out—that’s because there’s no scenario where we can choose one of those apartments and still satisfy our affordability filter.
The pattern of filtering on formula outputs scales nicely to more complex spreadsheets. For example, imagine you’re budgeting for a wedding in an ambsheet. You can create multi-valued amb cells representing choices: the venue, whether it’s on a weekend or weekday, and the number of guests. You can then set up a series of calculations that compute the total cost of the wedding, based on a given set of choices. (We’ll skip the details because there’s nothing unusual in the spreadsheet logic, just some straightforward math.)
Now you can do the exact same exercise as before: filter on the “total” cell to consider affordable choices. In this case we’ll use a different UI for filtering: a histogram with draggable slides, which helps you see the range of values as you filter. But the underlying logic is exactly the same as before: you’re picking a subset of outputs, and the tool shows you which inputs correspond to those outputs.
Filtering the wedding ambsheet instantly provides a picture of which wedding venues and dates are realistic choices at different price points. As you set an upper limit on the budget, you can see in the table view at the bottom right that the cells in the Library column greyed out, telling you the Library isn’t viable as a venue if you want to keep the wedding under a certain price.
Using spreadsheet formulas to express filters provides generality while leaning on familiar knowledge. Many analytics systems have clunky “query builders” for constructing filter criteria, which are limited to expressing simple inequalities or boolean ANDs and ORs. Meanwhile, spreadsheet formulas can flexibly express an open-ended set of mathematical and logical expressions, and many people already know how to write them.
While we think the basic idea of filtering on formulas is compelling, there are some limitations we’ve noticed in this prototype. One issue is that only one set of filters can be active at any one time. Sometimes you might want to compare across the results from multiple distinct sets of filters, and it’d be useful to see them simultaneously without “toggling” back and forth. Another question is how to represent the state of the currently activated filters—should that be shared collaboratively? Can you save sets of active filters? What are patterns that emerge when using filters to evaluate decisions? We’ll share more ideas on these fronts in upcoming posts.
This is the last entry for now, but you can go back to read the lab notebook from the beginning.
The Ink & Switch Dispatch
Keep up-to-date with the lab's latest findings, appearances, and happenings by subscribing to our newsletter. For a sneak peek, browse the archive.
Instagram Engineering
Shopify Engineering