Angel works in Finance. She often asks me questions on calculations in a table. Today I got this question: How to calculate Year over Year (YoY) change ratios for both quarterly and yearly sums, in a single sheet?
Here is the solution we got. First there are two parts for the YoY calculation.
SUM([Sales])/LOOKUP(SUM([Sales]), -4) -1
For the yearly YoY, the above formula doesn't work. We need a slightly different one like:
SUM([Sales])/LOOKUP(SUM([Sales]), -1) -1
To combine them in a single one, we got this:
Note that CountD()=1 is equivalent of Max()=Min() when we deal with grand total/subtotal calculations.
The above formula works well logically. Here we have a few variants that look simpler.
Download the demo workbook here.
Add a comment