Array constant issue

S

Sige

Hi There,

IMO this formula should allow me to define what my last row is in
column D
=MAX(CHOOSE({1,2};IF(ISNUMBER(MATCH(REPT("z";255);D:D));MATCH(REPT("z";255);D:D);0);MATCH(9,99999999999999E+307;D:D)))
Above formula will find me the last row with a TEXT string!

Though I have to change CHOOSE({1,2} to CHOOSE({1;2} to find the last
row with a NUMERICAL value!

Mind I use ";" as argument separator!

What "separator"do I have to use to let it work like it should?
Or is ther another issue?

This is what the HELP tells me:
· Separate values in different columns with commas (,)
· Separate values in different rows with semicolons (;)


Anyone Please,
Sige
 
B

Bob Phillips

Try this instead

=LOOKUP(2;1/(D1:D65535<>"");D1:D65535)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Hi There,

IMO this formula should allow me to define what my last row is in
column D
=MAX(CHOOSE({1,2};IF(ISNUMBER(MATCH(REPT("z";255);D:D));MATCH(REPT("z";255);
D:D);0);MATCH(9,99999999999999E+307;D:D)))
Above formula will find me the last row with a TEXT string!

Though I have to change CHOOSE({1,2} to CHOOSE({1;2} to find the last
row with a NUMERICAL value!

Mind I use ";" as argument separator!

What "separator"do I have to use to let it work like it should?
Or is ther another issue?

This is what the HELP tells me:
· Separate values in different columns with commas (,)
· Separate values in different rows with semicolons (;)


Anyone Please,
Sige
 
S

Sige

Hi Bob,

Thx for your reponse ... your solutions will retrieve what the value is
in my last row.
Though I would like to define what the last row number is.

I think my formula should do fine ... besides the CHOOSE-issue where i
have to change my spearator from , to ;
I do not see what is wrong in the formula?!?!?!

Thanks for your insight,
Best Regards, Sige

As an alternative I can retrieve my last row-number via:
{=IF(ISBLANK(D65536);MATCH(2;1/(1-ISBLANK(D1:D65535)));65536)}
But I prefer not to use array formulas for "performance" reasons.
 
B

Bob Phillips

Sige,

I couldn't get your formula to work as I couldn't get the two IFs to work
jointly to return an array that MAX can work on. Meanwhile, whilst I think
about it, how about this much simpler array formula along the same lines

=MAX(IF(D1:D65535<>"",ROW(D1:D65535)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
B

Bob Phillips

I tell a lie, this does it

=MAX((IF(ISNUMBER(MATCH(REPT("z",255),D:D)),MAX(MATCH(REPT("z",255),D:D)),0)
),(IF(ISNUMBER(MATCH(9.99999999999999E+307,D:D)),MAX(MATCH(9.99999999999999E
+307,D:D)),0)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
R

Ron Rosenfeld

Hi Bob,

Thx for your reponse ... your solutions will retrieve what the value is
in my last row.
Though I would like to define what the last row number is.

I think my formula should do fine ... besides the CHOOSE-issue where i
have to change my spearator from , to ;
I do not see what is wrong in the formula?!?!?!

Thanks for your insight,
Best Regards, Sige

As an alternative I can retrieve my last row-number via:
{=IF(ISBLANK(D65536);MATCH(2;1/(1-ISBLANK(D1:D65535)));65536)}
But I prefer not to use array formulas for "performance" reasons.
Click to expand...

Somewhat simpler array formulas:

Row number of last numerical entry:

=MAX(ISNUMBER(rng)*ROW(rng))

Row number of last entry (number or text)

=MAX(NOT(ISBLANK(rng))*ROW(rng))

where rng is NOT a full column


--ron
 
S

Sige

Hi Bob & Ron,

Thank you for your suggestions! Not all solutions are robust for Errors
/ EmptyRange / ...
But could you agree that:

=MAX((IF(ISNUMBER(MATCH(REPT("z",255),D:D)),MAX(MATCH(REPT("z",255),D:D)),0­)

),(IF(ISNUMBER(MATCH(9.99999999999999E+307,D:D)),MAX(MATCH(9.99999999999999­E

+307,D:D)),0)))
Is in terms of performance by far the quickest?

Best Regards, Sige


Bob, maybe you could "append" this part to your excellent paper
"Getting the last value in Range"
 
C

Charles Williams

If you want fast calculation try the counting functions in fxlCountFuncs.zip

http://www.decisionmodels.com/downloads.htm

they are a lot faster than the array formulae.

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

Hi Bob & Ron,

Thank you for your suggestions! Not all solutions are robust for Errors
/ EmptyRange / ...
But could you agree that:

=MAX((IF(ISNUMBER(MATCH(REPT("z",255),D:D)),MAX(MATCH(REPT("z",255),D:D)),0­)

),(IF(ISNUMBER(MATCH(9.99999999999999E+307,D:D)),MAX(MATCH(9.99999999999999­E

+307,D:D)),0)))
Is in terms of performance by far the quickest?

Best Regards, Sige


Bob, maybe you could "append" this part to your excellent paper
"Getting the last value in Range"
 
S

Sige

If I would like to find the "MAX ROW" for eg.10 columns, eg. D:M

Do you have any suggestions on that matter for this formula?

=MAX((IF(ISNUMBER(MATCH(REPT("z",255),D:D)),MAX(MATCH(REPT("z",255),D:D)),0­­)

),(IF(ISNUMBER(MATCH(9.99999999999999E+307,D:D)),MAX(MATCH(9.99999999999999­­E

+307,D:D)),0)))

With array formulas, no prob:
{=MAX(IF(D1:M65535<>"";ROW(D1:M65535)))}
{=MAX(ISNUMBER(D1:M65535)*ROW(D1:M65535))}
{=MAX(NOT(ISBLANK(D1:M65535))*ROW(D1:M65535))}

Best Regards Sige
 
S

Sige

Thank you Charles,

A code solution is not always well accepted. But I think that sometimes
it is clearer and more functional than "inventing" some complicated
worksheetformulae...
Writing the UDFs ...is then of course another story, but thank you for
making it public!

Best Regards Sige
 
R

Roger Govier

Hi Sige

As you well know from our previous "conversations", I am always in
favour of the simple solution.
I don't really see too much difference between this problem, and one you
posted last October.

If in another column you used
=--(COUNTA(D1:M1)>0)*ROW()
Then your answer is simply
=MAX(C:C) (or whatever column you used for the formula above.

--
Regards

Roger Govier


If I would like to find the "MAX ROW" for eg.10 columns, eg. D:M

Do you have any suggestions on that matter for this formula?

=MAX((IF(ISNUMBER(MATCH(REPT("z",255),D:D)),MAX(MATCH(REPT("z",255),D:D)),0­­)

),(IF(ISNUMBER(MATCH(9.99999999999999E+307,D:D)),MAX(MATCH(9.99999999999999­­E

+307,D:D)),0)))

With array formulas, no prob:
{=MAX(IF(D1:M65535<>"";ROW(D1:M65535)))}
{=MAX(ISNUMBER(D1:M65535)*ROW(D1:M65535))}
{=MAX(NOT(ISBLANK(D1:M65535))*ROW(D1:M65535))}

Best Regards Sige
 
A

Aladin Akyurek

X2:

=MATCH(REPT("z";255);D:D)

X3:

=MATCH(9,99999999999999E+307;D:D)

=CHOOSE(COUNT(X2:X3)+1;#N/A;SUMIF(X2:X3;"<>#N/A");MAX(X2:X3))
 
S

Sige

Hi Roger,
I don't really see too much difference between this problem, and one you
posted last October
Click to expand...

The difference here is that I have a blind faith in Aladin's solutions
;-)...and was surprised to find out that the solution he proposed at
that time does not return me the Max because the
Choose-funtion in ... =MAX(CHOOSE({1,2};... is dependent on the
separator I put there!

That's why I was asking about "Array constant issue", cos maybe I had
to use another separator? (Me not being very familiar to use arrays in
a formula, which are not array-formulas, that is.)

Best Regards Sige
 
S

Sige

MYSTERY SOLVED

Due to the "*~*~*" my regional settings a "," -separator should be a
"\" !

Thanks Rogier!
 
R

Roger Govier

You're very welcome Sige.

Apologies to the NG, the suggestion to change "," to "\" was made in a
private email between Sige and myself.
For the sake of completeness in the archives, Sige explained certain
anomalies he was experiencing in results between using Choose{1,2} and
Choose{1;2}
Using similar data I gained the same results with both types of entry in
the formula.
I tried it in XL97 (the same as Sige), to ensure it wasn't an Excel
variation between versions.
I then changed my Regional settings to French(Belgium), which converted
the formula with ", " to ";"
and that with ";" to "\".

--
Regards

Roger Govier


MYSTERY SOLVED

Due to the "*~*~*" my regional settings a "," -separator should be a
"\" !

Thanks Rogier!
 
S

Sige

Thanks for the full explanation.
Sorry forry misspelling your name Roger!

I must have been "functioning" away! ;-)
=MID("Roger";1;MID(PI();11;1))&MID("Govier";MID(PI();4;1);MID(PI();11;1))

Cheers Sige
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

MegaFormula curiosity 2
Multiple table lookup 12
Run Time Error, FormulaR1C1 3
IF AND MATCH data type issue 2
Set of numbers 2
Identifying correct elements with Countif 3
vlookup issue 2
Excel Trying to do a vlookup with a nested right function on the array 3

Top