software

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(quot;zquot;;255); D));MATCH(REPT(quot;zquot;;255);D);0);MATCH(9,99999999 999999E 307;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 quot;;quot; as argument separator!

What quot;separatorquot;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,
SigeTry this instead

=LOOKUP(2;1/(D165535lt;gt;quot;quot;);D165535)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Sigequot; gt; 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(quot;zquot;;255); D));MATCH(REPT(quot;zquot;;255);
D);0);MATCH(9,99999999999999E 307;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 quot;;quot; as argument separator!

What quot;separatorquot;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
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(D165535)));65536)}
But I prefer not to use array formulas for quot;performancequot; reasons.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(D165535lt;gt;quot;quot;,ROW(D165535)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Sigequot; gt; wrote in message ups.com...
gt; Hi Bob,
gt;
gt; Thx for your reponse ... your solutions will retrieve what the value is
gt; in my last row.
gt; Though I would like to define what the last row number is.
gt;
gt; I think my formula should do fine ... besides the CHOOSE-issue where i
gt; have to change my spearator from , to ;
gt; I do not see what is wrong in the formula?!?!?!
gt;
gt; Thanks for your insight,
gt; Best Regards, Sige
gt;
gt; As an alternative I can retrieve my last row-number via:
gt; {=IF(ISBLANK(D65536);MATCH(2;1/(1-ISBLANK(D165535)));65536)}
gt; But I prefer not to use array formulas for quot;performancequot; reasons.
gt;
I tell a lie, this does it

=MAX((IF(ISNUMBER(MATCH(REPT(quot;zquot;,255),D)),MAX(MA TCH(REPT(quot;zquot;,255),D)),0)
),(IF(ISNUMBER(MATCH(9.99999999999999E 307,D)),M AX(MATCH(9.99999999999999E
307,D)),0)))

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

quot;Sigequot; gt; wrote in message ups.com...
gt; Hi Bob,
gt;
gt; Thx for your reponse ... your solutions will retrieve what the value is
gt; in my last row.
gt; Though I would like to define what the last row number is.
gt;
gt; I think my formula should do fine ... besides the CHOOSE-issue where i
gt; have to change my spearator from , to ;
gt; I do not see what is wrong in the formula?!?!?!
gt;
gt; Thanks for your insight,
gt; Best Regards, Sige
gt;
gt; As an alternative I can retrieve my last row-number via:
gt; {=IF(ISBLANK(D65536);MATCH(2;1/(1-ISBLANK(D165535)));65536)}
gt; But I prefer not to use array formulas for quot;performancequot; reasons.
gt;
On 10 Mar 2006 05:32:53 -0800, quot;Sigequot; gt; wrote:

gt;Hi Bob,
gt;
gt;Thx for your reponse ... your solutions will retrieve what the value is
gt;in my last row.
gt;Though I would like to define what the last row number is.
gt;
gt;I think my formula should do fine ... besides the CHOOSE-issue where i
gt;have to change my spearator from , to ;
gt;I do not see what is wrong in the formula?!?!?!
gt;
gt;Thanks for your insight,
gt;Best Regards, Sige
gt;
gt;As an alternative I can retrieve my last row-number via:
gt;{=IF(ISBLANK(D65536);MATCH(2;1/(1-ISBLANK(D165535)));65536)}
gt;But I prefer not to use array formulas for quot;performancequot; 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

Hi Bob amp; Ron,

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

=MAX((IF(ISNUMBER(MATCH(REPT(quot;zquot;,255),D)),MAX(MA TCH(REPT(quot;zquot;,255),D)),0*)

),(IF(ISNUMBER(MATCH(9.99999999999999E 307,D)),M AX(MATCH(9.99999999999999*E

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

Best Regards, SigeBob, maybe you could quot;appendquot; this part to your excellent paper
quot;Getting the last value in Rangequot;If you want fast calculation try the counting functions in fxlCountFuncs.zip

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

quot;Sigequot; gt; wrote in message oups.com...
Hi Bob amp; Ron,

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

=MAX((IF(ISNUMBER(MATCH(REPT(quot;zquot;,255),D)),MAX(MA TCH(REPT(quot;zquot;,255),D)),0*)

),(IF(ISNUMBER(MATCH(9.99999999999999E 307,D)),M AX(MATCH(9.99999999999999*E

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

Best Regards, SigeBob, maybe you could quot;appendquot; this part to your excellent paper
quot;Getting the last value in Rangequot;
If I would like to find the quot;MAX ROWquot; for eg.10 columns, eg. D:M

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

=MAX((IF(ISNUMBER(MATCH(REPT(quot;zquot;,255),D)),MAX(MA TCH(REPT(quot;zquot;,255),D)),0**)

),(IF(ISNUMBER(MATCH(9.99999999999999E 307,D)),M AX(MATCH(9.99999999999999**E

307,D)),0)))

With array formulas, no prob:
{=MAX(IF(D1:M65535lt;gt;quot;quot;;ROW(D1:M65535)))}
{=MAX(ISNUMBER(D1:M65535)*ROW(D1:M65535))}
{=MAX(NOT(ISBLANK(D1:M65535))*ROW(D1:M65535))}

Best Regards SigeThank you Charles,

A code solution is not always well accepted. But I think that sometimes
it is clearer and more functional than quot;inventingquot; 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:
gt; If you want fast calculation try the counting functions in fxlCountFuncs.zip
gt;
gt; www.decisionmodels.com/downloads.htm
gt;
gt; they are a lot faster than the array formulae.
gt;
gt; Charles
gt; ______________________
gt; Decision Models
gt; FastExcel 2.2 Beta now available
gt; www.DecisionModels.com
gt;
gt; quot;Sigequot; gt; wrote in message
gt; oups.com...
gt; Hi Bob amp; Ron,
gt;
gt; Thank you for your suggestions! Not all solutions are robust for Errors
gt; / EmptyRange / ...
gt; But could you agree that:
gt;
gt; =MAX((IF(ISNUMBER(MATCH(REPT(quot;zquot;,255),D)),MAX(MA TCH(REPT(quot;zquot;,255),D)),0*)
gt;
gt; ),(IF(ISNUMBER(MATCH(9.99999999999999E 307,D)),M AX(MATCH(9.99999999999999*E
gt;
gt; 307,D)),0)))
gt; Is in terms of performance by far the quickest?
gt;
gt; Best Regards, Sige
gt;
gt;
gt; Bob, maybe you could quot;appendquot; this part to your excellent paper
gt; quot;Getting the last value in Rangequot;