Issue 109721

Reproducible with "Ooo-Dev 3.3.0 multilingual version German UI WIN XP:
[DEV300m71 (Build 9481)]"! 

Pls see my "sample.ods", what contains 3 cells 'A1:A3' with text formatting and
text contents and 3 formulas (add 1 to value of A-cell) in 'B1:B3'
With 3.1 all 'A' cells are calculated with value "0"

In 3.3. Formatting for a-Cells still is shown as "text", but
- Text content looking as numbers is used as number. 
  That looks like a feature, but i dislike it. My opinion: You can't 
  calulate with text, and so error message "#VALUE!" should be shown.
- In 3.3. a text like "SSS" will no longer be calculated with value "0", but
  it will be indicated as an illegal "impurity" for a numeric calculation.
  That's consequent, it was a bug in former versions not to show the error
  message.

If a user had text strings in his calculations and trusted to use as "0" he did
it on his own risk.

Of course, a "3.1 compatibility mode" that calculates test strings as "0" would
have been great, but I don't believe that an enhancement request wolud be useful
- a solution would come too late.

Pls see comments and links in Issue 109165 !

*** This issue has been marked as a duplicate of 109165 ***

Created attachment 68093 [details]
Sample document

.

Any non-numeric text in the range of cells used in of numeric formula should be
taken as zero, as is the past practice of OpenOffice.org Calc (up to and
including OOo 3.1.x.)
This practice has been followed by other spreadsheet programs as well.
A typical use is entering "n/a" in fields of a table where a non-zero value is
not appropriate, but other text could be entered as well.
(For example, the equivalent in french would be "s/o".)
Changing this breaks the functionality of OOo.

Having seen the attachment, the change interpreting text-formated cells as
numbers is reasonable -- as it does look the same.
(This removes errors resulting only from unintended text format.)
That isn't the problem.
The problem is treating non-numeric text as an error, instead of the traditional
treatment as zero.

By the way, in many programming languages, numeric conversion functions treat a
non-numeric string as zero -- not an error.
It is an entirely reasonable result.
Changing the function in OOo should be done for some compelling reason, not on a
whim.

Think about it for a moment.
What is the point of producing an error message ?
All that does is cancel all the calculations of any formulas directly or
indirectly using the cell containing non-numeric text.
Entering such text in a cell would most probably be deliberate.
Entering an incorrect number would produce invalid results, but NOT give an
error message.  A much more likely occurance.

Reopening this issue as the other issue was not clearly explained (only by the
contents of an attachment), and has been subsequently closed.

@andr55:
We all understood Issue 109165, and this one is a DUP of that one. It's not a
good idea always to file a new issue only because one does not agree with the
arguments in the comments of a closed issue. So I close this issue again as DUP.

If you see good reasons to rethink about WONTFIX decision, please comment in
Issue 109165 with facts (not "other spreadsheets", but "'EXCEL 2003', '...',
'...') and a weighting comparing with the performance arguments there) and wait
for further comments, before you reopen it!

*** This issue has been marked as a duplicate of 109165 ***

@rainerbielefeld
My issue is not well defined by "Behavior of calculation with text cell.",
whatever that is supposed to mean.
Your argument about filing a new issue because one doesn't agree with the result
of this other issue is incomprehensible.  The other issue is extremely poorly
defined.


What specific version of what other spreadsheet is unimportant to the discussion.
(One could start with OOo 1.x, 2,x, 3.0, and 3.1, as well as Quattro Pro, among
others.)
The point is that if a user chooses to put text, such as "n/a", in the place of
zero, for cells to be excluded from calculation in a range used in a formula,
that is perfectly legitimate.
The intent of course, is not to calculate with text, but to ignore the cell for
purposes of calculation, and use the text entered for clarification.
(Also used in at least some examples for OOo.)

The change from OOo 3.1 to OOo 3.2 was to make cells which appeared to be
numbers, but were formated as text, to be calculated as numbers.
Unfortunately included was the additional change to make non-numeric text in a
calculation range an error.
That is my issue.

(Note that there is an anomoly common to many spreadsheets, including OOo.  The
value entered is normally dynamically typed, but if a cell has a non-default
format, and there is no current value in the cell, the new value entered is
stored, if possible, in the pre-existing format.) 

Examples where one would reasonably put text in the place of zero :
1) Spreadsheet which calculates income tax returns.
Fields which do not apply to the individual are entered as "n/a" for clarity.
Fields awaiting more info could have an appropriate comment.
Meanwhile, a result would be shown based on the other entries, which could
indicate that the missing entries are not necessary.

