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.
Video Tutorial Watch on YouTube & Subscribe to our ChannelDownloads IF Statement Logic – Greater Than Zero-IMAGES.xlsxDownload 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:
ConclusionEven 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.
Link to post: IF Statement Logic Comparison