OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Converting Excel formulas into Calc formulas

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
Deeut
Newbie
Newbie


Joined: 05 Oct 2011
Posts: 1

PostPosted: Wed Oct 05, 2011 1:43 pm    Post subject: Converting Excel formulas into Calc formulas Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Oct 05, 2011 1:53 pm    Post subject: Reply with quote

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
View user's profile Send private message
Ed
Super User
Super User


Joined: 28 May 2003
Posts: 1041

PostPosted: Wed Oct 05, 2011 2:22 pm    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Oct 05, 2011 3:03 pm    Post subject: Reply with quote

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
View user's profile Send private message
keme
Moderator
Moderator


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

PostPosted: Wed Oct 05, 2011 11:30 pm    Post subject: Some background Reply with quote

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.
Back to top
View user's profile Send private message
Ed
Super User
Super User


Joined: 28 May 2003
Posts: 1041

PostPosted: Thu Oct 06, 2011 1:01 pm    Post subject: Reply with quote

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
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Oct 06, 2011 10:19 pm    Post subject: Reply with quote

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
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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