0
votes

The current formula in my spreadsheet is the following:

=((Z$39-Z$38)/Z$38*100)

The Z column contains numerical data.

Each time I update the spreadsheet I add a new row to column Z and have to update the above formula with the new cell. For example, next month I'll have to update it to:

=((Z$40-Z$39)/Z$39*100)

How do I change this formula to automatically find the last cell in column Z AND the second from last cell in Google Spreadsheets? Thanks!

1

1 Answers

1
votes

You can use INDEX/MATCH:

=((INDEX(Z:Z,MATCH(1E+99,Z:Z))-INDEX(Z:Z,MATCH(1E+99,Z:Z)-1))/INDEX(Z:Z,MATCH(1E+99,Z:Z)-1)*100)

INDEX(Z:Z,MATCH(1E+99,Z:Z)) will find the last cell in Column Z with a number.

By putting a -1 after the MATCH it will pull the value from the second to last.

enter image description here