Substract last cell in row from first cell with number

Question

I have the following Excel sheet

In column J i need the final difference between the first cell in the row and the last cell (with a number).

Numbers can appear from column C until column I. Numbers do not always start in column C and do not always end in column I, but there are never empty cells in between.

Basically i need to subtract the value in the first cell with a number from the last cell with a number. The last value in the range from C-I minus the first value in that range with the result being displayed in J. I filled in column J manually for now, however I would like to do it with formula.

Answer 1

If the numbers are always ordered from smallest to largest, you could simply do this:

 =MAX(C2:I2)-MIN(C2:I2)

---

If not, things become a bit more difficult. Here's another solution that should work for non-ordered entries:

First, add an empty column to the right of Totaal.

Second, add seven columns with the following contents:

=IF(ISBLANK(C2),M2,C2)
=IF(ISBLANK(D2),N2,D2)
...

Third, add another empty column.

Fourth, add seven columns with the following contents:

=IF(ISBLANK(C2),S2,C2)
=IF(ISBLANK(D2),T2,D2)
...

Totaal can then be calculated with

=Z2-L2