On 2002-08-21 07:16, planetpj wrote:
Aladin,
I was reading an article that you wrote called Last In Cell. I was interested in one of the formulas that you wrote. The formula was =MATCH(1.0E+30,A:A)for columns. You also noted that these formulas expect that there will not be a numeric value as big as 1.0E+30 amoung the numeric values of a column/row you're testing. I know that the biggest positive number in excel repetory is(9.99999999999999E+307). I am just curious where does the 1.0E come from and what actually does it mean!!!
Where did you read that?
If you put 9.99999999999999E+307 in some cell, you'll see that Excel displays it as
1.00E+308
It's often necessary to determine the location/position of the last cell in use in a reference, which can be a definite range (e.g., A2:A50 or G1:M1) or a whole column or row (e.g., B:B or 4:4).
If the reference is of numeric type, this big number is used in a MATCH expression to capture the location of the last cell in use.
Lets say that we have defined BigNum as referring to 9.99999999999999E+307 by using the option Insert|Name|Define.
=MATCH(BigNum,Reference) [1]
indeed computes the location of the last cell in use.
MATCH in [1] ignores empty cells (also text and "") interspersed throughout the reference, which is a nice property. COUNT and COUNTA cannot for example be used with references showing gaps. Moreover, COUNTA is a volatile function that we would want to avoid if we possibly can.
How does [1] work?
As is clear from [1], the match-type is 1, that is, TRUE. When omitted, Excel assumes the match-type to be 1.
MATCH with match-type set to 1 expects that the reference is sorted in ascending order. In [1] we deliberately tell the undelying algorithm to look in the right half of the reference. When it finishes looking (by cutting each half in two until there is nothing left to cut), the last cell is the one that the algorithm has examined: it immediately returns the location of that cell
whose value is approximately matching the BigNum.
[1] enables us
( A.) to retieve the value in the last cell in use:
=INDEX(Reference,MATCH(BigNum,Reference))
( B.) to define names for references/ranges that frequently change, enclosed in OFFSET, e.g.,
=OFFSET(Data!$A$1,0,0,Admin!$B$3,Admin!$B$4) [2]
where Admin!B3 houses the formula
=MATCH(BigNum,Data!A:A) and Admin!B4 the formula:
=B3-(CELL("Row",Data!$A$1)-1)
[2] defines a possibly changing range in A:C and is superior to and far less risky than one which is often used:
=OFFSET(Data!$A$1,0,0,COUNT(Data!$A:$A)-1,3)
or
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A)-1,3)
If the reference of interest is of "text type", [1] becomes:
=MATCH(REPT("z",255),Reference)
which will give the location of the last cell which does not contain a formula-generated "".
Hope this explains the mystery a bit.
