View previous topic :: View next topic 
Author 
Message 
Yves42 Newbie
Joined: 30 Nov 2005 Posts: 2

Posted: Wed Nov 30, 2005 9:55 am Post subject: Calculate with text cells 


Hello
First excuse my English : I hope you will be able to undestand me
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 


richhill OOo Advocate
Joined: 16 Jun 2004 Posts: 418 Location: Mesa, AZ

Posted: Wed Nov 30, 2005 10:11 am Post subject: 


Sounds to me like Excel has a serious problem
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 


jrkrideau Super User
Joined: 08 Aug 2005 Posts: 6732 Location: Kingston ON Canada

Posted: Wed Nov 30, 2005 10:52 am Post subject: 


richhill wrote:  Sounds to me like Excel has a serious problem 
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
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 (999999999). 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 999999999. 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 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Wed Nov 30, 2005 12:26 pm Post subject: 


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 999999999. 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 textvalue, used in a numeric context evaluates to zero.
This is what I learned as an experienced Exceluser (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. 

Back to top 


Yves42 Newbie
Joined: 30 Nov 2005 Posts: 2

Posted: Wed Nov 30, 2005 11:31 pm Post subject: 


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 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Thu Dec 01, 2005 1:07 am Post subject: 


Hi, Yves
Your point of view is clear and comprehensible. But you asked in a technical forum.
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 


jrkrideau Super User
Joined: 08 Aug 2005 Posts: 6732 Location: Kingston ON Canada

Posted: Thu Dec 01, 2005 6:00 am Post subject: 


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 999999999. 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 textvalue, used in a numeric context evaluates to zero.
This is what I learned as an experienced Exceluser (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. 
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 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Thu Dec 01, 2005 7:43 am Post subject: 


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 999999999. 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 textvalue, used in a numeric context evaluates to zero.
This is what I learned as an experienced Exceluser (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. 
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 windowsPC running right now and I did some quick test, because I became unsure about my last statement.
Excel distinguishes between functionarguments and plain operators:
A1 is 1, A2 is "Foo"
A1+A2 > #VALUE
SUM(A1:A2)> 1
Strange, isn't it? 

Back to top 


jrkrideau Super User
Joined: 08 Aug 2005 Posts: 6732 Location: Kingston ON Canada

Posted: Thu Dec 01, 2005 11:46 am Post subject: 


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 999999999. 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 textvalue, used in a numeric context evaluates to zero.
This is what I learned as an experienced Exceluser (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. 
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 windowsPC running right now and I did some quick test, because I became unsure about my last statement.
Excel distinguishes between functionarguments 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 


jrkrideau Super User
Joined: 08 Aug 2005 Posts: 6732 Location: Kingston ON Canada

Posted: Thu Dec 01, 2005 1:22 pm Post subject: 


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 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Thu Dec 01, 2005 1:31 pm Post subject: 


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 dBasetables for your constant inputvalues, try the cellvalidation, or apply "myRedAlertCellStyle" as a conditional format.
EDIT:
A fourth option: View > "emphasize values" 

Back to top 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany


Back to top 


jrkrideau Super User
Joined: 08 Aug 2005 Posts: 6732 Location: Kingston ON Canada

Posted: Mon Dec 05, 2005 6:51 am Post subject: 


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 It's a typetesting 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 workaround 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 


Villeroy Super User
Joined: 04 Oct 2004 Posts: 10106 Location: Germany

Posted: Mon Dec 05, 2005 8:14 am Post subject: 


Comparison of builtin TYPE(refcell) 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 formularesult in refcell (except err >16). FNC_TYPE returnes 8 on date/timevalues (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 basicfunction. It's much like Excel's R1C1 addressingstyle. See the basiccomments 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 


jrkrideau Super User
Joined: 08 Aug 2005 Posts: 6732 Location: Kingston ON Canada

Posted: Mon Dec 05, 2005 8:55 am Post subject: 


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 


