[libreoffice-users] Re: LibreOffice 3.4.4 - Error: wrong data type.

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".

-- 
Regards, Pal


-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted