[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
Deeut
Newbie

Joined: 05 Oct 2011
Posts: 1

 Posted: Wed Oct 05, 2011 1:43 pm    Post subject: Converting Excel formulas into Calc formulas 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
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 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 https://forum.openoffice.org
Ed
Super User

Joined: 28 May 2003
Posts: 1041

Posted: Wed Oct 05, 2011 2:22 pm    Post subject:

 Deeut wrote: = IF(RAND() < 1/\$A\$1,0.31,1.188) err508
Not sure what went wrong there, it worked perfectly well when I tried it.

 Deeut wrote: =IF(MIN(C5:C104) = 0,) err501
Formula is incomplete, the comma needs to be followed by something to do if the condition is true, then another comma, then something to do otherwise, before the closing bracket.

This is nothing to do with converting from Excel to Calc,the same applies in any spreadsheet.

 Deeut wrote: =IF(C5*B5>2000,2000,IF(C5*B5>\$A\$2,C5*B5,0)) err508
Again the formula worked perfectly well when I tried it, so no idea what happened in your case.

 Villeroy wrote: Why don't you simply open your Excel files in Calc and have a look at the imported formulas?
Because they do not have a Excel files to open in Calc, they have a Word document with formulae to copy and paste into a spreadsheet. This was stated perfectly clearly in the original post.
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

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

 ed wrote: Not sure what went wrong there, it worked perfectly well when I tried it.

You are using LibreOffice.

 ed wrote: =IF(MIN(C5:C104) = 0,) err501 Formula is incomplete

Not so in Excel.
The IF is obsolete since ...
=MIN(C5:C104) = 0
... returns the same

 ed wrote: Because they do not have a Excel files to open in Calc,

Open any xls from the internet.
Open the F1 help and lookup the correct syntax.
Play with the formula wizard.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
keme
Moderator

Joined: 30 Aug 2004
Posts: 2910
Location: Egersund, Norway

 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: 1041

Posted: Thu Oct 06, 2011 1:01 pm    Post subject:

Villeroy wrote:
 ed wrote: Not sure what went wrong there, it worked perfectly well when I tried it.

You are using LibreOffice.

I tested in both LibreOffice and OOo. The only difference is that for OOo you have to replace the commas with semicolons, then the formula will work.

Villeroy wrote:
 ed wrote: =IF(MIN(C5:C104) = 0,) err501 Formula is incomplete

Not so in Excel.
The IF is obsolete since ...
=MIN(C5:C104) = 0
... returns the same

You can use the IF function in Calc without the second and third arguments to just return TRUE or FALSE, but the comma (or semicolon) separator shouldn't be there if there is nothing for it to separate.

It is that stray separator that makes this an invalid formula.

Villeroy wrote:
 ed wrote: Because they do not have a Excel files to open in Calc,

Open any xls from the internet.
Open the F1 help and lookup the correct syntax.
Play with the formula wizard.

What would be the purpose of downloading some random Excel file that does not contain the formula you need? Why not just create a new spreadsheet and then open help and play with the formula wizard?
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

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.

 Quote: What would be the purpose of downloading some random Excel file that does not contain the formula you need?

There has always been a certain amount of questions where people copy and paste blindly formulas from somewhere and wonder why
FUNCTION(Sheet1!A1 , Sheet2!A3) fails.
Some arbitrary spreadsheets with formulas (xls or ods) would clarify the correct formula syntax for the respective office version and locale setting of OOo or LibreOffice compared to MSExcel.

Somebody who believes in magic spells is not even aware that there is a formula syntax and can not be helped anyway.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum