LibreOffice 3.4.4 - Error: wrong data type.

Csanyi_Pal · December 27, 2011, 2:23pm

Hi,

I have in the range of O23:O27 on one sheet formulas:
1. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=5;K5:K24<=7))
2. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=8;K5:K24<=15))
3. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=16;K5:K24<=25))
4. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=26;K5:K24<=34))
5. cell: =DARABTELI(K5:K24;">=35")

The cell K5 has the formula:
=HA(CELLA("contents";'1.'.T$28)<>0;CELLA("contents";'1.'.T$28);".")

In the cells: O23, O24, O27 everything is well.

But in cells O25 and O26 I get ### instead of some result and in the
status line there is an error: 'Error: wrong data type.' when those cells
are active (one by one of course).

What could be the problem here?

Carlo_Strata · December 27, 2011, 2:57pm

Hi Pal,

could you put your file in a public place (i.e. DropBox) so that we could open the sheet and see the formulas in our own language and so that we could help you better?

Thank you,

Carlo

p.s. You could also attach the file (create a new copy and delete private infos from it) in this mailing list thread (Nabble interface) with
http://nabble.documentfoundation.org/LibreOffice-3-4-4-Error-wrong-data-type-td3614423.html

logging in it (registration needed), use the attach file feature and then telling us when done.

Csanyi_Pal · December 27, 2011, 3:04pm

Csanyi Pal <csanyipal@gmail.com> writes:

Sorry I was unclear again!

I have in the range of O23:O27 on one sheet formulas:

cell O23: =DARABTELI(K5:K24;ÉS(K5:K24>=5;K5:K24<=7))
cell O24: =DARABTELI(K5:K24;ÉS(K5:K24>=8;K5:K24<=15))
cell O25: =DARABTELI(K5:K24;ÉS(K5:K24>=16;K5:K24<=25))
cell O26: =DARABTELI(K5:K24;ÉS(K5:K24>=26;K5:K24<=34))
cell O27: =DARABTELI(K5:K24;">=35")

The cell K5 has the formula:
=HA(CELLA("contents";'1.'.T$28)<>0;CELLA("contents";'1.'.T$28);".")

The cells bellow of K5 (K6:K24) has same formulas except addresses is
different, eg.:

instead of '1.'.T$28 there is '2.'.T$28 and so on, like:
=HA(CELLA("contents";'2.'.T$28)<>0;CELLA("contents";'2.'.T$28);".")

In the cells: O23, O24, O27 everything is well.

But in cells O25 and O26 I get ### (or #VALUE! if I set the column
wider) instead of some result and in the status line there is an
error: 'Error: wrong data type.' when those cells are active (one by
one of course).

These formulas are the same, uses the same range so I don't understand
what could be the problem here?

Carlo_Strata · December 27, 2011, 3:14pm

I suggest you, only for debug, to output in some way, somewhere and in the unused space in the sheet, the parameters' value of the " wrong data type" formulas and to analize them: may be you are able to discover the trouble yourself.

Make us to know what you'll discover n this way.

Carlo

Csanyi_Pal · December 27, 2011, 3:16pm

Hi Carlo,

I attached the file:

http://nabble.documentfoundation.org/file/n3614513/6oszt_NaploBizonyitvany_KitoltesetSegitoTablazat.ods
6oszt_NaploBizonyitvany_KitoltesetSegitoTablazat.ods

Best Regards, Pál

Gerard_Fargeot · December 27, 2011, 3:19pm

Hi,

Hi,

I have in the range of O23:O27 on one sheet formulas:
1. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=5;K5:K24<=7))
2. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=8;K5:K24<=15))
3. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=16;K5:K24<=25))
4. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=26;K5:K24<=34))
5. cell: =DARABTELI(K5:K24;">=35")

The cell K5 has the formula:
=HA(CELLA("contents";'1.'.T$28)<>0;CELLA("contents";'1.'.T$28);".")

In the cells: O23, O24, O27 everything is well.

But in cells O25 and O26 I get ### instead of some result and in the
status line there is an error: 'Error: wrong data type.' when those cells
are active (one by one of course).

What could be the problem here?

--
Regards, Pal

You will have more answer if you post formula in English instead of
Hungarian.

dot (.) is a special character. In the menu Tools > Options > LO Calc >
Calculate,
turned off "Enable regular expressions in formulas".

