Re: [libreoffice-users] Re: Stuck with 3.3 forever?
SNIP On 12/4/2011 1:18 PM, Johnny Rosenberg wrote: 2011/12/3 Jay Lozierjsloz...@gmail.com: On 12/03/2011 12:50 PM, Johnny Rosenberg wrote: If you enter a number in A1, A3 displays that number+27. If you don't, A3 displays an error message: ”#VALUE!” (I guess; I didn't test this particular example…). I want it to display 27. Thanks anyway for trying to help. :) Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ You could use this kind of formula structure: =IF(ISNUMBER(A1),A1+27,) (without the quotes). This should do what you want. Joe Conner, Poulsbo, WA USA -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Stuck with 3.3 forever?
2011/12/3 Jay Lozier jsloz...@gmail.com: On 12/03/2011 12:50 PM, Johnny Rosenberg wrote: 2011/12/3 Pedropedl...@gmail.com: Johnny Rosenberg wrote Depending on the value in other cells, the result can be a time or an empty string (). In this case, E9 is an empty string, so E10 becomes ”#VALUE!”, and in fact every cell below E10 is also ”#VALUE!” because of this. Can you provide an example file? This used to be a problem but it is fixed in 3.4.4 (at least under Windows) I could, but it's not needed, I think. I just did this simple test in 3.3.4 and 3.4.4: Open LibreOffice Calc from scratch. A1: = A2: =A1+1 In LibreOffice 3.3.4 (Ubuntu 10.10), A2 displays: 1 In 3.4.4 (Ubuntu 11.10), OOO340m1 (Build:402), A2 displays: #VALUE! Try in A1 no data/empty cell and in A2 =A1+1, you will get 1 in A2. Well, I know that, of course. And that's not a part of the problem. The problem occurs when a cell is set to an empty string. Older versions considered the value empty strings to be 0, which was very elegant. That feature now seems to be gone. You are adding a text and number and the + operator is not used for concatenation. Very good, because concatenation is not what I want in this case. This has to do with operator overloading, are other, similar, operations allowed when using '+'. Some may allow it and others may not, restricting its use to mathematical operations only. LO uses the '' operator for concatenation. Which I use all the time when I want concatenation, which I don't in this case. If you use A1 = and A2 = A1 1 you get 1. Yes, but if A1=75, I get 751 and not 76, so that's not the ultimate workaround in this case. The point is that ALL of the cells in a certain column contains a formula that, depending of other cells, give either an empty string as result, or a numerical value. Let's create another example: Let's use A1 for manual input. In A2, enter: =IF(A1=;;A1) In A3, enter: =A2+27 If you enter a number in A1, A3 displays that number+27. If you don't, A3 displays an error message: ”#VALUE!” (I guess; I didn't test this particular example…). I want it to display 27. Thanks anyway for trying to help. :) Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Stuck with 3.3 forever?
2011/12/3 Pedro pedl...@gmail.com: Johnny Rosenberg wrote I could agree with that, but it would have been nice if they thought of that since 1.0 and not changing it on the way, breaking a whole lot of spreadsheets… I see your point. But this was wrong. It's not a change of plans it's simply a correction. And not fixing it would break a lot of other spreadsheets ;) Johnny Rosenberg wrote Is there another way to make A2 look empty in this case, making it useable for calculations? Something like the following: =IF(A1=6;SomethingNiceToPutHere;B1) There are at least two ways: add an extra IF to the following formula and use ISTEXT to validate or replace SomethingNiceToPutHere by 0 (zero) and use conditional formatting to hide the zeros (set the font to White or the cell background color when the value is zero) If you liked the way it worked before you can use =N() as suggested by Miguel Angel. Seems like the N() function is the best solution in this case then. Using a test every time I want to use a cell that MIGHT be an empty string would make my formulas ridiculously long, and most of them are quite long already. Another solution would of course be to create a new cell formula with LibreOffice Basic, that takes care of everything, but I guess it would be rather slow, which I think would be noticeable in my case since I we are talking about thousands of rows here. The answer I was hoping for in this case, about if there was a way to make a cell look empty, was that there is maybe some function to use or something, something like EMPTYCELL(): =IF(Something;EMTYCELL();AnotherCell+AThirdCell) Which is not a good idea, because if I empty the cell, I guess its formula will be erased too… :P Well, never mind, I think I'll go with N() anyway now. Short and simple. Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Stuck with 3.3 forever?
Johnny Rosenberg wrote Depending on the value in other cells, the result can be a time or an empty string (). In this case, E9 is an empty string, so E10 becomes ”#VALUE!”, and in fact every cell below E10 is also ”#VALUE!” because of this. Can you provide an example file? This used to be a problem but it is fixed in 3.4.4 (at least under Windows) -- View this message in context: http://nabble.documentfoundation.org/Stuck-with-3-3-forever-tp3557428p3557454.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Stuck with 3.3 forever?
2011/12/3 Pedro pedl...@gmail.com: Johnny Rosenberg wrote Depending on the value in other cells, the result can be a time or an empty string (). In this case, E9 is an empty string, so E10 becomes ”#VALUE!”, and in fact every cell below E10 is also ”#VALUE!” because of this. Can you provide an example file? This used to be a problem but it is fixed in 3.4.4 (at least under Windows) I could, but it's not needed, I think. I just did this simple test in 3.3.4 and 3.4.4: Open LibreOffice Calc from scratch. A1: = A2: =A1+1 In LibreOffice 3.3.4 (Ubuntu 10.10), A2 displays: 1 In 3.4.4 (Ubuntu 11.10), OOO340m1 (Build:402), A2 displays: #VALUE! Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- View this message in context: http://nabble.documentfoundation.org/Stuck-with-3-3-forever-tp3557428p3557454.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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 -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Stuck with 3.3 forever?
Johnny Rosenberg wrote I could, but it's not needed, I think. I just did this simple test in 3.3.4 and 3.4.4: Open LibreOffice Calc from scratch. A1: = A2: =A1+1 In LibreOffice 3.3.4 (Ubuntu 10.10), A2 displays: 1 In 3.4.4 (Ubuntu 11.10), OOO340m1 (Build:402), A2 displays: #VALUE! That is expected. Cell A1 contains an empty text string (not a value). When you try to do a calculation with a text string you get an error. MS Excel also displays #VALUE TBH I think it was version 3.3.4 that was wrong... -- View this message in context: http://nabble.documentfoundation.org/Stuck-with-3-3-forever-tp3557428p3557511.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Stuck with 3.3 forever?
I agree with Pedro. A null text is not to be treated as a numeric zero. An empty cell may be calculated as a zero but a null text is not an empty cell. If have found the #VALUE result of mixing null text within a calculation to be a good debugging and alert technique that the values elsewhere in my spreadsheet have become messed up. - -- David S. Crampton -- View this message in context: http://nabble.documentfoundation.org/Stuck-with-3-3-forever-tp3557428p3557547.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Stuck with 3.3 forever?
2011/12/3 David S. Crampton david_cramp...@ie2b.com: I agree with Pedro. A null text is not to be treated as a numeric zero. An empty cell may be calculated as a zero but a null text is not an empty cell. If have found the #VALUE result of mixing null text within a calculation to be a good debugging and alert technique that the values elsewhere in my spreadsheet have become messed up. I could agree with that, but it would have been nice if they thought of that since 1.0 and not changing it on the way, breaking a whole lot of spreadsheets… Let's assume we have this example: A2: =IF(A1=6;;B1) Now, in some cases, A2 will be an empty string, so =A2+1 will fail in another cell, right? Is there another way to make A2 look empty in this case, making it useable for calculations? Something like the following: =IF(A1=6;SomethingNiceToPutHere;B1) Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ - -- David S. Crampton -- View this message in context: http://nabble.documentfoundation.org/Stuck-with-3-3-forever-tp3557428p3557547.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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 -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Stuck with 3.3 forever?
On 12/03/2011 12:50 PM, Johnny Rosenberg wrote: 2011/12/3 Pedropedl...@gmail.com: Johnny Rosenberg wrote Depending on the value in other cells, the result can be a time or an empty string (). In this case, E9 is an empty string, so E10 becomes ”#VALUE!”, and in fact every cell below E10 is also ”#VALUE!” because of this. Can you provide an example file? This used to be a problem but it is fixed in 3.4.4 (at least under Windows) I could, but it's not needed, I think. I just did this simple test in 3.3.4 and 3.4.4: Open LibreOffice Calc from scratch. A1: = A2: =A1+1 In LibreOffice 3.3.4 (Ubuntu 10.10), A2 displays: 1 In 3.4.4 (Ubuntu 11.10), OOO340m1 (Build:402), A2 displays: #VALUE! Try in A1 no data/empty cell and in A2 =A1+1, you will get 1 in A2. You are adding a text and number and the + operator is not used for concatenation. This has to do with operator overloading, are other, similar, operations allowed when using '+'. Some may allow it and others may not, restricting its use to mathematical operations only. LO uses the '' operator for concatenation. If you use A1 = and A2 = A1 1 you get 1. Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- View this message in context: http://nabble.documentfoundation.org/Stuck-with-3-3-forever-tp3557428p3557454.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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 -- Jay Lozier jsloz...@gmail.com -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Stuck with 3.3 forever?
Johnny Rosenberg wrote I could agree with that, but it would have been nice if they thought of that since 1.0 and not changing it on the way, breaking a whole lot of spreadsheets… I see your point. But this was wrong. It's not a change of plans it's simply a correction. And not fixing it would break a lot of other spreadsheets ;) Johnny Rosenberg wrote Is there another way to make A2 look empty in this case, making it useable for calculations? Something like the following: =IF(A1=6;SomethingNiceToPutHere;B1) There are at least two ways: add an extra IF to the following formula and use ISTEXT to validate or replace SomethingNiceToPutHere by 0 (zero) and use conditional formatting to hide the zeros (set the font to White or the cell background color when the value is zero) If you liked the way it worked before you can use =N() as suggested by Miguel Angel. -- View this message in context: http://nabble.documentfoundation.org/Stuck-with-3-3-forever-tp3557428p3557738.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Stuck with 3.3 forever?
Expanding a bit on Jonny's concatenation: A1: (null string) A2: 1 (numeric) A3: =A1+A2 displays #VALUE A4: =A1 A2 displays left-aligned 1. It is a text result. A5: =A2 + A4 displays a right-aligned 2. It is a numeric result. Ergo, the + operator gave #VALUE result when combining a null string and a numeric but gives a numeric when combining a text string which can be interpreted as a numeric with a numeric. Per my mini-sermon (earlier today) on debugging and preferring strict type-based behavior, I would prefer A5 to also display #VALUE. -- David S. Crampton On Sat, 03 Dec 2011 12:08:20 -0800, Jay Lozier jsloz...@gmail.com wrote: On 12/03/2011 12:50 PM, Johnny Rosenberg wrote: 2011/12/3 Pedropedl...@gmail.com: Johnny Rosenberg wrote Depending on the value in other cells, the result can be a time or an empty string (). In this case, E9 is an empty string, so E10 becomes ”#VALUE!”, and in fact every cell below E10 is also ”#VALUE!” because of this. Can you provide an example file? This used to be a problem but it is fixed in 3.4.4 (at least under Windows) I could, but it's not needed, I think. I just did this simple test in 3.3.4 and 3.4.4: Open LibreOffice Calc from scratch. A1: = A2: =A1+1 In LibreOffice 3.3.4 (Ubuntu 10.10), A2 displays: 1 In 3.4.4 (Ubuntu 11.10), OOO340m1 (Build:402), A2 displays: #VALUE! Try in A1 no data/empty cell and in A2 =A1+1, you will get 1 in A2. You are adding a text and number and the + operator is not used for concatenation. This has to do with operator overloading, are other, similar, operations allowed when using '+'. Some may allow it and others may not, restricting its use to mathematical operations only. LO uses the '' operator for concatenation. If you use A1 = and A2 = A1 1 you get 1. Kind regards Johnny Rosenberg ジョニー・ローゼンバーグ -- View this message in context: http://nabble.documentfoundation.org/Stuck-with-3-3-forever-tp3557428p3557454.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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 -- For unsubscribe instructions e-mail to: users+h...@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
[libreoffice-users] Re: Stuck with 3.3 forever?
David S. Crampton wrote Per my mini-sermon (earlier today) on debugging and preferring strict type-based behavior, I would prefer A5 to also display #VALUE. Obviously! If it behaves differently it's simply inconsistent. It the user wants to change that he just has to do =A2+VALUE(A4) If there is a voting somewhere you can count +1 on the strict (coherent, consistent) behavior. -- View this message in context: http://nabble.documentfoundation.org/Stuck-with-3-3-forever-tp3557428p3558054.html Sent from the Users mailing list archive at Nabble.com. -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Stuck with 3.3 forever?
El 03/12/11 22:54, Pedro escribi:David S. Crampton wrotePer my mini-sermon (earlier today) on debugging and preferring strict type-based behavior, I would prefer A5 to also display #VALUE.Obviously! If it behaves differently it's simply inconsistent. It the user wants to change that he just has to do =A2+VALUE(A4) If there is a voting somewhere you can count +1 on the strict (coherent, consistent) behavior.Also mine.But interpret text as number is only with direct reference, not for example in the middle of a SUM().Miguelngel.. -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Stuck with 3.3 forever?
El 03/12/11 22:54, Pedro escribió: David S. Crampton wrote Per my mini-sermon (earlier today) on debugging and preferring strict type-based behavior, I would prefer A5 to also display #VALUE. Obviously! If it behaves differently it's simply inconsistent. It the user wants to change that he just has to do =A2+VALUE(A4) If there is a voting somewhere you can count +1 on the strict (coherent, consistent) behavior. Also mine. But interpret text as number is only with direct reference, not for example in the middle of a SUM(). Miguel Ángel. . -- For unsubscribe instructions e-mail to: users+h...@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
Re: [libreoffice-users] Re: Stuck with 3.3 forever?
On 12/03/2011 04:39 PM, David S. Crampton wrote: Expanding a bit on Jonny's concatenation: A1: (null string) A2: 1 (numeric) A3: =A1+A2 displays #VALUE A4: =A1 A2 displays left-aligned 1. It is a text result. A5: =A2 + A4 displays a right-aligned 2. It is a numeric result. Ergo, the + operator gave #VALUE result when combining a null string and a numeric but gives a numeric when combining a text string which can be interpreted as a numeric with a numeric. Per my mini-sermon (earlier today) on debugging and preferring strict type-based behavior, I would prefer A5 to also display #VALUE. The default behavior appears to be an implicit type conversion of string to a numeric data type when it is possible. Either to an integer or decimal as needed. I tried your test using 1.356 (US format) in A2 and got A# = #VALUE A4 = 1.356 as string (left justified) A5 = 2.356 as decimal (right justified) If the there is any character that can not implicitly convert to a number, an error is thrown. This probably dates back to the original spreadsheets (Visicalc and Lotus 123). For consistency one must follow some of the original, even if stupid, decisions so the average user is not confused and spreadsheets can be easily imported into other spreadsheet programs. -- Jay Lozier jsloz...@gmail.com -- For unsubscribe instructions e-mail to: users+h...@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
