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

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



--
View this message in context: 
http://nabble.documentfoundation.org/LibreOffice-3-4-4-Error-wrong-data-type-tp3614423p3614648.html
Sent from the Users mailing list archive at Nabble.com.

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