Last value in column
Posted by Jack on January 03, 2002 7:05 PM
Trying to find formula that will look at the LAST value in a column, not the MAXimum value. Any help would be very much appreciated.
Posted by Scott on January 03, 2002 7:30 PM
you can try this:
This is assuming that the column does not have any blanks in it, and that the data is in column A and begins in A1 (adjust as needed).
Posted by Aladin Akyurek on January 04, 2002 1:08 AM
If col A is of numeric type,
will give you the row number of the last cell that contains a numeric value;
will give you the last numeric value.
If col A is of mixed data type (that is, A houses numbers as well as text) and you want the last value whether its data type is numeric or text, then add the following UDF to your workbook:
Function Used(r As Range) As Range
' Harlan Grove
' Sun 25 Nov 01
Dim q As Range
Set q = r.Parent.UsedRange.Cells(r.Parent.UsedRange.Cells.Count)
Set Used = Intersect(r, r.Parent.Range(r.Parent.Cells(1, 1), q))
Note 1. The above formulas are not affected by blanks.
Note 2. They will not return a formula generated blank that is the last value.
Posted by Mike on January 04, 2002 8:53 AM
The way I did this was as follows:
This assumes that the column is full from D1, and has no blank cells.
The above will return D24 is D24 is the last cell that contains a value. Note that it will NOT count blank cells, and thus the number will be off by the number of blank cells in the column.
If you have 3 blank cells, and you know you will always have 3 blank cells, then make the formula
Posted by Aladin Akyurek on January 04, 2002 9:49 AM
DCOUNTA as well as COUNTA always suffer from the potential existence of blanks in the range of interest. Moreover, COUNTA is inefficient compared to MATCH (as I use in my reply) and it's a volatile function that leads to recals.