Your formula =DARABTELI(K5:K24;ÉS(K5:K24>=5;K5:K24<=7)) (COUNTIF in
english) seems to be false.
ÉS(K5:K24>=5;K5:K24<=7) if validate as "normal formula" only checked if 1st
row (K5) is between 5 and 7.
If validate as an "array formula", checked if *all* the cells of the range
are between 5 and 7 and return an array of 1 if True, 0 if false.

If you want to count how many cells of the range are >=5 & <=7 , used
SUMPRODUCT :
=SZORZATÖSSZEG(K5:K24>=5;K5:K24<=7)

It is also a very bad idea to use dot in sheetname. Always use alphanumeric
characters.

Gérard

Csanyi_Pal · December 27, 2011, 3:23pm

csanyipal <csanyipal@gmail.com> writes:

I attached the file:

http://nabble.documentfoundation.org/file/n3614513/6oszt_NaploBizonyitvany_KitoltesetSegitoTablazat.ods
6oszt_NaploBizonyitvany_KitoltesetSegitoTablazat.ods

The formulas that causes error are on the Sheet called: 'O.tanácsi f.év'

Csanyi_Pal · December 27, 2011, 3:45pm

Gérard Fargeot <gerard.fargeot@orange.fr> writes:

I have in the range of O23:O27 on one sheet formulas:
1. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=5;K5:K24<=7))
2. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=8;K5:K24<=15))
3. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=16;K5:K24<=25))
4. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=26;K5:K24<=34))
5. cell: =DARABTELI(K5:K24;">=35")

The cell K5 has the formula:
=HA(CELLA("contents";'1.'.T$28)<>0;CELLA("contents";'1.'.T$28);".")

In the cells: O23, O24, O27 everything is well.

But in cells O25 and O26 I get ### instead of some result and in the
status line there is an error: 'Error: wrong data type.' when those cells
are active (one by one of course).

What could be the problem here?

You will have more answer if you post formula in English instead of
Hungarian.

Can I do that from LibreOffice? How can I translate formula names? Must
I change the Language environment for this?

dot (.) is a special character. In the menu Tools > Options > LO Calc >
Calculate, turned off "Enable regular expressions in formulas".

Your formula =DARABTELI(K5:K24;ÉS(K5:K24>=5;K5:K24<=7)) (COUNTIF in
english) seems to be false.
ÉS(K5:K24>=5;K5:K24<=7) if validate as "normal formula" only checked if 1st
row (K5) is between 5 and 7.
If validate as an "array formula", checked if *all* the cells of the range
are between 5 and 7 and return an array of 1 if True, 0 if false.

Ah I see now! Thanks!

If you want to count how many cells of the range are >=5 & <=7 , use
SUMPRODUCT :
=SZORZATÖSSZEG(K5:K24>=5;K5:K24<=7)

Exactly that is what I want to do. Thanks for the information about the
formula SUMPRODUCT!

It is also a very bad idea to use dot in sheetname. Always use alphanumeric
characters.

I edited the formula and replaced dot '.' with zero "0".

Carlo_Strata · December 27, 2011, 4:32pm

csanyipal wrote

Gérard Fargeot <gerard.fargeot@> writes:

I have in the range of O23:O27 on one sheet formulas:
1. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=5;K5:K24<=7))
2. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=8;K5:K24<=15))
3. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=16;K5:K24<=25))
4. cell: =DARABTELI(K5:K24;ÉS(K5:K24>=26;K5:K24<=34))
5. cell: =DARABTELI(K5:K24;">=35")

The cell K5 has the formula:
=HA(CELLA("contents";'1.'.T$28)<>0;CELLA("contents";'1.'.T$28);".")

In the cells: O23, O24, O27 everything is well.

But in cells O25 and O26 I get ### instead of some result and in the
status line there is an error: 'Error: wrong data type.' when those
cells
are active (one by one of course).

What could be the problem here?

You will have more answer if you post formula in English instead of
Hungarian.

Can I do that from LibreOffice? How can I translate formula names? Must
I change the Language environment for this?

dot (.) is a special character. In the menu Tools > Options > LO Calc >
Calculate, turned off "Enable regular expressions in formulas".

Your formula =DARABTELI(K5:K24;ÉS(K5:K24>=5;K5:K24<=7)) (COUNTIF in
english) seems to be false.
ÉS(K5:K24>=5;K5:K24<=7) if validate as "normal formula" only checked if
1st
row (K5) is between 5 and 7.
If validate as an "array formula", checked if *all* the cells of the
range
are between 5 and 7 and return an array of 1 if True, 0 if false.

