Cor Nouws schrieb:
Hi Eike,
Solution would be to always generate an error in such case.
You mean an error if an formula holds an non numeric argument. This is
the same as Excel does, 'only' with this difference that Excel in some
formulas uses text that can be interpreted as number, and in other also
simply neglects it. Which - with my limited knowledge - looks as a sort
of a mess. And I have the impression that it differs over various
versions of Excel as well.
>> Anyone with basic or comprehensive data on the way different
versions of Excel handles text in different formula's??
Would be much appreciated! (reason further below)
So the Excel way is not something I propose.
I have a few Excel versions lying around here, and cannot find any
differences.
Tested Excel versions:
- Excel 3.0a German UI
- Excel 4.0 German UI
- Excel 5.0 German UI
- Excel 2003 German UI
- Excel 2003 English UI
- Excel 2007 English UI
I have made the following test:
A1: 1.5
A2: '1.5
A3: '1.500
A4: '1,500
A5: text
A6: =A1+A2+A3+A4
A7: =SUM(A1,A2,A3,A4)
A8: =SUM(A1:A4)
A9: =1.5+"1.5"+"1.500"+"1,500"
A10: =SUM(1.5,"1.5","1.500","1,500")
A11: =A1+A2+A3+A4+A5
A12: =SUM(A1,A2,A3,A4,A5)
A13: =SUM(A1:A5)
A14: =1.5+"1.5"+"1.500"+"1,500"+"text"
A15: =SUM(1.5,"1.5","1.500","1,500","text")
Using locale "en-US", the results of the formula cells are:
A6 = 1504.5
A7 = 1.5
A8 = 1.5
A9 = 1504.5
A10 = 1504.5
A11 = #VALUE!
A12 = #VALUE!
A13 = 1.5
A14 = 1.5
A15 = #VALUE!
- "1.5" and "1.500" are interpreted as 1.5
- "1,500" is interpreted as 1500
- the addition operator always tries to convert text to numbers
- the SUM function ignores text cells, but converts literal text
Using locale "de-DE", the results of the formula cells are:
A6 = 41072
A7 = 1,5
A8 = 1,5
A9 = 41072
A10 = 41072
A11 = #VALUE!
A12 = #VALUE!
A13 = 1,5
A14 = 1,5
A15 = #VALUE!
- "1.5" is interpreted as 1st of May 2008, resulting in 39569
- "1.500" is interpreted as 1500
- "1,500" is interpreted as 1.5
Do you have other examples that I may test in different Excel versions?
Regards
Daniel
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
