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?

Improve this question

2 Answers 2

Reset to default
3

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

Improve this answer
2
  • 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, 2017 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, 2017 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))
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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