# Calculation based on first and last value in row

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.

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?

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
``````
• 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 Commented 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) Commented Oct 2, 2017 at 16:08

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))
``````