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

``````=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 – Midavalo Oct 2 '17 at 16:06
``````=INDEX(D3:D999,count(D3:D999))