4

I have a Google Sheets spreadsheet which has intermittent data. I have a column where I'd like to calculate the difference between the first value in each row, and the last value in that row. The first and last columns aren't always the same.

Data example

Here you can see that Row 2 has a value in B2 and E2 so the calculation in A is =E2/B2-1
Row 3 has a value only in C3 and D3, so the calculation is =D3/C3-1

How can I make the formula in A automatically detect and use the first value and the last value for the calculation?

2

For the first value:

=+filter(B2:E2,B2:E2<>"")

For the last value:

=index(B2:E2,match(1E100,B2:E2))

So together for your requirement:

=index(B2:E2,match(1E+100,B2:E2))/(+filter(B2:E2,B2:E2<>""))-1

FILTER
INDEX
MATCH

  • OK that's good, thanks - As the number of rows grows as new data comes in, I can just put B2:Z2 or similar to incorporate any new values, or is there a better way to do that? I tried 2:2 but of course got a Circular Reference error due to it hitting column A – Midavalo Oct 2 '17 at 16:06
  • Sorry I mean more columns per row as new data comes in (e.g. the next data input would be column F) – Midavalo Oct 2 '17 at 16:08
1

I needed to get the last entry to an increasing data set, so I used COUNT to identify the number of entries and then INDEX to pick the last one.

I found that COUNT will ignore blank cells below the last records it works well for my needs as the data grows - but there should not be gaps in the sequence.

=INDEX(D3:D999,count(D3:D999))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.