Converting Excel formulas into Calc formulas

Deeut · Newbie Joined: 05 Oct 2011 Posts: 1

Hi All,

Ive been use Calc for almost a year now with major problems. Unfortunately this semester I have a class where a professor gives us a word DOC with instructions to copy and paste her equations into Excel. When I do this, I get a variety of error codes. I know I need to replace commas with semi colons. Is there anything else in these formulas I need to change?

= IF(RAND() < 1/$A$1,0.31,1.188) err508
=IF(MIN(C5:C104) = 0,) err501
=IF(C5*B5>2000,2000,IF(C5*B5>$A$2,C5*B5,0)) err508

Thanks!

Villeroy · Posted: Wed Oct 05, 2011 1:53 pm Post subject:

Why don't you simply open your Excel files in Calc and have a look at the imported formulas?
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

Ed · Super User Joined: 28 May 2003 Posts: 1040

Villeroy · Posted: Wed Oct 05, 2011 3:03 pm Post subject:

keme · Posted: Wed Oct 05, 2011 11:30 pm Post subject: Some background

Error codes
You can look them up in the suite's help system. Keyword lookup "Error codes; list ..."

501: invalid character
This is what you get if you use comma to separate the parameters (or in LibreOffice you use semicolon when it is set to expect comma).

508: Unmatched pair.
E.g. opening and closing parentheses out of order.
Not sure how that applies to the formulas you posted, but perhaps there is an extra closing parenthesis somewhere. If you want our assessment, copy your own modified formulas from Calc, instead of copying your professor's suggestion from the Word document.

The IF() function
IF() has one required parameter in Calc, the condition. the "if-true" and "if-false" return values are optional, and if not specified they will default to boolean values TRUE and FALSE, respectively (effectively leaving zero-result conditions as zero/FALSE and converting nonzero conditions to 1/TRUE).

In Excel, the second parameter (if-true) is mandatory, but may be left empty. IOW you have to type the parameter separator (comma) before the closing parenthesis. The third (if-false) parameter is still optional and will default to FALSE.
IIRC, Excel 2003 would default to TRUE for an empty if-true value, just like Calc does (can't test that right now, sorry!).
Excel 2010 assumes empty value to have numerical value zero. (Tested)

Although Excel seems to distinguish better between booleans and numbers, a zero is still functionally equivalent to FALSE. So for practical purposes, in Excel 2010 your second function will always return the same result, amounting to zero/FALSE.
Villeroy's suggestion to eliminate the IF() function will return the same result in all applications, and is probably what you need.

Ed · Super User Joined: 28 May 2003 Posts: 1040

Villeroy · Posted: Thu Oct 06, 2011 10:19 pm Post subject:

In some English version of MSExcel FUNCTION(arg1,,arg3,) is a function call with a given first argument, the optional 2nd argument missing, third argument given and the 4th argument missing. Excel ignores the last comma.

Deeut complains that the copied strings do not evaluate in OOo 3.3 which is mainly because no version of OOo supports comma as list operator. Same with many localized Excel versions, btw.
Secondly, the stray comma in the IF function gives a syntax error in OOo, but the whole IF is obsolete anyway. The comparison =MIN(C5:C104)=0 returns TRUE or FALSE in both applications. There is no need to wrap this in an additional IF function.