View previous topic :: View next topic 
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! 

Back to top 


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 

Back to top 


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. 

Back to top 


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 

Back to top 


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 "iftrue" and "iffalse" return values are optional, and if not specified they will default to boolean values TRUE and FALSE, respectively (effectively leaving zeroresult conditions as zero/FALSE and converting nonzero conditions to 1/TRUE).
In Excel, the second parameter (iftrue) is mandatory, but may be left empty. IOW you have to type the parameter separator (comma) before the closing parenthesis. The third (iffalse) parameter is still optional and will default to FALSE.
IIRC, Excel 2003 would default to TRUE for an empty iftrue 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. 

Back to top 


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? 

Back to top 


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 

Back to top 




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

Powered by phpBB © 2001, 2005 phpBB Group
