Array formulas change outcome on a German computer?

  • Thread starter Thread starter Jay Weiss
  • Start date Start date
J

Jay Weiss

Hi,

Okay, this is a strange one. I have a worksheet that uses an array
formula to sort values in another sheet and retrieve them in their
sorted order. So far, so good.

The weird thing is, this works fine on computers in our offices
everywhere but in Germany, where it breaks. No one can seem to figure
this out.

Does anyone have any idea how this might be happening, aside from
individual computers being screwed up?

Thanks...

....Jay
 
Hi Peo,

The contents of the array formula are as follows:
=IF(VALUE(MID(TRANSPOSE(LARGE(VALUE(Exec_Code),COLUMN(INDIRECT("1:"&COLUMNS(Exec_Code))))),
2,2))>0,
VALUE(TRANSPOSE(LARGE(VALUE(Exec_Code),COLUMN(INDIRECT("1:"&COLUMNS(Exec_Code)))))),"")

Exec_Code is a named range for a row in which every cell contains a
five-digit value.

Thanks for your (and anyone else's) help!

....Jay
 
That formula in German is

=WENN(WERT(TEIL(MTRANS(KGRÖSSTE(WERT(Exec_Code);SPALTE(INDIREKT("1:"&SPALTEN(Exec_Code)))));2;2))>0;WERT(MTRANS(KGRÖSSTE(WERT(Exec_Code);SPALTE(INDIREKT("1:"&SPALTEN(Exec_Code))))));"")


Tell them to check if that works and also when you say breaks what does it
mean?


Here is an alternative where you array enter the formula in a single cell
and copy it down until you get blank cells

=IF(ROWS(A$1:A1)<=SUM(N(Exec_Code<>"")),INDEX(Exec_Code,MATCH(LARGE(COUNTIF(Exec_Code,"<="&Exec_Code),
SUM(N(Exec_Code=""))+ROWS($A$1:A1)),COUNTIF(Exec_Code,"<="&Exec_Code),0)),"")

will sort NUMERIC values in descending order so if your values in Exce_Code
are text numbers it will fail



--


Regards,


Peo Sjoblom
 
=IF(ROWS(A$1:A1) said:
SUM(N(Exec_Code=""))+ROWS($A$1:A1)),COUNTIF(Exec_Code,"<="&Exec_Code),0)),"")

will sort NUMERIC values in descending order so if your values in
Exce_Code are text numbers it will fail
Click to expand...


Actually it will sort text as well (abc, gft etc) unless they are text
numbers


Peo
 
Back
Top