| 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: 6733
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 (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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
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 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.  |
|
| 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: 10065
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: 6733
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 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. |
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: 10065
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 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. |
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 |
|
|
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
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 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. |
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 |
|
|
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
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: 10065
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 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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
|
| Back to top |
|
|
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
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 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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Mon Dec 05, 2005 8:14 am Post subject: |
|
|
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 |
|
|
jrkrideau
Super User
Joined: 08 Aug 2005
Posts: 6733
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 |
|
|
|
