Created attachment 33673 [details]
The spreadsheet that is causing the problem

Excel 2003 gives an error #VALUE! for these cells and tells me: one of the 
values used in the formula has a wrong datatype

The problem arises with the string values in the IF formulas in Column V of the
attached example Excel file.

Equation that works in Excel, but gives Error 503 in OpenOffice.org
=IF(U5<165,"2",IF(U5<285,"3",IF(U5<370,"4",IF(U5<500,"4",IF(U5<650,"6",IF(U5<1000,"4",IF(U5<1305,"6","Large
Shear")))))))

Equation corrected to work in OpenOffice.org
=IF(U5<165;2;IF(U5<285;3;IF(U5<370;4;IF(U5<500;4;IF(U5<650;6;IF(U5<1000;4;IF(U5<1305;6;"Large
Shear")))))))

Note the only difference is the removal of the quotes around the values to enter
in the cells based on the condition resulting in the entry of a number rather
than a string.

A very simplified example of the problem:

Excel
A1 =100/2       results in 50
A1 =100/"2"     results in 50

A1 100  B1 2    C1=A1/B1 results in 50
A1 100  B1 "2"  C1=A1/B1 results in #VALUE!
A1 100  B1 ="2" C1=A1/B1 results in 50
A1 100  B1 '2   C1=A1/B1 results in 50

OpenOffice.org
A1     =100/2   results in 50
A1     =100/"2" results in 50

A1 100  B1 2    C1=A1/B1 results in 50
A1 100  B1 "2"  C1=A1/B1 results in Error:503 <-- This is correct error
A1 100  B1 ="2" C1=A1/B1 results in Error:503 <-- This is incorrect error
A1 100  B1 '2   C1=A1/B1 results in Error:503 <-- This is incorrect error

Notice that if =100/"2" works in a single cell, there is no apparent reason that
A1 100 B1 ="2" C1=A1/B1 should not result in the same answer.

I'd say 100/"2" results in 50 is an error; or any mathematical operation with 
text for which a number is required, so OOos handling of the example you gave is 
correct. Anyway the error in your spreadsheet is caused by comparing a text 
value with a number in the If functions, which I think is correct (the error). 
error 503 in OOo is probably the wrong one. Excels error sounds more logical and 
Excel 2003 gives this error (too), see comment above, so... 

Lars, I notice in the issue activity you comment that the error should be 502
rather than 503. 

From the OO 2.0 Help File:
Error 502 
Invalid argument
Function argument is not valid, for example, a negative number for the root
function.

Notice that =IF(A1<100,"2") has perfectly valid arguments for an IF formula and
is not the direct problem with the example Excel file. The problem does not
arise until the resulting text 2 is referenced in a formula in another cell and
treated like it was a number.  Therefore, Error 502 would not be appropriate in
this case. 

From the OO 2.0 Help File:
Error 519
No result (#VALUE is in the cell rather than Err:519!)
Formula yields a value that does not corresponds to the definition, or a cell
that is referenced in the formula contains text instead of a number.

This is more appropriate error since this is the exact problem with the example
spreadsheet.  I definitely agree with you that Error 503 is completely
inappropriate.

The results of the simplified example in my last comment were from Excel 2002,
so there will still be conversion problems from documents for Excel 2002 and
possibly older formats even if the formula results in a more descriptive error.

 

Hi,

Err:503 pointing to an invalid floating point operation fits pretty well to the
problem. You want to calculate with text values and this is definitly an invalid
floating point operation.

Basically this is a double to Issue 5658 .

Frank

*** This issue has been marked as a duplicate of 5658 ***

closed double