2) A table where the columns used for a line vary according to the type of info.
On a particular line, it is appropriate to enter something like "n/a" in unused
cells.  (This could be done automatically, according to the value of another field.)

3) I'm sure that you can think of something.


Programming languages that convert non-numeric text to zero in conversion
functions :
This is the case in most versions of Basic, in whatever operating environnement.
(Such as OOo Basic, where also 
  print 1+"text"+3
prints 4).

(Until this issue is corrected, forced to return to OOo 3.1.1)

You can use the function SUM instead of the operator +; then text is ignored.
You find the same feature using function PRODUCT and operator *.
If you use functions like AVERAGE or SUMXMY2, text is ignored.

@regina
thanks for the workaround
I'll reinstall OOo 3.2 and try it.

The workaround works !!!
The SUM function does ignore cells containing text.

For coherence, using + and SUM() should work the same.
Even though one could use only SUM, logically they have the same function and
users would expect them to work the same.
(As well, often + and SUM are used together.)

In summary, although there is less urgence with the existance of the workaround,
cells containing (at least non-numeric) text should be ignored in all
calculations, and not be treated as errors.

sum() can work, but :

sum(a1;-a2) will give an error if a2 is (non-numeric) text.

one has to write
sum(a1)-sum(a2)  [or equivalent, with no negative sign before any cell name]

also, for reference to a single cell that might contain text, one has to write
sum(a1)
alone.

That gives some strange-looking formulas to make it work.

Note that if one uses a language other than english, sum() is translated.
e.g. it is somme() in french.

*** Issue 109721 has been confirmed by votes. ***

Cool. 

I really doubt that 3 voters (jtienhaara told 5 minutes after his action that he
does not want to use OOo any longer) make this problem a bug. 

I agree, a "compatibility mode" for old OOo documents and EXCEL spreadsheets
might be a good Idea. A solution might be an option check box "switch off
conversion, treat text as "0". 

But will the benefit really legitimate the costs?

SUM(A1;A2) also fails, not just SUM(A1;-A2). It gives Err:508. It doesn't
surprise me that SUM(-A2) fails - I just tested it in Excel and that gives
#VALUE! as well.

It seems to me that treating non-numeric text as a number is an obvious error.
It should be treated as text to be ignored.
(In other words, as having no impact on the calculations in question, equivalent
to a value of zero.)
To generate an error message is treating the text as a number -- albeit an
invalid number.
Since non-numeric text would not appear to be a number, there is no reason to
expect that the user would want -- or expect -- the text to change in any
respect the result of the calculations.
But creating an error *does* change the result of the calculations.

It is however, entirely reasonable to treat text that converts to a valid number
as a number instead of text, since visually it would appear to be a number.

A poorly conceived change in function should be recognised as a defect to be
corrected, rather than an enhancement.

Agreed - whoever came up with this is seriously disconnected with how
spreadsheets get used.  Please remove this "enhancement" defect and restore
correct functionality ASAP!  Non numeric entries in a cell that is included
either as a range function or in a formula that expects it to have a number
should be treated as ZERO, not generate errors!

I think I will have to revise my opinion. 
This is a real bug and problem.
I am using CALC for business calculations (quotations, ...), and there i am used
to use comments in the cells like "Option", "unavailable" or similar instead of
prices in some cells. These comments appear in the final quotation, and for all
SUM calculations the cells will be counted with value "0", what's ok for this
application.
Starting with 3.2. I will no longer be able to use CALC in the way I am used to
because of the new behaviour, pls. see attached example "quotation.ods", what
seems to be a typical CALC application.

I can not confirm that regina's workaround is a solution with "Ooo-Dev 3.3
multilingual version English UI WIN XP: [DEV300m83 (Build 9511)]", pls see
attached "quotation.ods" with 3.3. screenshot.

If it would be my decision, I would renounce the text to number conversion in
favor of compatibility to existing spreadsheets, EXCEL compatibility and usability.

Created attachment 70232 [details]
Comments from rainerbielefeld Sat Jun 26

@rainerbielefeld: The workaround is to use a function instead of an operator. So
using PRODUCT(F20;B20) instead of F20*B20 works the same way as using SUM
instead of operator +. Please try it. 

@regina:
Can you please check attached "quotation.ods"? SUM did not work for me.

I was wrong, the workaround using "=PODUCT" and "=SUM" instead of multiplication
or addition formulas works fine.

No, the CORRECT workaround is to restore the previous way the program operates!
 Thousands of users should not have to rewrite tens of thousands of spreadsheets!

@regina
you're doing a great job of finding workarounds,
but I think everyone here would really appreciate it if you could give this bug
2 votes -- in case you haven't already :)