2012/1/1 Johnny Rosenberg <[email protected]>: > 2012/1/1 Jay Lozier <[email protected]>: >> Toni >> >> Please advise which LO version you are using and your OS. This will help us >> determine if the problem is a know issue with a specific version of LO or >> with a particular OS. >> >> For your problem, I was able to replicate the behavior in LO 3.4.4 and try >> the following: >> >> =sum(if(isnumber(b5),-b5,0),if(isnumber(c5),-c5,0),if(isnumber(d5),d5,0,if(isnumber(e5),e5,0)) >> >> Alternate >> >> =sum(if(isnumber(d5),d5,0),if(isnumber(e5),e5,0))-sum(if(isnumber(b5),b5,0),if(isnumber(c5),c5,0)) >> >> I am validating that the data is actually a number (isnumber(cell) = true) >> and using either the value in the cell if true or 0 if it is not a number. >> Without redesigning your entire spreadsheet this may be the simplest way to >> fix your problem. > > Or just replace every ”Cell reference” with ”N(Cell reference)” (for > instance ”A1” → ”N(A1)”), whenever there is a chance that there is > text in it. This is not needed for SUM if it is implemented correctly. > ”=SUM(E4+D5)” doesn't make much sense to me. Should be the same as > ”=E4+D5”. ”;” is the parameter delimiter in LibreOffice Calc cell > functions, not ”+”: > ”=SUM(E4;D5)” does the same thing as the abovem and it gives no error > message when one or both of the cells contains a text string. > > If you want to sum things without the SUM() function, you need to make > sure that no text cells are included OR you need to treat them as 0. > This can easily be done with the N() function: > ”=N(E4)+N(D5)” and ”=SUM(N(E4)+N(D5))” both does the same thing and > both of them works no matter if cells contain text or numerical data. > However, the last example is a bit necessary,
Sorry for that typo. I meant ”unnecessary”, fo course. > since ”=SUM(E4;D5)” > works perfectly without the N() function. > > > Kind regards > > Johnny Rosenberg > ジョニー・ローゼンバーグ -- For unsubscribe instructions e-mail to: [email protected] 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
