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.

CodePudding user response：

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

CodePudding user response：

You could try:

=LET(X,SORT(A:B,1,-1),(INDEX(X,2,2)/INDEX(X,3,2))-1)

Or:

=LOOKUP(MAX(A:A),A:A,B:B)/LOOKUP(MAX(A:A)-7,A:A,B:B)-1