Created attachment 29085 [details]
Block formatting and calculation example spreadsheet

Open Office Build 1.9.125
Complete Installation
OS: Windows XP Pro SP2
Platform: PC

We managed to successfully replicate this issue.

Replication:

1) Format an empty cell (cell X) to Text
2) Input a number into cell X, for example the value 2
3) Input an equation a third cell (cell Z), similar to the following “=X+3â€

If step 2 is done before step 1 then the operation works as expected meaning the
value in cell Z is 5.

Will attach a “Test case issue 53833.ods†that demonstrates the replication steps.

Problem:
The result treats the text cell X as 0, so for our example the result should
show 5, but it shows 3.

Additional problem, possibly a separate issue:

Furthermore when the cell X‘s format is changed from text to number the problem
is not fixed. In the spreadsheet itself it shows as a number, but in the
equation cell a single quote (‘) is present in front of the number. In out
example cell X formatted back to number type would have a value of 2, but when
clicked on and viewed in the equation editor it shows up as ‘2.

Why it’s an issue:

Similar problems have been dismissed in the past see issue 27241 and issue
27241. In general the problem shouldn’t be encountered very often through normal
use. When steps 1 through 3 is done in Excel the operation yield a proper
output, meaning they perform the operation on the value regardless of input
type. Hence,  when moving from Microsoft based Excel sheets to Open Office’s,
creates a problem in migrating files, since the Excel sheet no longer works in
Open Office’s spread sheet (Calc) as it did originally in Microsoft Excel.

Further Testing:

Use a different math operation -, *, /, SUM etc.
Use another cells as input instead of the value 3 we use.
Import Excel documents into Calc that use text type cells to perform operations.

Steve Wu and Jacek Leowski

Created attachment 29302 [details]
Test case

One more thing in andy999's attachment changing C2 type to number doesn't
correct the calculation failure.
See:
"Additional problem, possibly a separate issue"

in my comment from Sat Sep 3 17:57:17 -0700 200

The behaviour is not a bug and sure not priority P2. But it is a well known
problem for those who exchange with Excel. If it is importend for you, vote on 5658.

You can see whether digits are numbers or text, if you turn on View -> Value
Highlighting.

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

Regina, I have reopened this for review as I think you may have have
misunderstood this specific issue and the severity of the problem.  In summary;

* Issue 5658 deals with importing an XLS file and calculating the results of a
text cell.  No formatting changes are made.

* This issue (issue 53833) deals with an OpenDocument spreadsheet -- created
from-scratch and not necessarily saved.  Block formatting changes from text to
numeric are ignored.

Therefor, this issue is not a duplicate of issue 5658.

The first note I posted and the follow on notes by Steve and Jacek document the
steps required to reproduce this problem.  

To eliminate any potential confusion, here are the exact steps you can perform
to see that this is not the same issue as issue 5658;

* Open a new Calc spreadsheet.

* Click on cell A2.  

* Change the formatting of A2 to "text, @".

* Click on cell B2.

* Verify format of B2 is "numeric, general".

* Click on C2.

* Enter the formula "=sum(a2:b2)".

* Click on A2.

* Enter 1 for the value.  (NOTE: The value in the Input Line is just 1 not '1 or
similar.)

* Click on B2.

* Enter 2 for the value.

* Click on another cell.  (This will commit the value of 2 in B2.)

* C2 shows a calculated value of 2.

* IMPORTANT: Select both A2 and B2 at the same time.

* Bring up the format dialog.  It shows "numeric, general".

* Click OK to issue the change.

* C2 shows a calculated value of 2.  It should have been 3!

* The format of A2 is still "text, @" not "numeric, general" as Calc was
instructed to do.

Note: Selecting column A and changing the formatting also has no impact on cell
A2 being in "text, @" format.

In summary: selecting a block and changing the format of the block from text to
numeric does not change the cell from text to numeric -- the cell remains text.  

Please read through the steps required to reproduce this as there are specific
conditions required to see this problem and they are easy to miss.

This is a high or critical issue by definition;

* It is a break in functionality.

-- Changing formatting from text to numeric does not work over blocks.(!!!)

* The defect can easily be missed by both novice and expert; it is not a
cosmetic or medium problem.

-- Block formatting is something that should reliable, and in this case it is
ignored.  On moderately complex sheets, it is not reasonable that a novice or
even an expert would notice that the calculation is incorrect.

* Data loss or a miscalculation is the result.

-- Unlike issue 5658, this is a formatting problem but the results still are
that the calculation performed is not what Calc is instructed to perform.

