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?
Formula Question
-
- UraniumLounger
- Posts: 9300
- Joined: 13 Feb 2010, 01:27
- Location: Deep in the Heart of Texas
Formula Question
I have an Excel spreadsheet that uses the following formula in a column:
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?
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?
Bob's yer Uncle
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs
(1/2)(1+√5) |
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
-
- 4StarLounger
- Posts: 589
- Joined: 14 Nov 2012, 16:06
Re: Formula Question
=IFERROR($J2^2*(index(A:A;counta(A:A))-$A$2)/$J2,"")
-
- UraniumLounger
- Posts: 9300
- Joined: 13 Feb 2010, 01:27
- Location: Deep in the Heart of Texas
Re: Formula Question
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?
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?
Bob's yer Uncle
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs
(1/2)(1+√5) |
-
- Administrator
- Posts: 78647
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Formula Question
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.
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.
Best wishes,
Hans
Hans
-
- UraniumLounger
- Posts: 9300
- Joined: 13 Feb 2010, 01:27
- Location: Deep in the Heart of Texas
Re: Formula Question
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.
Thanks again.
Bob's yer Uncle
Dell Intel Core i5 Laptop, 3570K,1.60 GHz, 8 GB RAM, Windows 11 64-bit, LibreOffice,and other bits and bobs
(1/2)(1+√5) |