Find last 'non-blank' cell??!!

[slym34]

hey all.. I have a list of #'s starting from 1, and the next cell down adds 1 to that.. based on some other info, it will either add 1 or come out blank.. i want to be able to have cell A1 point out which is the last cell that has a number and is not blank.. does this make sense???

Here is the formula in this column..
In this sheet cell L284 has the same formula in it as cell L283, however because R283 is blank it also comes out blank...

Here is a copy of the sheet.. I want cell A1 to equal 282 in this example...

payment calcs.xls
	K	L	M	N	O	P	Q	R
279		278	6,171.47	36.00	1,330.60			4,876.87
280		279	4,876.87	28.45	1,330.60			3,574.71
281		280	3,574.71	20.85	1,330.60			2,264.96
282		281	2,264.96	13.21	1,330.60			947.56
283		282	947.56	5.53	953.09
284
285
286
calcs

 

[Aladin Akyurek]

Ooooh - I'm telling tushar!

I'm afraid you are not familiar with the history of this type of formulas. The link you quoted does not tell that either.

 

[Gates Is Antichrist]

In all seriousness and respect I consider you to be an expert and authority on this and many matters, but in the article tushar seems to indicate friendly resistance to that approach. In the cited newsgroup post he goes into it even more emphatically (but respectfully calls it "elegant" ). But it sounds like there's more to the story.

I've spent some time conversing directly with Chip and Charles Williams on the topic of VLOOKUP(...TRUE) and ultimately none of us were 100% sure about it. I've relied on its, shall I say, quirks since version 4, using it on unsorted data. But I'm willing to read and test more to correct my unfamiliarity, if you want to suggest anything.

 

[Aladin Akyurek]

In all seriousness and respect I consider you to be an expert and authority on this and many matters, but in the article tushar seems to indicate friendly resistance to that approach. In the cited newsgroup post he goes into it even more emphatically (but respectfully calls it "elegant" ). But it sounds like there's more to the story.

I've spent some time conversing directly with Chip and Charles Williams on the topic of VLOOKUP(...TRUE) and ultimately none of us were 100% sure about it. I've relied on its, shall I say, quirks since version 4, using it on unsorted data. But I'm willing to read and test more to correct my unfamiliarity, if you want to suggest anything.

Here is a formal argument I've put forward eons ago...

Aladin Akyurek
View profile
More options Dec 14 2003, 9:00 pm
Newsgroups: microsoft.public.excel.worksheet.functions
From: "Aladin Akyurek" <akyu...@xs4all.nl>
Date: Sun, 14 Dec 2003 19:49:47 +0100
Local: Sun, Dec 14 2003 8:49 pm
Subject: Re: Help with LOOKUP (golf handicaps)
Reply to author | Forward | Print | Individual message | Show original | Report this message | Find messages by this author

"Peo Sjoblom" <terr...@mvps.org> wrote in message

news:uZdcQUmwDHA.540@tk2msftngp13.phx.gbl...

> I also believe it is a bug since the help files says that the array should
> be in ascending order and you would expect the #N/A error if not.
> The problem is if MS fixes this "bug" and then it won't work. I'd rather
> use something else.

I fail to understand how the following proposition/argument forces one to
conclude a bug:

"[T]he help files says that the array should be in ascending order and you
would expect the #N/A error if not."

It seems to me quite probable that the lookup functions in Excel share the
same procedures. It's quite plausible that Excel's code contain the
following procedures:

[A] Linear/sequential search (LS).
Binary search (BS).
[C] Used range (UR).

It's also quite certain that

=MATCH(v,X,1)
=VLOOKUP(v,Y,n,1)

bound to activate UR and BS. I admit to be less certain about UR, although I
believe the behavior of the lookup functions strongly suggest it.
Additionally, this is of capital importance, these functions do not call
upon some procedure which verifies whether X or INDEX(Y,0,1) is sorted in
ascending order. That such a check/test is NOT carried out is certain for an
obvious reason: Speed. MATCH et al would not be as fast as we know them when
such a test should be included. I'm happy to observe that the programmers at
MS did not include such a test.

I strongly reject an application developer's view that Excel itself should
check whether a user is not passing an unsorted object (array or range) to
these functions. The article
http://support.microsoft.com/default.aspx?scid=kb;en-us;181201 warns against
unintended use of match-type (range-lookup) TRUE/1 in lookup functions, not
revealing a bug about them, as sometimes pointed out.

