Issue 35809 - Text evaluated as zero in formulae
Summary: Text evaluated as zero in formulae
Status: CLOSED DUPLICATE of issue 5658
Alias: None
Product: Calc
Classification: Application
Component: ui (show other issues)
Version: OOo 1.1.3
Hardware: PC Windows XP
: P4 Trivial (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
URL:
Keywords: oooqa
Depends on:
Blocks:
 
Reported: 2004-10-19 17:04 UTC by groundczero
Modified: 2004-10-20 10:21 UTC (History)
2 users (show)

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

Attachments
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description groundczero 2004-10-19 17:04:53 UTC 
This is under Windows XP Pro SP2 and OOo1.1.3

Steps:
1) enter 22 in cell A1
2) enter "a" in cell B1
3) in cell C1, enter formula =A1/B1

This gives you an error 503 instead of an invalid input
error.  

4) in cell D1, enter formula =22/a

This gives a #REF error which is different than when you select 
the numbers in cells.

5) in cell F1, enter formula =A1*B1

This evaluates to zero.

6) in cell E1, enter formula =22*a

This gives an invalid input error which is what you should
have gotten for the other three.
Comment 1 sjb90 2004-10-20 04:43:19 UTC 
Hi groundczero: I would tend to agree here. For the development team, here is a
comparison of the way that OOo (both 1.1.3 and m56) and MS Excel (2000) handle
these situations. MS Excel seems to give more sensible errors in each case:

A1=22 [a number]
B1=a  [text]

FORMULA       OOo         EXCEL
--------------------------------
=A1/B1        Err:503     #VALUE
=22/a         #REF        #NAME
=A1*B1        0           #VALUE
=22*a         #REF        #NAME

According to OOo help, #VALUE should be displayed when a cell that is referenced
in the formula contains text instead of a number, and so this seems most
appropriate in cases 1 and 3. As you indicate, it appears that the text is being
evaluated as a zero value by Calc.

#NAME should be displayed when no valid reference / column or row label is
found. However, cases 2 and 4 are more debatable -- at least #REF indicates that
the user may have entered an incomplete cell reference.

Have changed subcomponent to the more general 'ui', and modified the summary
slightly -- hope that's ok.
Comment 2 frank 2004-10-20 10:21:07 UTC 
Hi,

Point 1) Err:503 vs #VALUE : The Error 503 (Illegal floating point operation) is
much more productive than #VALUE as you can much easier determin what's going
wrong. VALUE just states that there is a problem with the calculation but the
Error message (in the statusbar) just tells you that you try to calculate with a
wrong type of data.

Point 2) #REF vs #NAME : a single 'A' is just a broken Refference, so the 'REF
is more appropriate than #NAME.

Point 3) This is a double to Issue 5658

Point 4) It's the same as Point 2)

Frank

*** This issue has been marked as a duplicate of 5658 ***
Comment 3 frank 2004-10-20 10:21:24 UTC 
closed double