OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Calculate with text cells
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Yves42
Newbie
Newbie


Joined: 30 Nov 2005
Posts: 2

PostPosted: Wed Nov 30, 2005 9:55 am    Post subject: Calculate with text cells Reply with quote

Hello

First excuse my English : I hope you will be able to undestand me Embarassed

Very often, Our suppliers send Excel Documents to my company

We are using OpenOffice (1.1.4 and 2.0)

In these documents, some calculations use text cells (cells containing numeric datas but with a text format)

Excel is able to "recognize" the number in the cell even if it's a text cell (for example if a text cell containt +12, Excel will do the calculation with the number 12) so our suppliers don't have any problem

But When we open these documents with OOo, it can't identify the numbers and all calculations using text cells are wrong (0 is using for calculation)

I can't ask our suppliers to change there datasheets, we can't check every cells to verify the results -

Is there a way to make OpenOffice able to calculate with text cells ?

Thanks for your answers
Back to top
View user's profile Send private message
richhill
OOo Advocate
OOo Advocate


Joined: 16 Jun 2004
Posts: 418
Location: Mesa, AZ

PostPosted: Wed Nov 30, 2005 10:11 am    Post subject: Reply with quote

Sounds to me like Excel has a serious problem Laughing

But the VALUE() function may be worth looking at. It converts textual numbers to numeric numbers and leaves numeric numbers alone.

You can do this on the fly inside formulae. Limitation with range notation though.
_________________
OOo Calc tips: http://www.openofficetips.com
Back to top
View user's profile Send private message Visit poster's website
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Wed Nov 30, 2005 10:52 am    Post subject: Reply with quote

richhill wrote:
Sounds to me like Excel has a serious problem Laughing


I am pleased? to report that I just managed to multiply my social insurance number X 12:)

I am now suffering from multiple personalities. Now if I can only collect the 12 pensions due in a few years Smile

I formated a number of cells in Excel as text cells and entered my SIN as a string of 9 digits without the customary formating (999-999-999). Excel reported that this number was formated as text or precided by an appostrophe. It then happily allowed me to multilpy it. However if I format the number it returns a #VALUE and a warning that "A value used in the formula is of the wrong data type".

On the other hand Calc given a text field of 999999999 multiplied by 12 returns 0 with no warning and does exactly the same for 999-999-999. I tried this with and without a apostrophe in the text cell. Can any one duplicate this? If so, I'd say we have a problem.

Addition
I just notice that text +12 = 12.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Nov 30, 2005 12:26 pm    Post subject: Reply with quote

jrkrideau wrote:


On the other hand Calc given a text field of 999999999 multiplied by 12 returns 0 with no warning and does exactly the same for 999-999-999. I tried this with and without a apostrophe in the text cell. Can any one duplicate this? If so, I'd say we have a problem.


Any text-value, used in a numeric context evaluates to zero.
This is what I learned as an experienced Excel-user (and teacher).
No idea, what Excel does in this case. But in general I think they go too far in their preemption, always guessing what the user really means without saying so. Evil or Very Mad
Back to top
View user's profile Send private message
Yves42
Newbie
Newbie


Joined: 30 Nov 2005
Posts: 2

PostPosted: Wed Nov 30, 2005 11:31 pm    Post subject: Reply with quote

Problem for me is that users un my company receive documents with false calculations in the cells and they can't see it without checking all the table to verify that they are no calculations with text cells

No matter if Excel is wrong or not, I cant't ask all ou suppliers using Excel to change it for OOo or modify the documents they are using with many customers

I can't ask the users of my company to check thousands cells in each documents they receive to verify that datas are correct

I've got two solutions :

