I have a table, which you can find below, and I was hoping to calculate the WoW (Week over Week) percentage of the last two rows of the table. The calculation I use for percent change is =(E5/E4)-1
The thing is, this table updates every Friday, adding a new week, so instead of manually changing the formula (next week would be =(E6/E5)-1), I was hoping there would be a more automotive approach (whether it’s VBA or formulas).
Essentially, I am trying to pull the Page Views value from the last row of the table and the second to the last row of the table no matter how many records I add.
| Week End Date | Page Views |
|---|---|
| 10/22/2021 | 35,604 |
| 10/29/2021 | 22,202 |
| 11/5/2021 | 49,921 |
| 11/12/2021 | 40,202 |
>Solution :
INDEX/MATCH will return the last and second to last:
=(INDEX(E:E,MATCH(1E+99,E:E))/INDEX(E:E,MATCH(1E+99,E:E)-1))-1
Basically:
INDEX(E:E,MATCH(1E+99,E:E))
Will return the last number in the column and:
INDEX(E:E,MATCH(1E+99,E:E)-1)
Will return the value one row above the last number in the column
