need a function to report last value in a column

[Brent]

I need a function that will report the value of the last
filled cell in a range of cells or column.

The purpose is to show a "final balance" at the bottom of
a page that contains a running total of debits and
credits.

It seems ridiculously simple but I cannnot seem to find a
solution. I'm using Excel 97.

Brent

 

[Norman Harker]

Hi Brent!

Whilst finding the last cell in a column might be one way,

Opening Balance + Sum of Credits - Sum of Debits = Final Balance

If debits are given negative signs then it's add the Sum of Debits.



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Thursday: Argentina (Day of the Child), Bahamas
(Independence), Belarus (Independence), Belgium (Ommegang Pageant
Thursday), Danish West Indies (Emancipation)
Celebrations Thursday: Festival of Cerridwen: Celticism
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Biff]

Hi Brent,

I use this formula in my check register:

=INDIRECT(ADDRESS(MAX(IF(H1:H65535="",0,1)*ROW
(H1:H65535)),COLUMN(H:H)))

Entered as an array. Change column as needed.

Biff

 

[Aladin Akyurek]

Also...

=LOOKUP(9.99999999999999E+307,E:E)

will fetch the last value from E.

 

[John Tjia]

WOW! Like Biff, I am also fascinated by this formula and would like to
know how it works.

 

[Biff]

Hi John,

I think I have it figured out after some tinkering.
9.99999999999999E+307 appears to be overkill. This is used
to ensure a value is found. The formula as written will
return the last value in the column if it's smaller than
9.99999999999999E+307. That's a pretty safe bet! If the
last value was 50, you could then write the formula as:

=LOOKUP(51,E:E)

It's the same as using E:E as the range. You don't know
exactly where the range ends so you use the entire column.
So if you don't know the exact value, you use an arbitrary
value that you know to be greater than the value you're
looking for. And 9.99999999999999E+307 definitely fits the
bill!

I don't fully understand the 'why' but I think I
know 'how'.

Biff

 

[Norman Harker]

Hi Biff!

Always good to ask, "Why?" as it's only understanding "Why?" that
brings easily recoverable memory. Use of 9.99999999999999E+307 is not
exactly "overkill". It's recognition that this is the largest number
that can be entered into a cell.

I look at the logic of LOOKUP(9.99999999999999E+307,E:E) by comparing
with the VLOOKUP equivalent.

=VLOOKUP(9.99999999999999E+307,E:E,1,TRUE)

That would also return the last value in column E. Note the use of
TRUE as the final argument and this VLOOKUP variant would return the
last value in the VLOOKUP table E:E as long as the value looked up was
greater than the values in the column. 9.99999999999999E+307 happens
to be the largest number that you can type in a cell