1. Find a way to make OOo able to calculate the same way as Excel (that is the reason I'm here)

2. Install Excel instead of OOo
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Dec 01, 2005 1:07 am    Post subject: Reply with quote

Hi, Yves
Your point of view is clear and comprehensible. But you asked in a technical forum. Wink
Since you did not specify the details of your problem, you can not expect a technical answer, how to change your data on the fly. This is a delicate step.
Do you want all preceeding "+" of strings to be replaced with "="?
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Thu Dec 01, 2005 6:00 am    Post subject: Reply with quote

Villeroy wrote:
jrkrideau wrote:


On the other hand Calc given a text field of 999999999 multiplied by 12 returns 0 with no warning and does exactly the same for 999-999-999. I tried this with and without a apostrophe in the text cell. Can any one duplicate this? If so, I'd say we have a problem.


Any text-value, used in a numeric context evaluates to zero.
This is what I learned as an experienced Excel-user (and teacher).
No idea, what Excel does in this case. But in general I think they go too far in their preemption, always guessing what the user really means without saying so. Evil or Very Mad


I cannot see any reason why I would want a charater string to evalutate to zero in an arithmetic operations. I must say I am not impressed with either Excel or Calc in this case. An arithmetic operation on a character string should be illegal. I would normally format such things as a SIN or inventory number as text in any analysis or data base to explicitly avoid such an error. Either resultfrom Calc or Excel ,has a very nasty potential to introduce errors in a complex spreadsheet.

Another reason not to do statistics with a spreadsheet.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Dec 01, 2005 7:43 am    Post subject: Reply with quote

jrkrideau wrote:
Villeroy wrote:
jrkrideau wrote:


On the other hand Calc given a text field of 999999999 multiplied by 12 returns 0 with no warning and does exactly the same for 999-999-999. I tried this with and without a apostrophe in the text cell. Can any one duplicate this? If so, I'd say we have a problem.


Any text-value, used in a numeric context evaluates to zero.
This is what I learned as an experienced Excel-user (and teacher).
No idea, what Excel does in this case. But in general I think they go too far in their preemption, always guessing what the user really means without saying so. Evil or Very Mad


I cannot see any reason why I would want a charater string to evalutate to zero in an arithmetic operations. I must say I am not impressed with either Excel or Calc in this case. An arithmetic operation on a character string should be illegal. I would normally format such things as a SIN or inventory number as text in any analysis or data base to explicitly avoid such an error. Either resultfrom Calc or Excel ,has a very nasty potential to introduce errors in a complex spreadsheet.

Another reason not to do statistics with a spreadsheet.

I fully agree with that (especially the statistics issue).
My wife has a windows-PC running right now and I did some quick test, because I became unsure about my last statement.
Excel distinguishes between function-arguments and plain operators:
A1 is 1, A2 is "Foo"
A1+A2 --> #VALUE
SUM(A1:A2)--> 1
Strange, isn't it?
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Thu Dec 01, 2005 11:46 am    Post subject: Reply with quote

Villeroy wrote:
jrkrideau wrote:
Villeroy wrote:
jrkrideau wrote:


On the other hand Calc given a text field of 999999999 multiplied by 12 returns 0 with no warning and does exactly the same for 999-999-999. I tried this with and without a apostrophe in the text cell. Can any one duplicate this? If so, I'd say we have a problem.


Any text-value, used in a numeric context evaluates to zero.
This is what I learned as an experienced Excel-user (and teacher).
No idea, what Excel does in this case. But in general I think they go too far in their preemption, always guessing what the user really means without saying so. Evil or Very Mad


I cannot see any reason why I would want a charater string to evalutate to zero in an arithmetic operations. I must say I am not impressed with either Excel or Calc in this case. An arithmetic operation on a character string should be illegal. I would normally format such things as a SIN or inventory number as text in any analysis or data base to explicitly avoid such an error. Either resultfrom Calc or Excel ,has a very nasty potential to introduce errors in a complex spreadsheet.

Another reason not to do statistics with a spreadsheet.

I fully agree with that (especially the statistics issue).
My wife has a windows-PC running right now and I did some quick test, because I became unsure about my last statement.
Excel distinguishes between function-arguments and plain operators:
A1 is 1, A2 is "Foo"
A1+A2 --> #VALUE
SUM(A1:A2)--> 1
Strange, isn't it?


And A1 is and
B1 is is "5" i.e. a character string then
A1*B1 is 10

And in Calc
A1 +A2 = 1
Sum(A1:A2)=1
A1* A2 = O

I'm not use whether Excel or Calc scares me more. The potential for error is frightening.

I once got a perfect +1 correlation on a dataset and for a second was elated until common sense took hold. I was correlating the id number (assigned consecutively) with something. But without something like that as a warning oerating on a text string would be easy to miss.

Do you think we should report this as a bug or issue?
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Thu Dec 01, 2005 1:22 pm    Post subject: Reply with quote

As an update on the behaviour of Calc and Excel
Code:
Calc Verison 2.0.0 (Final)
   Check of arithmetic operations on text cells.
   A1 = 5   A2 = foo  C1=999   <- C1 is a Text formated cell   
   Operation   Result         
   Add A1 + B1   5         
   Sum (A1:B1)   5         
   Multipy A1*B1   0
         
   Sum (A1:C1)   5
   Add A1 + C1   5
   Sum(A1:C1)   5         
   Multiply A1+C1   0         
            
   All of these operations should have returned an error. 

   The equivalent results from Excel (2003) are
   A1 = 5   B1 = foo    C1 = 999      <- C1 is a Text formated cell
   Operation   Result   
   Add A1 + B1   #VALUE!   
   Sum (A1:B1)   5   
   Multipy A1*B1   #VALUE!   
   Add A1 + C1   #VALUE!   
   Sum (A1:C1)   5   
   Multipy A1*C1   4995

   All of these operations should have returned an error. 

_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Dec 01, 2005 1:31 pm    Post subject: Reply with quote

Quote:

And A1 is and
B1 is is "5" i.e. a character string then
A1*B1 is 10

And in Calc
A1 +A2 = 1
Sum(A1:A2)=1
A1* A2 = O

A1 is numeric 1, I suppose.
At least Calc consistantly treats a string as zero. May be I'm a little bit conservative at this point. I would not post an issue, since this behavior (string = zero) is one of the "traditionals" of Excel and StarOffice, not shure about the others. On the other hand it could be set as an option in Tools > Options > Calc >Calculation "treat strings as zero". I would turn it off, because of the reasons you mentioned. By default it has to be on, because of the millions of badly designed spreadsheets out there. If you are so scared about this "feature", you should use either simple dBase-tables for your constant input-values, try the cell-validation, or apply "myRedAlertCellStyle" as a conditional format.
EDIT:
A fourth option: View > "emphasize values"
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Dec 05, 2005 6:31 am    Post subject: Reply with quote

As a 5th option i'd like to indroduce my user defined cell-function FNC_TYPE()
http://www.oooforum.org/forum/viewtopic.phtml?t=28069&highlight=
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Mon Dec 05, 2005 6:51 am    Post subject: Reply with quote

Villeroy wrote:
As a 5th option i'd like to indroduce my user defined cell-function FNC_TYPE()
http://www.oooforum.org/forum/viewtopic.phtml?t=28069&highlight=



Villeroy wrote:
May be useful before exporting to dBase among other things.

Edit: I forgot to uncomment statement "on error goto typeErr". Now you get all "errors", (in fact they are strings Twisted Evil It's a type-testing function, returning wrong errors as string)


I don't read Basic very well. Can you tell us what it does in plain language. I assume that it is setting every "numeric' text value to 'error'?

If so, I like it as a work-around but in the long run I think it is a serious problem with both Calc and Excel . Has anyone check this on Gnumeric or Quattro Pro etc?

Quote:
May be I'm a little bit conservative at this point. I would not post an issue, since this behavior (string = zero) is one of the "traditionals" of Excel and StarOffice, not shure about the others. On the other hand it could be set as an option in Tools > Options > Calc >Calculation "treat strings as zero".


I must say the more that I think about this though the more I think it should be filed as an issue. This may have been the 'traditional' defaults of Excel and StarOffice but I know that I have never seen or read anything about it. I am not a sophisticated user of spreadsheets I would suspect a lot of other people who know even less about them than I do would not be impressed to find that one can add or multiply by text variables.

Even worse is the fact that screwy as Excel's handling is Calc's is just a screwy in another way which introduces even more chances for errors.

I am not too keen on a Dept of Finance analyst multplying by my Social Insurance Number when setting tax policy or a NASA engineer multiplying by a part number when repairing the shuttle.
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Dec 05, 2005 8:14 am    Post subject: Reply with quote

Comparison of builtin TYPE(ref-cell) and FNC_TYPE(sheet;row;column)
FNC_TYPE tries its best to determine beetween number, string, bool, date and error, may be calculated by a formula or not. TYPE always returns 8 if there is any kind of formula-result in ref-cell (except err -->16). FNC_TYPE returnes 8 on date/time-values (formula or not).
Code:

Values/Formulas in  in A1:A10:
123          TYPE(A1)-->1  FNC_TYPE(SHEET();ROW(A1);COLUMN(A1))-->1
=100+23 TYPE(A2)-->8 FNC_TYPE(SHEET();ROW(A2);COLUMN(A2))-->1
asdf  123 TYPE(A3)-->2 FNC_TYPE(SHEET();ROW(A3);COLUMN(A3))-->2
="asdf"    TYPE(A4)-->8 FNC_TYPE(SHEET();ROW(A4);COLUMN(A4))-->2
TRUE      TYPE(A5)-->4 FNC_TYPE(SHEET();ROW(A5);COLUMN(A5))-->4
=1<2        TYPE(A6)-->8 FNC_TYPE(SHEET();ROW(A6);COLUMN(A6))-->4
5/12/2005 TYPE(A7)-->1 FNC_TYPE(SHEET();ROW(A7);COLUMN(A7))-->8
=TODAY() TYPE(A8)-->8 FNC_TYPE(SHEET();ROW(A8);COLUMN(A8))-->8
=1/0   TYPE(A9)-->16 FNC_TYPE(SHEET();ROW(A9);COLUMN(A9))-->16
Err503  TYPE(A10)-->16 FNC_TYPE(SHEET();ROW(A10);COLUMN(A10))-->16

The boolean part with formula(-->4) works with cells explicly formatted as boolean (the same with TYPE()).
The other way of addressing (Sheet;RowMum;ColNum) is due to the way how parameters are passed to a userdefined basic-function. It's much like Excel's R1C1 addressing-style. See the basic-comments for some other examples, how to implement references with this syntax.
AFAIR the only way to create a constant error value is through paste special
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Mon Dec 05, 2005 8:55 am    Post subject: Reply with quote

jrkrideau wrote:


I must say the more that I think about this though the more I think it should be filed as an issue. This may have been the 'traditional' defaults of Excel and StarOffice but I know that I have never seen or read anything about it. I am not a sophisticated user of spreadsheets I would suspect a lot of other people who know even less about them than I do would not be impressed to find that one can add or multiply by text variables.

Even worse is the fact that screwy as Excel's handling is Calc's is just a screwy in another way which introduces even more chances for errors.

I am not too keen on a Dept of Finance analyst multplying by my Social Insurance Number when setting tax policy or a NASA engineer multiplying by a part number when repairing the shuttle.


Issue reported: Number 58903
_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group