Ah I see now! Thanks!

If you want to count how many cells of the range are >=5 & <=7 , use
SUMPRODUCT :
=SZORZATÖSSZEG(K5:K24>=5;K5:K24<=7)

Exactly that is what I want to do. Thanks for the information about the
formula SUMPRODUCT!

It is also a very bad idea to use dot in sheetname. Always use
alphanumeric
characters.

I edited the formula and replaced dot '.' with zero "0".

In addition to what has already been said I attach your ods slightly
modified by me.

Take a look on orange backgrounded 'O.tanácsi f.év' cells: you could learn
more (vector formulas and "." cell value interpretation for range test).

http://nabble.documentfoundation.org/file/n3614648/6oszt_NaploBizonyitvany_KitoltesetSegitoTablazat%2C_Carlo.ods
6oszt_NaploBizonyitvany_KitoltesetSegitoTablazat%2C_Carlo.ods

Have a nice evening,

Carlo

Gerard_Fargeot · December 27, 2011, 5:36pm

<quote="csanyipal">
Can I do that from LibreOffice? How can I translate formula names? Must
I change the Language environment for this?

Tools > Options > LO Calc > formula, "use english functions names".

But some functions (add-in) are not translated.

I've made a "formula translator" :
http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=41870
You can translate functions or whole formula to a language to an other.

Gérard

Csanyi_Pal · December 30, 2011, 10:35pm

Gérard Fargeot wrote

I've made a "formula translator" :
http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=41870
You can translate functions or whole formula to a language to an other.
Gérard

Grait!
I downloaded it and translate the UI to Hungarian. However I have some
reflections about it.
In the 'fonction' sheet columns B and C are protected so one can't make none
of these columns wider so eg. my translation can't be seen on the whole.
That's the case in the 'formule' sheet too.

My translated UI is here:
http://nabble.documentfoundation.org/file/n3622035/traducteur_formule_RC1.ods
traducteur_formule_RC1.ods
Thanks!

Tom_Davies · December 30, 2011, 11:37pm

Hi
On the version you uploaded i was able to change the width of those columns quite easily. Perhaps it is only the original that protects those columns?
Regards from
Tom

Csanyi_Pal · December 31, 2011, 5:58am

Tom wrote

Hi
On the version you uploaded i was able to change the width of those
columns quite easily. Perhaps it is only the original that protects those
columns?
Regards from
Tom

Strange. I download that file that I uploaded and I can't change the width
of those columns.

Hladuvka_Jiri · December 31, 2011, 7:01am

Dne 30.12.2011 23:34, csanyipal napsal(a):

Gérard Fargeot wrote

I've made a "formula translator" :
http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=41870
You can translate functions or whole formula to a language to an other.
Gérard

... and how does this correspond to the original context of
http://nabble.documentfoundation.org/LibreOffice-3-4-4-Error-wrong-data-type-tt3614423.html

???

Csanyi_Pal · December 31, 2011, 7:26am

Hladůvka Jiří wrote

Dne 30.12.2011 23:34, csanyipal napsal(a):

Gérard Fargeot wrote

I've made a "formula translator" :
http://user.services.openoffice.org/en/forum/viewtopic.php?f=9&t=41870
You can translate functions or whole formula to a language to an other.
Gérard

... and how does this correspond to the original context of
http://nabble.documentfoundation.org/LibreOffice-3-4-4-Error-wrong-data-type-tt3614423.html

???

You are right, so I rename the Thread.

JOE_Conner · December 31, 2011, 5:03pm

<<SNIP>>
I think the problem may be the settings. Look at TOOLS -> PROTECT DOCUMENT -> (Both SHEET and DOCUMENT) uncheck each.
Did this do it for you?

Joe Conner, Poulsbo, WA USA

Csanyi_Pal · December 31, 2011, 5:22pm

Hi Joe,

Joe Conner <joeconner2007@gmail.com> writes:

Tom wrote

Hi
On the version you uploaded i was able to change the width of those
columns quite easily. Perhaps it is only the original that protects those
columns? Regards from
Tom

Strange. I download that file that I uploaded and I can't change the width
of those columns.

<<SNIP>>
I think the problem may be the settings. Look at TOOLS -> PROTECT
DOCUMENT -> (Both SHEET and DOCUMENT) uncheck each.
Did this do it for you?

Thanks! Yes it did and the password was 'Calc' without quotes.