I have a row of values in a range and need to subtract the value in the last cell in the row from the value of the first cell of the row. The challenge is that the last cell location is unknown, so the formula needs to be general.
1 Answer
This formula finds the last number in the range A2:Z2, adjust as required:
=LOOKUP(9.99E+307,A2:Z2)
9.99E+307
is [almost] the largest number that excel can handle, when the lookup value is so large that it won't be found in the range then that results in the formula finding the last number in that range