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

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

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.

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.

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.

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

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"

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"

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

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

Hi Aladin,

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

Thank you very much, Sige