Lowest row with data in Col A

[The Idea Dude]

Hi all,

How do I find the lowest row (highest row number) in column A that contains some data?

Thanks

 

[PaddyD]

Here goes:

=LARGE(IF(ISNA(B2:B4)=FALSE,B2:B4),1)

array entered, with {2,#N/A!,5} in B2:B4

isna(B2:B4) would return the array {FALSE, TRUE, FALSE), we use isna(B2:B4)=FALSE to return the array {TRUE, FALSE, TRUE), effectively identifying the cells with numbers in this case. Fed into the large - if() combination, this effectively says, "do the large calculation on those cells in the array that return true for the test isna(B2:B4)=FALSE.

Select the cell with the formula & click the = sign in the formula bar - you should see something like {2,FALSE,5}...

Clear as mud?
Paddy

see also:
http://www.mrexcel.com/tip011.shtml
This message was edited by PaddyD on 2002-10-14 21:35

 

[Aladin Akyurek]

On 2002-10-14 20:48, The Idea Dude wrote:
Aladin, is it possible to set this formula up to allow for all 3 combinations so I don't get a #NA error

You mean you want to apply

=MAX(MATCH(9.99999999999999E+307,A:A),MATCH(REPT("z",50),A:A))

to a column instead of first determining the data type and choosing the appropriate formula. The following formula would do what you want...

=MAX(IF(ISNUMBER(SETV(MATCH(9.99999999999999E+307,A:A),1)),GETV(1),0),IF(ISNUMBER(SETV(MATCH(REPT("z",90),A:A),2)),GETV(2),0))

but it requires that you install the morefunc.xll add-in, downloadable from:

http://longre.free.fr/english/index.html

A tip. You can assign a name, say BigNum, to the big number that figures in the above formula and use that name in the formula instead.

(1.) Activate Insert|Name|Define.
(2.) Enter BigNum as name in the Names in Workbook box.
(3.) Enter in the Refers to box:

9.99999999999999E+307

(4.) Click OK.

The above formula now beomes...

=MAX(IF(ISNUMBER(SETV(MATCH(BigNum,A:A),1)),GETV(1),0),IF(ISNUMBER(SETV(MATCH(REPT("z",90),A:A),2)),GETV(2),0))

Aladin

 

[Aladin Akyurek]

[...]
9.9999999E307 is the lardest number excel can store. The match looks down the column for the number, and returns the last row with data if it can't find it. IMO, it is an open question whether match()'s behavious in this regard is considered expected, surprising or a small bug...see,e.g,

http://216.92.17.166/board/viewtopic.php?topic=17175&forum=2&start=0

for more discussion.

Paddy,

I know I failed to reply/return to that bug in MATCH issue... Since you quoted it, I might as well state what I believe to be the case...

Tushar listed 3 examples, all of which involve how MATCH behaves under MatchType 1 and -1.

The issue appears to me to be the following:

If you think/expect that whether the reference is sorted one way or other should be the assessed by MATCH and return results in accord with that assessment, I concede the MATCH worksheet function does not carry such an assessment. It takes the MatchType argument as "intended", that's, as depicting the sort order of the reference it's given.

I believe its programmers took the right decision not to check the correctness of 1 or -1 spec about the reference argument. It's not hard to imagine that such a check will make MATCH (and other lookup functions) rather inefficient.

See...

Book3
	A	B	C	D
1	Data	Target	MatchType=1	AscendingSortBeforeMatch
2	3	3	3	3
3	2	2	2	2
4	1	1	#N/A	1
5
6	Data	Target	MatchType=-1	DescendingSortBeforeMatch
7	3	3	1	1
8	2	2	2	2
9	1	1	3	3
Sheet1

We get #N/A in C4. That's quite logical. The algorithm compares the Target value 1 with the First Value, which is 3, in the reference with as result: First Value > Target ==> Target is not available.

All of the examples has the same point: Whether MATCH should believe the MatchType argument or not. I say MATCH should better believe the MatchType spec, otherwise it has to evaluate the truth of the spec every time, an action that will make it a useless, inefficient function.

MATCH does not sort nor assess the sort order specified by the MatchType argument. I'm happy with that it doesn't.

Aladin

 

[PaddyD]

Hi Aladin,

Cheers for the thoughts! I'm symathetic to the "don't expect match to evaluate whether the range is appropriately ordered - that's the developer's job" idea!

paddy