Formula Question

[BobH]

I have an Excel spreadsheet that uses the following formula in a column:

=IF($J2<>"",$J2*($A$31-$A$2),"")

As you can see there is an absolute reference, $A$31. Instead of this absolute reference, is it possible to write the formula such that the value in the cell in column A of the last row having a value is used?

[HansV]

Try

=IF($J2<>"",$J2*(LOOKUP(1E+307,$A:$A)-$A$2),"")

[snb]

=IFERROR($J2^2*(index(A:A;counta(A:A))-$A$2)/$J2,"")

[BobH]

Hans, what does the expression (1E+307,$A:$A) mean?

My guess is that 1E+307 is a number representing the maximum number of rows and the range expression limits it to column A. Is that anywhere close?

[HansV]

1E+307 means a 1 followed by 307 zeros. Excel uses so-called scientific notation for such numbers. It is close to the largest number one can enter in an Excel cell (the largest is actually 9.99999999999999E+307).

LOOKUP(1E+307,$A:$A) tries to look up 1E+307 in column A. It is not found, of course, and in that situation, LOOKUP returns the last number it encountered in column A.

[BobH]

Thank you. My guess was very close. I recognized the scientific notation and incorrectly conflated the number with the maximum rows Excel can have.

Thanks again.