[Solved] Help converting MS Excel formula to AOO formula

Discuss the spreadsheet application

[Solved] Help converting MS Excel formula to AOO formula

Postby Imtom.113 » Mon Feb 18, 2019 8:35 pm

Thx to all for help with solving this. Very much appreciated !!! Tom

I'm using this version...

Apache Open Office 4.1.6
AOO416m1(Build:9790) - Rev. 1844436
2018-10-23 12:57

I'm getting an ERR:509 when I try to use the following EXCEL formula in my AOO Calc spreadsheet. Could someone assist me to convert it ? Thx, Tom

=CHOOSE(MOD(COLUMNS($a:a)-1,4)+1,"1st","2nd","3rd","4th")&" Qtr "&2018+ROUNDUP(COLUMNS($a:a)/4,0)
Last edited by Imtom.113 on Tue Feb 19, 2019 11:08 pm, edited 1 time in total.
OpenOffice 4.1.6 on Windows 10
Imtom.113
 
Posts: 3
Joined: Wed Feb 13, 2019 2:06 am

Re: Help converting Excel formula to AOO formula

Postby Zizi64 » Mon Feb 18, 2019 9:31 pm

My First tip: use semicolon ; instead of the comma , - as a parameter separator character.

The modified formula seems to work for me in my LO 6.1.5 version.
Code: Select all   Expand viewCollapse view
=CHOOSE(MOD(COLUMNS($A:A)-1;4)+1;"1st";"2nd";"3rd";"4th")&" Qtr "&2018+ROUNDUP(COLUMNS($A:A)/4)

I just added a semicolon between the parameters 1"1st" -> 1;"1st"
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.4; AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7965
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Help converting Excel formula to AOO formula

Postby FJCC » Mon Feb 18, 2019 10:15 pm

OpenOffice does not recognize referring to entire columns by just the letter. Try
Code: Select all   Expand viewCollapse view
=CHOOSE(MOD(COLUMNS($A1:A1048576)-1;4)+1;"1st";"2nd";"3rd";"4th")&" Qtr "&2018+ROUNDUP(COLUMNS($A1:A1048576)/4)
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7153
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Help converting Excel formula to AOO formula

Postby Villeroy » Mon Feb 18, 2019 10:55 pm

You have 2 options:
1) If the formula used to work with Excel then open the xls file you have with OpenOffice. OpenOffice will do the right thing with your formulas and you can see how they differ syntactically from MS Excel.
2) Install https://libreoffice.org/ which is the legitimate successor of OpenOffice. Its Calc component is almost fully compatible with MS Excel.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26710
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Calc

Who is online

Users browsing this forum: No registered users and 23 guests