Variable Last cell in a calculation/formula

[kcompton]

Hello - I calculate the difference between the last cell in a column and the first cell in a column.
The first cell in the column is an absolute value ($A$1), but the last cell in the column changes daily and the last cell changes daily.
For example, today the calculation is A25-$A$1, but tomorrow the calculation is A26-$A$1.
Is there a way to create a formula that will recognize the changing cell (the last cell is always the next row in the column with a value).
Thank you in advance.

 

[Tetra201]

Try

=LOOKUP(2^1023,$A:$A)-$A$1

 

[kcompton]

Thank you -
If I place the formula in the column next to the column being calculated, it works.
However, if I place the formula in a cell in the column being calculate, I get a circular reference warning.
Is that because $A:$A part of the formula is looking at all the cells in the entire A column and placing the formula in Column A creates a circular reference?
What does the 2^1023 refer to? I thought in excel the caret symbol was an exponent math function - does it refer to the last number/cell available in a column in excel?
Sorry if my understanding is way off.
Does the formula need to be in the column next to the column being calculated?
Thanks again

 

[Tetra201]

- Yes, the suggested formula looks for the last numerical value in the entire column A, so placing the formula in column A would create a circular reference.

- No, you don't have to place the formula in another column. You can limit the range to, say, A1:A40 (i.e., =LOOKUP(2^1023,$A$1:$A$40)-$A$1) and have the formula in cell A41.

- The 2^1023 is just a very big number. You can also find something like 9.99E+307 being used. Please read posts by Aladin Akyurek on the LOOKUP construct and the BigNum.

 

[kcompton]

Thank you! and thank you for referring me to the posts by Aladin Akyurek on the LOOKUP construct and the BigNum.