Sentence “=B1+C1” in the cell A1 and both B1 and C1 are empty or have counts, its OK. I push “space bar"either in cells B1 or C1 it is returned in to the cell A1; #ARVO! (in english?; =#VALID VALUE!). Sentence “=SUM(B1;C1)” runs OK returning value 0 to A1, though space bar is pushed.
Sentence in A1; “=IF(B1=””;"";B1*C1)" worked well in OpenOffice. I like cell A1 is empty if B1 is empty. Again its OK if “space bar” is not pushed either in B1 or C1. If I “cut” B1 and C1, A1 returnes empty (unlike space bar; #ARVO!).
The question is; how I can keep end result cell stay empty though factor cell is “space bar pushed”?
I solved the problem how to bypass it. Choose>Tools>Settings>LibreOfficeCalc>Formula>dot for Adjust (Converting text to numbers a.s.o…)>Specifics…>x for The value of the blanco string is zero. Formula; =IF(A1B1=0;"";A1B1) Works Yess! Words are translated from finnish LibreOffice. The text may be idicative
Must be A1xB1. Multiplier signs has disappeared.
Any text content or formula result is either of type ‘Number’ or of type ‘Text’ or of type ‘Error’
SUM() definitely ignores text. As any function (except a few specialized ones) it propagates the ‘Error’ type.
Adding with the operator + behaves differently: It expects operands of type ‘Number’, but if it finds a ‘Text’ it checks whether that text is “numeric” or not. If “numeric” the text is converted to ‘Number’ on the fly and included with the calculation. Otherwise the #VALUE! error is returned.
Just a simple example:
“123”, if present as a text (string) is a numeric text, “one” is not.
="123 + “123” will not return any ‘Text’ and also not an error, but the ‘Number’ 246.
This is independent of whether the respective string is entered directly - into a cell formatted to not recognize numbers probably - or was returned by a formula or a simple reference.
This kind of working implies some guessing about what is expected by an assumed user.
Concerning empty (better: blank) cells Calc takes them either as ‘Number’ 0 or as ‘Text’ "" depending on the context.
"" as an operand in an arithmetic expression is judged to be not “numeric” and therefore causes an error. Same with " ".
You’re right. It’s a mess. I can’t change it. The assumed user expects it this way I’m told.
="123 + “123” will not return any ‘Text’ and also not an error, but the ‘Number’ 246.
That depends on the Detailed Calculation Settings for conversion from text to number under Tools → Options → Calc → Formula. Best option is “Generate #VALUE! error” to spot such errors early, whereas consumers of Excel documents may prefer the worst of all options “Convert also locale dependent”. YMMV.
See help.
Thanks.
Well, it seems the worst of all options is chosen by default.
__
No matter. The “Worst of all possible settings” seems to be a general principle with configurable software, next to always thoroughly regared by the admins. In evidence see the “date and time formats” used by this askbot site. The only issue with that principle seems to be that there once in a whil is one who found an even worse variant. The sport might apply for Olympic games.
We won’t recover. Prognosis is infaust.
The worst of all options is the default because that mimics the Excel behaviour users are used to and otherwise complain it doesn’t work as in Excel.
I found the same as erAck but diffent words because of translate. Thank´s for all.
Simple solution
Try =IF(TRIM(B1)="";"";B1*C1)
Know what you are doing!
Your habit of clearing a value by spacebar is a potential source for error. A space is not “nothing”. You can’t see it by itself, but it is there. NEVER use the space bar to “clear” a cell. Always use keys backspace or delete to remove content.
Calc will interpret a cell containing text and allow it in number context only in very special cases. Single space character is not one of those cases. It is a mess, as Lupp has elaborated good and well.
Hence, you need to know what type(s) of data you are working with.
The formula =B1+C1 is interpreted as “There are numbers in cell B1 and C1. Add them!” If one of the cells contain something not allowed in number context, Calc says “nope” and throws an error. This is by design. If one of the cells contain the text “123”, it will be interpreted to the number 123 and included in the sum, because the formula insists that it be a number. An empty cell will add nothing, and an error will propagate to the result.
The formula =SUM(B1;C1) is interpreted as “There may be numbers in the given cells. Add them up!” Cells containing numbers will be included in the sum. Cells containing text will be discarded. So, if one cell contains “123” represented as text, it will add nothing to the sum. Again, empty cells will add nothing, and errors will propagate.
A cell with one space will not match the empty string "", but it will match the single character string " "
If you insist on using the spacebar to clear a value, you can compare with that.
Better, use the TRIM() function, which will clear out “excess” spaces (leading, trailing and repeat spaces). This will be more robust, as it handles both empty cells, cells with one space and cells which (accidentally) contains multiple spaces. Hence, the simple solution above. If you still made it this far, good on you!
if one cell contains “123” represented as text, it will count as the number zero.
No, it is completely ignored. That doesn’t matter for the SUM() function but makes a difference in the AVERAGE() function, for example.
The help had that wrong as well but is fixed meanwhile for the next release.
@erAck: You are right of course. Thanks!
Edited my answer to reflect true behavior better, hopefully without muddying the case in point.