Array constant issue
 

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,99999999 999999E+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

Array constant issue
 

Try this instead

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sige" wrote in message
ups.com...
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

Array constant issue
 

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.

Array constant issue
 

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)

"Sige" wrote in message
ups.com...
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.

Array constant issue
 

I tell a lie, this does it

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

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Sige" wrote in message
ups.com...
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.

Array constant issue
 

On 10 Mar 2006 05:32:53 -0800, "Sige" wrote:

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.

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

Array constant issue
 

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(MA TCH(REPT("z",255),D:D)),0*)

),(IF(ISNUMBER(MATCH(9.99999999999999E+307,D:D)),M AX(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"

Array constant issue
 

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

"Sige" wrote in message
oups.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(MA TCH(REPT("z",255),D:D)),0*)

),(IF(ISNUMBER(MATCH(9.99999999999999E+307,D:D)),M AX(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"

Array constant issue
 

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(MA TCH(REPT("z",255),D:D)),0**)

),(IF(ISNUMBER(MATCH(9.99999999999999E+307,D:D)),M AX(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

Array constant issue
 

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

Charles Williams wrote:
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

"Sige" wrote in message
oups.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(MA TCH(REPT("z",255),D:D)),0*)

),(IF(ISNUMBER(MATCH(9.99999999999999E+307,D:D)),M AX(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"

Array constant issue
 

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


"Sige" wrote in message
oups.com...
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(MA TCH(REPT("z",255),D:D)),0**)

),(IF(ISNUMBER(MATCH(9.99999999999999E+307,D:D)),M AX(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

Array constant issue
 

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))

Sige wrote:
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,99999999 999999E+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

Array constant issue
 

Hi Roger,

I don't really see too much difference between this problem, and one you
posted last October

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


Roger Govier wrote:
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


"Sige" wrote in message
oups.com...
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(MA TCH(REPT("z",255),D:D)),0**)

),(IF(ISNUMBER(MATCH(9.99999999999999E+307,D:D)),M AX(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

Array constant issue
 

Hi Aladin,

This "Choose", works like a charm :o)

Thank you very much, Sige

Array constant issue
 

MYSTERY SOLVED

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

Thanks Rogier!


Sige wrote:
Hi Roger,

I don't really see too much difference between this problem, and one you
posted last October

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


Roger Govier wrote:
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


"Sige" wrote in message
oups.com...
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(MA TCH(REPT("z",255),D:D)),0**)

),(IF(ISNUMBER(MATCH(9.99999999999999E+307,D:D)),M AX(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

Array constant issue
 

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


"Sige" wrote in message
ups.com...
MYSTERY SOLVED

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

Thanks Rogier!


Sige wrote:
Hi Roger,

I don't really see too much difference between this problem, and one
you
posted last October

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


Roger Govier wrote:
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


"Sige" wrote in message
oups.com...
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(MA TCH(REPT("z",255),D:D)),0**)

),(IF(ISNUMBER(MATCH(9.99999999999999E+307,D:D)),M AX(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

Array constant issue
 

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