As such, a higher priority (top or second from top) is appropriate.  (I tend to
use 4 severity levels -- ISO, IEEE, and IBM use similar definitions -- instead
of the 5 used here, and under those this would be a critical aka 'showstopper'
as data loss / miscalculation is involved and the workaround can not be expected
to be performed by experts consistently let alone casual users.)

Hi andy999,

your are right, I missed your original request, because severeal issues are
mixed here. I hope I get it now.

It seems that the main problem to you is, that OOo doesn't change the format for
some cells in a marked range. But OOo works here as designed. You don't like
this behaviour, so your issue would be a request for enhancement. But you need
no new issue, please look at issue 26225. I think it fits to your wish.

Your comment in the attached document is wrong. The entry 222 will not change to
a number, when A2 gets the format "number - standard", but it will get an
apostroph in front and remains text (German 1.9.125 on WinXP). OOo was designed
to work this way, so no bug. Issue 23348 handles the fitting request for
enhancement.

The third thing is, that OOo doesn't calculates with texts, but that is issue
27241 and 5658, as you already said.

Altogether there is no real new issue here and you should close it therefore.

Regina, as suggested I've voted for related issue 26225.

While I disagree on this being a feature request, I'll leave it as is and allow
you or others to close it.  (I don't have access.)

* Current behavior is not standard for any other application that has a block
format option.

* Current behavior can lead to data loss; it's a bug not a feature.

Hi andy999,

sorry to say, but you're wrong. Changing a2 to format numbers doesn't make the
text a number as the possibity exist that this 'number' is supposed to be a
text. To make it a real number you have to remove the apostrophe in front of the
number to make it a 'real' number.

All other stuff is covered by the other Issues.

Frank

closed invalid

"sorry to say, but you're wrong. Changing a2 to format numbers doesn't make the
text a number as the possibity exist that this 'number' is supposed to be a
text. To make it a real number you have to remove the apostrophe in front of the
number to make it a 'real' number."

Your're right.  You're right.  A few things puzzle me, though.  As you have
thought out this so throughly, maybe you could fill in the blanks for me?

1. Why are numeric fields that are changed to text fields kept as numeric fields
for calculations?

2. If a numeric cell contains a formula using a reference to a non-numeric cell,
why isn't an error message generated?

Hi,

to ask your questions it wasn't necessary to re-open this Issue.

1.) It's the same as for text cells converted to numbers. The first used cell
format rules as long as the cell isn't Edited in the way that you've entered it
by either doubleclicking it or using F2 and change something and close the cell.
Entering new content to the cell will change it immediately.

2.) Because text cells have the value zero.

Frank

closed invalid

>>1. Why are numeric fields that are changed to text fields kept as numeric
fields for calculations?

>"1.) It's the same as for text cells converted to numbers. The first used cell
format rules as long as the cell isn't Edited in the way that you've entered it
by either doubleclicking it or using F2 and change something and close the cell.
Entering new content to the cell will change it immediately.

So, the true value of the cell is invisible to the user?

>>Q. 2. If a numeric cell contains a formula using a reference to a non-numeric
cell, why isn't an error message generated?

>"2.) Because text cells have the value zero."

The cells don't contain zero.  They contain the value entered, and if that's
anything but null it's something, numeric or not.  (Note, I'm not talking about
any invisible-to-the-user values, but what they can see.)

[[Side note: I've just this week had to explain to my boss and a co-worker that
the highlighting in an OOo text file when saved as a Word .doc can't be removed
with the highlighting tool in Word since Word has 2 levels of highlighting and
the highlighting tool in Word doesn't act on the same level OOo saved (OOo has 1
level of highlighting).  ((Checked the DB here, and just like this report, the
highlighting issue isn't technically a defect in OOo.  In that case it's a
defect in MSO...yet, it's still causing me problems in getting OOo adopted (see
below).  If a calculation doesn't match what Word says (or can't be proven to be
more accurate as Gnumeric is), there's no way I can explain it to the boss.))

This has made me consider putting together a seperate 'OOo to MSO coverter' just
so I don't have to get routinely embarrased even when does things correctly,
though that would probably require me to create a macro for Word or Excel to
clean up the differences, and nobody is going to install and run that macro so
I'll have to do it before exchanging any documents.  An 'insert MS quirks mode
(incompatable)' for saving a file would be a good idea in these situations.  ((A
search of the DB here came up with no hits on this issue, though I might have
used the wrong search paramaters.))

The usual comment after I explain the situation is 'Well, we just can't rely on
that [OpenOffice.org] -- use MSO' or 'why do you insist on using OpenOffice?'. 
Beyond the embarasements, this is a big problem since I can't add
OOo/OpenDocument to requirements documents on the contracts I'm on once people
encounter a few of these these differences no matter if the implementation is
technically correct or not.]]