Hi,
I want to record the difference between starting value and current value in another cell. The values reside in a single column and the current value will be updated daily. Is there a function to determine the last populated cell in a column?
Or, am I thinking about this wrong? - complete possibility. 
May be =OFFSET(A1;MATCH(1;ISBLANK(A:A);0)-2;0;1;1) is what you are looking for, if your data are on column A (works only if there are no empty row inbetween; i.e. contiguous range of data).
Pasted into an unrelated cell and adjusted to reflect proper column reference this seems to return the value in the cell on the previous row.
For example, for column A, Row 5 this seems to return the value of A4.
Not quite what I was looking for. Unless I got something wrong.
Thank you for the attempt.
Then you did not adapt correctly - in function OFFSET first argument A1 has to be the first cell of the data row. In other words: If data, were you look for the last value start in e.g. C7 then change A1 → C7 (even better $C$7).
I tried again, function used: =OFFSET(AR4,MATCH(1,ISBLANK(AR:AR),0)-2,0,1,1)
That function resides in cell AV3
Value returned resides in cell AR4
Value expected to be returned resides in cell AR24, but this will increase by 1 row daily.
Could it be my placement of the function that is causing it to not work properly?
In any case, thank you for your help. The solution offered below works for my needs.
Returns the value of the last non-blank cell in the cell range B2:B999
=LOOKUP(2;1/NOT(ISBLANK(B2:B999));B2:B999)Ok, so it looks like this COULD work. It does cause some problems. Currently the value column is formatted as “currency” and, as such, zero values populate unpopulated cells. As a zero value does actually cause a “population” of the cell (because zero is a valid value) this breaks the function from working as intended. This means I would have to withhold formatting the cells in that column until data entry. An inconvenience, but only an inconvenience. I think I could make this work.
So, final solution using this function…
Beginning value populates AP4
Current value populates AP23
AR1 is labeled “Total Gain”
AR3 contains function AS3-AP4
AS3 contains function =LOOKUP(2,1/NOT(ISBLANK(AP4:AP999)),AP4:AP999)
Column AS will be hidden after testing.
Thank you very much for your help erAck. I will test when I enter values tomorrow morning and, I expect, come back and say thank you again.
Works perfectly, Thank you again.
Cell formatting doesn’t produce content in the cell. However, if you have 0 value as cell content or formulas that produce 0 results and you want to exclude those then
=LOOKUP(2;1/IFERROR(AP4:AP999<>0;1);AP4:AP999)
The IFERROR() is necessary because the comparison B2:B99<>0 propagates error values, like if the last value is =1/0 with a #DIV/0! error result, without IFERROR() it would be ignored looking up the 2 in the array and the previous non-error value returned.