Now back to the use of BigNum (9.99999999999999E+307) and BigStr
(REPT("z",255)...

BigNum and BigStr are extremely rare events in data Excel is used to
process. That's why it's safe to use them as lookup values in an expression
that will invoke BS. Schematically,

=MATCH(BigNum/BigStr,X,1)

will invoke:

BS(BigNum/BigStr,UR(X))

Given the foregoing, we are to bound to get the last value in X because the
last value will be the last half of data BS will be examining. Since the
last examined value <= BigNum/BigStr, MATCH (or ther lookup functions) will
return it tout court.

Hope it's clear that only an *ascending order check* would allow Excel to
return #N/A regarding the formulas under discussion. A software programmer
should definitely reject adding a procedure for such a test to the
underlying system for performance reasons.

Source: http://tinyurl.com/2lj39x

 

[Gates Is Antichrist]

Thank you for that link. Several of your big-hitter peers lay it out there. I wouldn't call it "eons ago."

Despite tushar's even more vociferous disdain for the approach in that discussion, he had earlier used the word "elegant" - a term that any mathematician can appreciate. This is just one of a list of breakthroughs that you've expressed; by all means keep them coming.

Still in scientific fairness - or perhaps pragmatic fairness, since we're dealing with Microsoft here - friendly reservations and concerns of course have their place. Rather than rehash all of those I only referred to that curtly, assuming that anyone who wanted the details could follow the links, pointing to people more advanced and articulate than myself. Harlan made a notable offering - "Or, perhaps more to the point, when Microsoft makes undocumented changes to MATCH in some future version. Not exactly a remote possibility."

Deducing the binary search nature of (...TRUE) by experimental results and speed timings was impressive (was there other evidence?). I would gather that this was then used as a postulate to developing the largest-number technique. It would just be nice to know for sure what's "under the hood," which to me (and perhaps the aforementioned peers) would solidify it. What would be the "right thing to do" (a forbidden phrase at Microsoft?) would be for them to expose the currently (and long-standing!) undocumented behavior. Sadly, their MBA whiz kids or witch doctors or whoever decides it has seemingly concluded that releasing the details of what VLOOKUP does would so empower their competitors as to cause the immediate demise of the Microsoft kingdom. Typical.

There's interesting "to and fro" about whether Excel should even permit TRUE with unsorted. You make a strong point that its great performance appeal would be decimated if the range was first sorted every time. That's certainly logical: what good is a fast variant if its validation wipes out its speed!

 

[Aladin Akyurek]

...

Deducing the binary search nature of (...TRUE) by experimental results and speed timings was impressive (was there other evidence?). I would gather that this was then used as a postulate to developing the largest-number technique.

A reconstruction the way you put it would be admissible... Binary search (BS) is not an unknown territory in computer science and artficial intelligence. It's a fast algorithm for search (and sort) tasks. TRUE is just a signal to Excel to run the functions with BS.

It would just be nice to know for sure what's "under the hood," which to me (and perhaps the aforementioned peers) would solidify it.

It's not that mysterious. It's just (some form of) BS or a kindred algorithm. The look up functions LOOKUP in Excel, I assume, behaves the same as that of Lotus. Nowadays, LOOKUP of OpenOffice and Gnumeric is like that of Excel. I don't see why MS should make public underlying code.

What would be the "right thing to do" (a forbidden phrase at Microsoft?) would be for them to expose the currently (and long-standing!) undocumented behavior.

I'm not that sure that the qualification "undocumented" applies here. Excel's Help file is not a "formal specs" for programmers...

Sadly, their MBA whiz kids or witch doctors or whoever decides it has seemingly concluded that releasing the details of what VLOOKUP does would so empower their competitors as to cause the immediate demise of the Microsoft kingdom. Typical.

As intimated above, I don't see why the formal specs should be public.

There's interesting "to and fro" about whether Excel should even permit TRUE with unsorted. You make a strong point that its great performance appeal would be decimated if the range was first sorted every time. That's certainly logical: what good is a fast variant if its validation wipes out its speed!

Exactly... More often than not, the lookup questions involve tables which cannot be enumerated like in:

0 up to 5 --> A
5 up to 25 --> D
25 up to infinitum --> X

A lookup function that cannot but carry out an exact match would require a separate entry for every possible value like 0.009, etc.