Issue 79752 - Forumla calculation if cel format is text
Summary: Forumla calculation if cel format is text
Status: CLOSED DUPLICATE of issue 5658
Alias: None
Product: Calc
Classification: Application
Component: code (show other issues)
Version: OOo 2.2.1
Hardware: PC Windows XP
: P4 Trivial (vote)
Target Milestone: ---
Assignee: spreadsheet
QA Contact: issues@sc
Depends on:
Reported: 2007-07-18 12:28 UTC by karlis
Modified: 2007-07-18 12:51 UTC (History)
1 user (show)

See Also:
Latest Confirmation in: ---
Developer Difficulty: ---

example (62.50 KB, application/
2007-07-18 12:30 UTC, karlis
no flags Details

Note You need to log in before you can comment on or make changes to this issue.
Description karlis 2007-07-18 12:28:57 UTC
Cell A1 = 1. Format of cell is "text" (@).
Cell B1 = 1. Format of cell is "standard" (or number).
Cell C1 = A1*B1. Returns value of 0,00.
If any of the cells used in the formula are formatted as text, then formula
returns a result of 0,00.

This behaviuor is wrong. The result should either take into account numeric
values of the cells, despite their formatting or report an error.
MS Excel uses numeric values of the cells for the calculation of formulas,
ignoring the formatting. Thus a file, prepared in Excel can have different
results of the formulas, if opened in OOO and the creator has been careless
enough to mess with formatting of the cells.
Such discrepancies are very hard to detect if they are just silently ignored as
currently and can lead to very serious problems where people exchange files
between the two spreadsheet applications.
Comment 1 karlis 2007-07-18 12:30:18 UTC
Created attachment 46884 [details]
Comment 2 frank 2007-07-18 12:51:15 UTC

text is text and you can't calculate with text. So the convention is to set text
to the value zero. So Calc acts correctly.

Please use the search functionality of Issuezilla before submitting Issues. This
is a double to a lot of Issues, especially Issue 5658 .

For finding such formatted cells, use the Value Highlighting feature which can
be reached with CTRL+F8 . Blue is a number, black a text and green are formula

*** This issue has been marked as a duplicate of 5658 ***
Comment 3 frank 2007-07-18 12:51:37 UTC
closed double