So I suppose I could say that LOOKUP works because it has the VLOOKUP
default of TRUE when looking for a match.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Friday: Guam (Independence Day); Faroe Islands
(Varmakeldustevna); Haiti (Day of Agwe); Latvia (Jewish Genocide
Commemoration); Lesotho (Family Day); Marshall Islands (Fisherman's
Day); Norway (Queen Sonja's Birthday); Philippines (United States
Friendship Day); Puerto Rico (For US Independence Day); Switzerland
(Aarauer Maienzug); Rwanda (Independence Day); Tonga (King's Day);
United States (Independence Day); Virgin Islands (US Independence
Day); Yugoslavia (Freedom Fighter's Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Biff]

Hi Norman,

Thanks for the info...but...I still have to ask why use
9.99999999999999E+307 as the lookup_value if a much
smaller number will do the same thing? It seems to me that
it's used just to cover every possibility. I would much
rather use:

=LOOKUP(9.99999999999999E+307,E:E)

than

=INDIRECT(ADDRESS(MAX(IF(H1:H65535="",0,1)*ROW
(H1:H65535)),COLUMN(H:H)))

and by the same token, I would much rather use:

=LOOKUP(10000,E:E)

Just asking!
Biff

 

[Norman Harker]

Hi Biff!

I would argue in favour of:

=LOOKUP(9.99999999999999E+307,E:E)

It's generic. You can't possibly have a number larger than that
because it is the limit of the Excel specification for a value in a
cell. You imposition of 10000 might be OK but only in a situation
where 10000 cannot be exceeded.

Easier, perhaps, to understand but requiring another function call
would be:

=LOOKUP(MAX(E:E)+1,E:E)

It would be generic and designed to use the LOOKUP default of
returning the last value in a column if an exact match is not found.
Easier to understand? At least, it is obvious that the amount looked
up can't exist rather than leaving it to the Excel Specification
"saying" it can't exist.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Friday: Guam (Independence Day); Faroe Islands
(Varmakeldustevna); Haiti (Day of Agwe); Latvia (Jewish Genocide
Commemoration); Lesotho (Family Day); Marshall Islands (Fisherman's
Day); Norway (Queen Sonja's Birthday); Philippines (United States
Friendship Day); Puerto Rico (For US Independence Day); Switzerland
(Aarauer Maienzug); Rwanda (Independence Day); Tonga (King's Day);
United States (Independence Day); Virgin Islands (US Independence
Day); Yugoslavia (Freedom Fighter's Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[John Tjia]

Aladin, the originator of the intriguing genie-like formula, must be
smiling next to his magic lamp!

 

[Norman Harker]

Hi John!

You bet!

Elegant in its simplicity like a well executed Japanese flower
arrangement. Some of these solutions do have beauty all of their own.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Friday: Guam (Independence Day); Faroe Islands
(Varmakeldustevna); Haiti (Day of Agwe); Latvia (Jewish Genocide
Commemoration); Lesotho (Family Day); Marshall Islands (Fisherman's
Day); Norway (Queen Sonja's Birthday); Philippines (United States
Friendship Day); Puerto Rico (For US Independence Day); Switzerland
(Aarauer Maienzug); Rwanda (Independence Day); Tonga (King's Day);
United States (Independence Day); Virgin Islands (US Independence
Day); Yugoslavia (Freedom Fighter's Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Biff]

Hi Guys,

Norman, great description!

Speaking of beauty and simplicity in a solution, the most
impressive example that I've seen was offered by J.E.
McGimpsey:

http://groups.google.com/groups?hl=en&lr=&ie=UTF-
8&threadm=3EA1544A.3CEC7C7A%
40optonline.net&rnum=2&prev=/groups%3Fas_drrb%3Db%
26scoring%3Dd%26as_mind%3D4%26as_minm%3D7%26as_miny%3D2001%
26as_maxd%3D4%26as_maxm%3D7%26as_maxy%3D2003%26as_q%
3DDeciphering%2520formula%26num%3D100%26as_ugroup%
3D*Excel*%26as_usubject%3D%26as_uauthors%3D

I know...but for some reason I couldn't connect to
Makeashorterlink.com

Norman - also in this thread is a subtle mention of the
subject of my post for books. I will be sending you more
info.

Biff

-----Original Message-----
Hi John!

You bet!

Elegant in its simplicity like a well executed Japanese flower
arrangement. Some of these solutions do have beauty all of their own.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Friday: Guam (Independence Day); Faroe Islands
(Varmakeldustevna); Haiti (Day of Agwe); Latvia (Jewish Genocide
Commemoration); Lesotho (Family Day); Marshall Islands (Fisherman's
Day); Norway (Queen Sonja's Birthday); Philippines (United States
Friendship Day); Puerto Rico (For US Independence Day); Switzerland
(Aarauer Maienzug); Rwanda (Independence Day); Tonga (King's Day);
United States (Independence Day); Virgin Islands (US Independence
Day); Yugoslavia (Freedom Fighter's Day)
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Tushar Mehta]

Aladin and I have had this discussion in another forum, so what I have
to write should not be news to him.

While he has a seemingly elegant solution to find the last value in a
column, it relies on the fact that MS -- yet again? -- has an XL
function that doesn't match the documentation. If one looks up the
LOOKUP function in XL Help, one finds

--- begin quote ---
Important The values in lookup_vector must be placed in ascending
order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP
may not give the correct value. Uppercase and lowercase text are
equivalent.
--- end quote ---

Yet, if the lookup_vector is not in ascending order, LOOKUP -- and all
other look up type functions -- happily return incorrect results (XL:
HLOOKUP, VLOOKUP, LOOKUP Return Incorrect Values --
http://support.microsoft.com/default.aspx?scid=kb;en-us;181201).
Aladin has been leveraging this behavior to his advantage. How long MS
can continue with such gross indifference to GIGO in the face of its
own push for "trustworthy computing" remains to be seen.

Also, the solution depends on knowing the nature of the last entry --
numeric, text, or error code. If the last entry is text, the formula
doesn't work. One needs to use REPT("z",{absurdly large number}) in
place of the 9.999 value.

A lot more reliable resolution would be to use a one-line UDF

Public Function LastValInCol(x As Range)
LastValInCol = x.Parent.Cells(Rows.Count, x.Column).End(xlUp).Value
End Function

Use it as =LastValInCol(E:E)

 

[Ron Rosenfeld]

Also, the solution depends on knowing the nature of the last entry --
numeric, text, or error code. If the last entry is text, the formula
doesn't work. One needs to use REPT("z",{absurdly large number}) in
place of the 9.999 value.

A lot more reliable resolution would be to use a one-line UDF

Public Function LastValInCol(x As Range)
LastValInCol = x.Parent.Cells(Rows.Count, x.Column).End(xlUp).Value
End Function

For a formula solution, without the disadvantages of the previously posted, I
believe the array-entered formula:

=OFFSET(A1,MAX(NOT(ISBLANK(A1:A100))*ROW(A1:A100))-1,0)

will also work regardless of the nature of the last entry.

It will also work even if there are blanks within the array.

For a function that will stop at the cell before the first blank cell in the
array, array-enter:

=INDEX(A1:A100,MATCH(TRUE,ISBLANK(A1:A100),0)-1,1)


--ron

 

[Peo Sjoblom]

Can be shortened albeit not as educative

=OFFSET(A1,MAX((A1:A100<>"")*ROW(A1:A100))-1,)

=INDEX(A1:A100,MATCH(TRUE,A1:A100="",0)-1,)

--

Regards,

Peo Sjoblom

 

[Harlan Grove]

Can be shortened albeit not as educative

=OFFSET(A1,MAX((A1:A100<>"")*ROW(A1:A100))-1,)

...

Returns error values if any error values in A1:A100.

 

[Tushar Mehta]

Four comments.

First, the array formula doesn't handle the entire column, but, by
design, must be at least 1 less than all rows.

Second, I find that over the past couple of years, influenced by some
very smart minds, my use of array formulas increased. But, now, it's
reversing. Largely for reasons of understanding, documentation, and
maintainability.

Third, a downside to a UDF -- unless it is in an add-in -- and that is
it may trigger the 'this workbook has a possible virus' warning. And,
yes, it can be slower than XL-native formula/functions.

Fourth, as far as speed goes, the results can be surprising. Using
the code below, the array formula took 41 seconds, and the UDF between
zero and 1 second.

Sub TimeIt()
Dim i As Long, StartTime As Date, EndTime As Date, _
RandRow As Long
StartTime = Now
RandRow = 1 + Int(Rnd() * 65536)
Application.ScreenUpdating = False
For i = 1 To 500
Cells(RandRow, 1).ClearContents
RandRow = 1 + Int(Rnd() * 65536)
Cells(RandRow, 1) = Rnd()
Next i
EndTime = Now
MsgBox Format(EndTime - StartTime, "hh:mm:ss.s")
End Sub

The array formula I used was
=OFFSET(A1,MAX(NOT(ISBLANK(A1:A65535))*ROW(A1:A65535))-1,0,1,1)

--
Regards,

Tushar Mehta (www.tushar-mehta.com) MS MVP -- Excel

...
..

While he has a seemingly elegant solution to find the last value in a
column, it relies on the fact that MS -- yet again? -- has an XL
function that doesn't match the documentation. . . . ..
. . . Yet, if the lookup_vector is not in ascending order, LOOKUP -- and all
other look up type functions -- happily return incorrect results (XL:
HLOOKUP, VLOOKUP, LOOKUP Return Incorrect Values --
http://support.microsoft.com/default.aspx?scid=kb;en-us;181201).
Aladin has been leveraging this behavior to his advantage. How long MS
can continue with such gross indifference to GIGO in the face of its
own push for "trustworthy computing" remains to be seen.

Begging the question whether Microsoft ever has or ever will match its
statements with its actions. It's done pretty well up to now, why should it
start doing so? [Tangential note: I'm not going to believe they know what
they're doing with regard to security until BY DEFAULT any and all Windows
Administrator folders are stored in different top level directories than all
other user folders and BY DEFAULT IE and OE quick install with settings that
don't make them ideal virus propogation conduits.]

Also, the solution depends on knowing the nature of the last entry --
numeric, text, or error code. If the last entry is text, the formula
doesn't work. One needs to use REPT("z",{absurdly large number}) in
place of the 9.999 value.

Um, REPT(CHAR(255),255) would be a better choice. So,

MAX(MATCH(9.99999999999999E300,A:A),MATCH(REPT(CHAR(255),255),A:A))

but this doesn't handle error values as bottommost values.

A lot more reliable resolution would be to use a one-line UDF

Public Function LastValInCol(x As Range)
LastValInCol = x.Parent.Cells(Rows.Count, x.Column).End(xlUp).Value
End Function

..

This works, but the point to Aladin's use of MATCH is that it takes advanatage
of binary search, so is potetially much faster than linear search methods. Using
a UDF eliminates much of the potential execution time advantage. Indeed, I
wouldn't want to bet that the .End(.) properties weren't implemented using
loops, stepping through ranges cell by cell.

So, given the UDF call overhead and the potentially linear nature of .End(.),
I'd guess the worksheet array formula

=MAX(IF(ISBLANK(A1),0,1),IF(ISBLANK(A2:A65536),0,ROW(A2:A65536))

is faster than a UDF.

--
1. Don't attach files to postings in this newsgroup.
2. Snip unnecessary text from quoted text. Indiscriminate quoting is wasteful.
3. Excel 97 & later provides 65,536 rows & 256 columns per worksheet. There are
no add-ins or patches that increase them. Need more? Use something else.

 

[Harlan Grove]

...
...

First, the array formula doesn't handle the entire column, but, by
design, must be at least 1 less than all rows.

Complements of Microsoft. As I've pointed out before, Excel can happily handle
2-by-65535 arrays, so the KB article saying that arrays with 65536 entries are
too large is just another classic piece of Microsoft disinformation. They almost
certainly use an unsigned short integer as an array iterator, which tops out at
65535, so that's why they can't handle a whole column.

[Out of curiousity, if anyone still has Excel 5/7/95, do those versions handle
entire columns?]

Second, I find that over the past couple of years, influenced by some
very smart minds, my use of array formulas increased. But, now, it's
reversing. Largely for reasons of understanding, documentation, and
maintainability.

...

Documentation shouldn't be an issue - if more's needed, more should be provided.

As for understandability and maintainability, it's often a trade-off between one
complex array formula in one range vs many formulas in many ranges each of which
may be easier to understand in isolation, but the interrelation may be no easier
to understand than the single array formula. Still, there's a general rule that
always applies: if the same results may be achieved doing something different
ways, do it the simplest way.

Understandability and maintainability are very likely candidates for the reasons
Excel's stats functions are so horrible. Nice, simple iterative procedures may
be easier to understand, but they can become pure crap when pushed beyond the
simplistic testing that seems to have been all Microsoft QA performed. Point:
there are times when understandability must take second place to raw
capacity/performance. As for maintainability, ya gotta hire people who know what
they're doing.

No argument in re 3rd and 4th points.

 

[Norman Harker]

Hi Tushar!

Thanks! That at least explains why I wasn't able to get much help from
the Help files in understanding why Aladin's solution worked.

Your later post was also interesting in comparing timing of array
solution with a simple UDF. The trouble as usual is those darned
security settings disabling the UDF.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
Public Holidays Wednesday: Argentina (Independence Day); Brazil (Sao
Paulo State Civil Holiday); Isle of Man (Senior Race Day); Morocco
(King Hassan II’s Birthday); Palau (Constitution Day).
Celebration: Baha’i (Martyrdom of the Bab).
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Harlan Grove]

A strong case for putting functional code in an add-in, rather than
opening the associated workbook(s).

...

Meaning XLA add-ins never trigger the macro virus warning? That wouldn't be
good.