refer to data in the 'last cell' of a column
Posted by martin bell on January 02, 2002 3:10 AM
I am collecting stock data each day where the latest stock price is always added to the next cell down in in the same column.
In another worksheet, I have a cell whose value should equal the latest stock price. How do I construct a formula such as: =the stock pricethat is in the last/bottommost filled cell in column A"
Hope someone can help.
Posted by Aladin Akyurek on January 02, 2002 3:22 AM
Martin --
It's:
=INDEX(Sheet1!A:A,MATCH(9.99999999999999E+307,Sheet1!A:A))
Aladin
==============
Posted by Martin Bell on January 02, 2002 5:13 AM
Thanks Aladin - it works great and I'm very grateful. Only thing is, its created another problem which I can only explain by example:
My stock data is in column A (say, the range A1:A20). In column B, adjacent to each closing price in column B I have a formula (which calulates the average of the previous 5 stock closing prices in column A).
I have pre-pasted the formula in column B (down to B30) and need to do the same sort of formula as in my original question.
Of course the result I get from using the same formula relates to the cell B30 (the incomplete pre-pasted formula).
How can I change this formula to target the cell in column B that is directly next to my last stock price in column A (i.e. B20)?
Posted by Aladin Akyurek on January 02, 2002 5:53 AM
Martin --
Not sure I understand the problem.
I'd think that
=INDEX(Sheet1!B:B,MATCH(9.99999999999999E+307,Sheet1!B:B))
or
=INDEX(Sheet1!B:B,MATCH(9.99999999999999E+307,Sheet1!A:A)) [ which gets you the average that is in the same row as the latest stock price ]
should give the last computed average in B. I guess that value includes the latest stock price in A.
If this is not what you meant to have, please post the formula that you se in B.
Aladin
========
