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

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

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