As you become more experienced with Excel, you get comfortable with the idea of “nesting” one formula within another one. A classic use of this is with nested IF() statements where the first IF() checks a TRUE/FALSE option & then the Value_If_True and Value_if_False arguments include additional IF() statements to continue the branching off to a final, definitive answer.
However, without care, this can easily start to look confusing & what’s more it can be difficult to work through the various stages. Perhaps this little tip will help – it’s an extension of the idea that we looked at last month where, by using ALT+ENTER we can force a “word wrap” within a cell.
If you need a reminder of that, here’s that tip: https://us4b.co.uk/wrapping-text-in-excel/
The problem with “nested” formulas
Here’s an interesting formula in a spreadsheet that I’ve been working on for the tabletop wargaming club that I belong to.
The idea behind the spreadsheet is to look at the ranges that a weapon can fire and then categorise them as “Short”, “Medium”, “Long” or “X/Long” as we then have a set of rules that determines how easy it is to see things at those ranges & ultimately, use Conditional Formatting to colour the cells accordingly as the values will be different dependent on whether there are (as in this example), 7, 9 or 10 values to look at.
So, first of all, as the Hitch Hikers’ Guide to the Galaxy said on its front cover: Don’t Panic!
This isn’t going to be a detailed walk through of the formula (especially as I have more tweaks to do to it for it to work exactly how I want!), nor an introduction to tabletop wargaming. What it will be is to give you an idea of how to make formulas easier to understand & edit.
Expanding the Formula bar
As you can see in the formula, it’s a fairly long one & goes (at the moment) over two lines within the formula bar; normally you only see one.
To increase the depth of the formula bar, you need to move your mouse to point just above the column letters of any of the columns in your spreadsheet & it will turn into an open, double-headed arrow.
Once this has happened, hold down your left mouse button & drag down to see a larger formula bar area
Editing the formula
We can now edit the formula to assist readability. We could do this first & then expand the formula bar to fit; whilst we may need to reset the depth once the edit is done, personally, I find that creating the space first easier to use.
So, to edit the formula, we need to select natural breaks… as much of the formula is made up of IF() statements, then a logical break point would be immediately before the Value_if_false argument…
To do this, we left-click within the formula & then, in the same way as we did in last month’s tip when we wanted to manually word wrap some text (here’s the reminder again: https://us4b.co.uk/wrapping-text-in-excel/ ) we press ALT+ENTER
& that will split the formula…
.. so that it now looks like this…
We can then repeat this as necessary…
There are several advantages to splitting IF() statements in this way… if the Value_if_False argument is also an IF statement, then each line starts with IF, so it’s easy (easier?) to work through… of course, we could also choose to split the current third row as that’s still fairly complex. The key is to have “bite-sized formula chunks” on each line to make it easy to read.
Also, just to note that, whilst these IF statements are at the start of a row, they do not need an equal sign (“=”) that a formula usually requires as they are all simply part of the initial row which does need the “=”.
Taking things further
To make a formula even easier to read, we could choose to give some of the cell references names; it probably wouldn’t work in the above formulas, but if you have a column of sales values & need to multiply them by VAT or a discount rate, it would make more sense meaning that you could have a formula that read =Sales*VATRate but we’ll save that little tip until next month