Here is a situation you might face that we have seen some clients run into. It makes for a good brain teaser. Thanks to Roger Orlady for the topic.
The exercise and the result are in the YTD Test file.
You may run into a case where the download you get is just YTD data. To get the monthly data, you have to calculate it yourself.
You might think, no big deal. Just subtract the last month YTD number from the current month YTD number. Something like:
=P5-O5 where P5 is the February YTD data and O5 is the January data.
That would be easy.
But let's make it a little harder. Instead of having to revise the formula each month by copying last month's formula over to the current month, we want to set up the formulas for all 12 months ahead of time.
In that case, the above formula would not work. For example, suppose we were at the end of February. What would happen is that the March formula would wipe out the any YTD numbers for the first two months, so you might have values like:
January 188
February 155
March (343)
So you have to do a test to see if the next month value is zero.
No problem, you say, easy work for an IF statement, something like:
=IF(Q5=0,0,Q5-P5)
That would work in most cases. Above we are calculating the March balance. Since the balance is zero because there is no entry yet for March, then this keeps March blank.
However, we are not off the hook yet. What if the balance was positive and then fell back to zero? In this case, the formula would not work, for example:
| Result | YTD | |
| January | 188 | 188 |
| February | 155 | 343 |
| March | 0 | 0 |
March would calculate as 0, instead of the desired (343).
Another case where the formula might not work is when you have zero two months in a row after having a negative or positive number
So it needs another test. Let's also say a test for being above zero won't count either, since numbers can go negative.
Here is our solution:
The April formula for example is =IF(COUNT(R3:$Z3)=0,0,R3-Q3)
Let's break it down:
COUNT(R3:$Z3)=0 We check to see if the April YTD cells onward are blank
0 If blank, then we enter zero.
R3-Q3 If not blank, then we do the monthly calculation for April
As you can see in this example, even though the YTD number in April reverted back to zero, the formula still properly calculated the April reduction, rather than putting a value of zero in for April.
Keep this reference handy in case you ever have to do calculations with YTD numbers.