Issue 27241 - Fails to subtract one cell from another!
Summary: Fails to subtract one cell from another!
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 1.1
Hardware: All All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
Depends on:
Reported: 2004-03-31 13:54 UTC by graham_harris
Modified: 2004-03-31 23:18 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---

Spreadsheet showing calculation problem (18.00 KB, application/
2004-03-31 13:56 UTC, graham_harris
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description graham_harris 2004-03-31 13:54:32 UTC
Attached climate.xls shows only 0 in rows 26 and 31 in yet shows 
non-zero values (correctly it seems) in MS Excel. The formulae for these rows 
are just like "=b25-b24" which should be real easy....
Comment 1 graham_harris 2004-03-31 13:56:36 UTC
Created attachment 14217 [details]
Spreadsheet showing calculation problem
Comment 2 frank 2004-03-31 14:34:54 UTC
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.

Comment 3 frank 2004-03-31 14:35:23 UTC
closed invalid
Comment 4 graham_harris 2004-03-31 15:11:20 UTC
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
Comment 5 frank 2004-03-31 16:11:16 UTC
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

Comment 6 graham_harris 2004-03-31 16:56:38 UTC

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 ;-)
Comment 7 rblackeagle 2004-03-31 19:33:20 UTC
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>."
Comment 8 graham_harris 2004-03-31 20:06:41 UTC
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))". 

Comment 9 rblackeagle 2004-03-31 23:18:31 UTC
I much prefer the second method as it informs the user without adopting an
approach that could produce errors (and confusion) in the user.