Resolved: Question about the IF Function for Additional Features
Hello,
I have watched this video a couple times, but I still don't quite get this statement:
=IF(AND(C2>=(EOMONTH(dashboard!$B$5, -13) +1), C2<EOMONTH(dashboard!$B$5, -1)), "LTM", "")
Could you explain further the reason that we should add +1 and -1 at the end? Why would it go back to December 2018 if you don't add it?
Hi Lifang,
thanks for reaching out! This is probably one of the trickiest parts of the Excel Dashboard case study. Because we're going back 13 months instead of 12, in case the current month isn't over yet, we also need to add 1 month at the end of the first check. Similarly, in case the current month we're in isn't over we go back one month with -1 during the second check. I suggest you play around with the formula in Excel and see how altering it will affect the results for the filter. And as a side note, there might be a better, more efficient way to create this filter, so if you have an idea, feel free to share with us in the Q & A section.
Best,
365 Eli
Hi,
I am using the below formula:
=IF(AND(C2 >= EDATE(Dashboard!$B$5,-12), C2 < Dashboard!$B$5),"LTM","")
The logic here is that, if the row date is larger than or equal to the reference date minus 12 month, and it is less than the reference date, then I put "YTD". I have tried it with couple of scenarios and compared it to your method and I can see no difference.
What do you think?
Thanks