A friend called me the other day with an Excel question. He came across a formula in one of his reports that didn't look the way he would have written it.
It's a simple IF formula to calculate the difference between budget numbers and actuals.
The way it was written was =IF(D5, D5-D4,””), where D5 would be the budget number and D4 would be the actual number.
Because the first D5 in the formula was all by itself and didn't have any logical statements attached to it, he was thrown off. My friend would have written the formula =IF(D5>0, D5-D4,””). Here are both formulas so you can compare them:
In my friend's version, the formula is saying: “If the value in D5 is greater than zero, subtract the budgeted number from the actual number, and if not, return a blank.”
The first version would be translated more like this: “If there is a value in cell D5, subtract the budgeted number from the actual number, and if not, return a blank.”
Which is better?
So is the first formula better because it's shorter?
Not necessarily. An important factor in building your formulas is using logical statements that make sense to you and/or your audience. If a formula is confusing, it may be best to go another route.
One thing to note is that in the example above, the report is dealing with values that are always positive numbers (like Sales, for example). But if we were dealing with projected numbers that might be negative (Revenue/Expenses, for example), the first formula might be a better choice because it would show values for negative months.
On the other side of the coin, however, if my friend were dealing with negative numbers or zeros for his actuals, he could alter his formula to read: =IF(ISBLANK(D5),””,D5-D4). In other words: If the Actual is blank, then return a blank cell, otherwise, subtract the Budget from the Actual. This would have the same effect but might be more logical to his and many people's way of thinking.
For either formula, when dealing with negative numbers, I would recommend adding some number formatting to make the font red (as above) or conditional formatting to change the fill color when a number is negative. This would better highlight that the variance is a negative thing instead of a positive thing.
I explain conditional formatting in these tutorials:
- How to Apply Conditional Formatting to Rows Based on Cell
- Conditional Formatting for List of Partial Matches
- Compare Two Sheets for Duplicates with Conditional Formatting
- Enhance Your Checkboxes with Conditional Formatting in Excel
Even with negative numbers, the point remains the same: write your formulas in a way that makes the most sense to you and your audience.
Thoughts, comments, or questions? I'd love to hear them. Leave a comment by clicking here.
I personally would have had the positive variance in one cell and the negative variance in the cell below the positive. That way it would be easy to tell at a glance which were positive and which were negative. And your suggestion of making the negative number red is a good one.
Variance 1 =IF(D5>0, D5-D4,””).
Variance 2 =IF(D5<0, D5-D4,””).
Always find your posts to be informative. Love how you present the materials. Very easy to follow. Nice work Jon.
I totally agree with making the equation as self-explanatory as possible. It has always been true that the cost of maintaining systems eventually exceeds the cost of development and a major part of this is that the original developers have departed the scene, or it’s been so long that they can’t remember why they did something that particular way. In all the many years I was writing programs, the worst projects I ever had were those that required looking at “old” code because I inevitably found that the external documentation was not useful and the code, itself, rarely had associated comment lines. Needless to say, I was guilty of that, myself.
Personally I would never utilize the fact that Excel regards a blank cell as a zero and since 0=FALSE for TRUE/FALSE tests with any other (numerical!!) value equalling TRUE (so 324,443 is TRUE while “324,443” is going to cause an error) for something like this, for several reasons. One is Excel could decide to modernize and change that. Then what? Well, then a whole awesome lotta what, with what=work and what=oh geez… But a couple other things seem more important, since Excel is NOT big into modernizing. They only do it grudgingly. To whit, all the nice new functions (due to decades of complaints) but still, there is no function that can take text and turn it directly into a formula. (EVALUATE doesn’t count as it’s technically a macro command, and Excel has actually taken noticeable steps over the last five years to screw people using that body of macro commands.)
One reason is theoretical (should be “practical” but in practice most seem to give up and just say “Well, that’d be a nightmare for me, but since I’ll fire you if it crops up, I can rely upon your self-preservation instinct to keep it from happening. Yeah, that’s what I can do…”) and that is “Is this spreadsheet accurate? Does it do what it claims? Or are there bombs lying in wait?” Seems the broader community, by which I mean programmers who’d like a longer work queue so they can get paid higher rates, but also the “boss community” who are very queasy about relying on spreadsheets written by one person with no great supervision, review, or testing before rolling into use. (Seriously, it’s a problem, but also, seriously, check the latest phone app you bought and ask yourself how much of that happened with its programming.) The 90% value of spreadsheets is they can be written in minutes to handle something that just came up. But like the old saying in the US, “Our strength is that ANYONE can grow up to be President. Our weakness is that ANYONE can grow up to be President.”…
Given that, using cleverness just to make a formula “tight” and demonstrate your own… cleverness… just exacerbates the problem. The harder it is for oversight to understand what’s going on, the less likely it is your work gets real oversight. And that’s a problem. Bureacracy is just a mass of individuals milling about, passing work onto others, letting people wait and wait in lines, if done that way. With oversight and teamwork, they no longer subtract from the work their numbers suggest, but rather begin to multipy it instead. Multiply value, not work itself! Another set of eyes reviewing, as a minimum, your work, amplifies the value of your work. It’s not just a case of some boss getting to act like HE wrote your spreadsheet. Could wax on and on with this, but I think today’s point is made: Esoteric tricks tend to destroy oversight and lose its entire value.
A second important reason is a selfish one, but I mention it often on help websites, so why not here? Do you want to do the work you do for however many years it is until the machines or a younger (cheaper, and with a future filled with promise from the employer’s standpoint) worker make you ask the eternal question “Do I stay in New York where I’m familiar with things and know some people, or is it better to be homeless in Miami where at least the winters don’t kill homeless people?”
The harder it is to follow your work, the harder it is to promote you away from it knowing the same spreadsheets will now be used by someone who can’t follow their workflow and will have a dicey time at best, or who will have to re-write all your work so “dicey” need not be part of things. The harder it is for the boss to be confident your replacement can do the work reliably, the harder it is for him to put you in for promotion and raises. There is no such thing as “I can’t fire him ’cause no one can follow his work.” There’s “He’s gotta go and soon, because no one can follow his work.” Miami, by the way. No reason to die just because you’re homeless. And you can always push your cart down to South Beach and watch the pretty, almost naked, people doing pretty people things that you’re not a part of anymore. ‘Cause, you know, a hot (fill in the blank) is a hot (whatever) and fun to look at whether you are dogsh*t to her/him or not. So move to Miami.
From a more daily perspective, I’ve never written a spreadsheet without improving it every time I’ve used it, except that daily things are more on a think about how to improve them every time, but only do it now and then, basis. The more arcane some cleverness was, the “tighter” your wrote something, the harder it is to take a quick understanding of a portion you’re not utterly thrilled with, and then reliably improve it. Reliably, not like, do it and find out three uses later, when the original is gone, that you… goofed… The faster you “grok” things in place, the more time you have for doing the improvement. The more time you have for remembering EVERY little connection into the rest of it, not just almost every one. It is PRECISELY akin to in-line documentation (“commenting”) that programmers can do. (I even sometimes use the addition of bits like ” + N(“This bit also feeds into the such-and-such report on sheet Grenoble but not into any other similar report.” Not for the last few years, but if the value arises, I shall again.)
So while I definitely wouldn’t do =IF(D5,D5-D4,””) or, say, tricks with INDEX like =INDEX(range,,) or even =INDEX(range,0,0), I also don’t do the algebra on (D5-D4)/D4 to end up with 1-D5/D4 simply to make it easier to follow by someone looking at it.
The function LET is quite helpful for this. You can set the formula level Name using the (D5-D4)/D4 formula, but then not “clutter up” the working formula since you are using that descriptive name. I like to layer out the LET Names too: put the basic blocks for a complicated formula on one line in the editor, then pull them together in one or more logical level of thought formulas on the next line (or lines, if really complicated), then into the final Names for the working formula to use in a moment. Then just simply reading down the formula brings quicker understanding from the structuring itself. Of course, if you read up, it’s just as complicated to chunk through as in the one long string of letters and numbers formulas often are. (Another great benefit of LET is you can put the Names that may need their definition elements (like, say, A1:A6000) edited by a user on the very first line so they can quickly and easily spot them all and not have to hunt through your working formula to end up missing one or three.) Things like this are a Godsend for a boss or other oversight. Not to mention the person who will replace you when you go off to your new raise, I mean, new promotion. (Not to mention the value of being able to extend the number of lines the Formula Editor shows. But that new Add-In might make everything else clunky. When it gets to the level that it writes a LET and scopes the Names at cell level, instead of making Named Ranges. (Which are awesome, not dissing Named Ranges, love them, but if a Name scoped for a formula can do the work, why clutter the higher-scoped Name Ranges up with one formula’s tools? Be nice to be able to pick and choose.) (Sigh…) You might now imagine my disappointment to find that the Names in LET’s are not related to Named Ranges… spreadsheets should regard every character in a cell as a granular item (so a text entry of “Hi dude!” would be addressable, natively, by INDEX as an eight character array rather than a single, atomic element that other functions must tear apart and put back together as an object it can address. Then that concept should go upwards. Above sheets would be the spreadsheet. But as one of many, so addressing other spreadsheets would just require a further layer of addressing for finding them, so INDEX could then natively address an array of spreadsheets, and so on. Done so, any function could address anything. Not so now… but in the Category of “Another argument for another day, Alex, I’ll take Seamless Functions for $400.”…)
Now, that all said, there IS something else. It’s a bit different from your “if that makes sense to him” reasoning which I take to mean “he gets it so all’s well and good”… though maybe I misunderstood. We are an amalgamation of hundreds of, if not thousands, strains of humans all of whom developed differences. Homo Naledi seems to have developed the modern foot, 300,000 years ago, not any of the rest of the apes leading to “Mitochondrial Eve” in “our” direct line. And kept it going in their descendents even after their strain became a few dozen other strains and mixed with dozens/hundreds more to eventually drop the fully modern foot into Eve’s descendents long after Eve supposedly “uniquified” her line. I cannot peel potatoes toward me, not with any dexterity or safety. Others can’t do anything BUT peel toward themselves. Oddly enough, I would never take a paring knife and cut into one to remove a bad spot by cutting out, away, from myself desiring instead the “control” of cutting toward me and putting my thumb just past the spot being cut out. Weirdly, that feels more in control and therefore safer. How one sees things in a formula can be very granular as well. To the extent that such a writing “makes exact sense” to your friend, and is natural in his thinking flow, as opposed to the one you and I’d both do being something that is not instinctual, that requires active thought, I say do what comes naturally to you. Not that I think THIS is an example that could ever fit that, but it needs said. Of course, Like a lefty looking to rent a bag of clubs for a round of golf, your promotions may be few. Still, to the extent that it raises your understanding of what YOU’re doing, and your accuracy in building the spreadsheet, do what feels natural.
Ever read one of those formulas in which a person has an IF with a lot of IF NOT chuff for a line and a half in the editor, then finally has the result that happens 99% of its use? Rather than as the initial, “follows the condition”, component? And you hunt for days to find it because all his chuff blends together, endlessly? And beginninglessly? But you talk to the person and he clearly finds it to make utter sense, can’t see the point of what you’re suggesting he change? Like that… he’s handling it, he’s… not wrong…, and it’s natural to him. Maybe don’t let his kid date your kid, but let him write his spreadsheets.
What is the formula if either the BUDGET or the Actual is Blank/ZERO? How wouls you write that formula
=IF( OR( ISBLANK(D4), ISBLANK(D5) ), “”, D5-D4 )
=IF( AND( D40, D50 ), D5-D4, “” )
The former has the plus of being more logically direct, but the latter has the advantage of probably being faster because it substitutes comparisons for the functions (“” vs. ISBLANK) and those, and arithmetic operations, are always faster than functions.
Sorry, in the latter formula, it should be D5 and D4, not D50 and D40. and somehow the comparisons got lost as well. Very bizarre!
=IF( AND( D50, D40 ), D5-D4, “” )
One could even add to the AND checking that D5 and D4 do not equal “” as well. So an value of actual “” somehow entered in them would not throw the logic off.
D5 does not equal 0
D4 does not equal 0
I see what happened. Not why, but what: the site software stripped the greater than-less than text characters so contracting D5…0 to D50 and similarly for D40. (Let’s see if it does in those ending characters just typed, as well, or if it is limited to formula-looking text.)