Issue 27241

Created attachment 14217 [details]
Spreadsheet showing calculation problem

Hi Graham,

what would you expect from textvalue minus textvalue ?

The values in B24 and B25 are text and OOo Calc will not calculate with textvalues.

To achieve what you want, you have at least two options :

1.) userdefined numberformats
2.) converting text to numbers using the value function.

As this is not a bug from OOo Calc, I've to close it as invalid.

Frank

closed invalid

Well, it's incompatible with Excel when reading an Excel file. 

I suppose it depends what you want- but if you want to offer people who use 
Excel an alternative to Excel, then you might want to change the way OOo behaves 
(bug or not).

Look, I am just finished helping a UK local government body migrate 4500 
desktops from Windows (various) and MS Office (various) to Windows XP and Office 
XP, mostly because MS withdrew support from the older versions. I would love 
them to have had a choice, which is why I started using OOo for all the 
documents, including showing them documents and spreadsheets in meetings. I face 
a hopeless task if they cannot take a simple Excel spreadsheet (bugs and all) 
and make it produce the same results in OOo that it does in Excel. Differences 
in laying out documents they might just forgive: "wrong" results in spreadsheets 
will have them run screaming back to Uncle Bill. Even if it's really Excel 
that's wrong, they won't trust OOo.

Best Regards
Graham

Hi Graham,

currently we have a duplicate to this Issue. Initially I thought yours isn't the
same as Issue 5658 because the substitute function always gives a text maybe I
should have known it better. I was misslead by the °C. So normally I had to
close this as double.

On the other hand I think using custom numberformats for the data cells even
would have saved time filling them. Also the need to make a substitution to make
even Excel work as they want would be unnecessary. In my opinion a consultant
should also keep in mind that different products can have different philosophys.
In this case handling of text values. From a mathematicans view, you never can
calculate with text. And OOo is somewhat strict in this point.

Nevertheless we hear our customers and are in the process of deciding how to
handle text values in calculations.

Best regards

Frank

Frank,

It's good to hear that you listen, and I hope we can get this resolved. 

The guy in the other report (5658) was right in saying that OOo currently 
handles this the worst of the three ways it could: silently converting a string 
to a zero value "on principle". 

Even throwing an error would be better (ideally an error that says "can't 
subtract a string from a string"), but best would be what Excel does, seeing if 
the string can be coerced to a number and then using that.

I have a couple more OOo spreadsheet bugs to file, if you're still listening ;-)

How about: "You will notice that Calc shows an incorrect answer here.  The
reason is that Excel tries to guess a value for a text input.  The simple fix is
to mark all the rows and change them to numbers <Trainer demonstrates how to do
that and voilá, the correct values appear>."

Trainer being some kind of OpenOffice Assistant? 

Done right could be attractive, but usually done wrong and then very annoying. 

In this case, the values being subtracted are calculated using eg 
"=SUBSTITUTE(B6,"°C","",1)" so the Trainer would have to convert them to things 
like "=value(SUBSTITUTE(B6,"°C","",1))". 

I much prefer the second method as it informs the user without adopting an
approach that could produce errors (and confusion) in the user.