[Solved] Plz translate Excel formula to OpenOffice

Discuss the spreadsheet application

[Solved] Plz translate Excel formula to OpenOffice

Postby statiyo » Mon Jul 15, 2019 4:01 am

Hi

Am trying to solve my case in Oppenoffice so I found a the perfect exemple but its for excel...I tried the formula in Oppenoffice did not work...:

Formula 2. Vlookup multiple matches and return results in a row at link below

https://www.ablebits.com/office-addins- ... ues-excel/

=IFERROR(INDEX($B$3:$B$13, SMALL(IF($D3=$A$3:$A$13, ROW($B$3:$B$13)-2,""), COLUMN()-4)),"")

Could someone kindly please translate this excel formula for openoffice?

Many thanks
Last edited by Hagar Delest on Mon Jul 15, 2019 10:11 pm, edited 1 time in total.
Reason: tagged solved
OpenOffice 4.1 on Windows XP
statiyo
 
Posts: 4
Joined: Mon Jul 15, 2019 3:56 am

Re: Plz translate excel formula to openoffice

Postby RusselB » Mon Jul 15, 2019 5:23 am

Welcome to the Forums.
Open Office does not have the IFERROR function.
For Open Office you need to change that to a nested IF with the first parameter being the ISERROR function. I also don't know about the comparison in the IF that is contained in your SMALL.
Presuming that works, the OpenOffice Calc formula would be
Code: Select all   Expand viewCollapse view
=if(iserror(INDEX($B$3:$B$13;SMALL(IF($D3=$A$3:$A$13;ROW($B$3:$B$13)-2;"");COLUMN()-4)));"";INDEX($B$3:$B$13;SMALL(IF($D3=$A$3:$A$13;ROW($B$3:$B$13)-2;"");COLUMN()-4)))

An alternative suggestion is to download and install LibreOffice, which supports the IFERROR function and the usage of commas as parameter separators, rather than semi-colons, which OpenOffice uses.
OpenOffice 4.1.6 and LibreOffice 6.0.6.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5282
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Plz translate excel formula to openoffice

Postby Zizi64 » Mon Jul 15, 2019 6:08 am

Note:
The AOO/LO Calc is a multiplatform, and multilanguage software.
The comma is the decimal separator in many languages (like the Hungarian and the German languages and others)
Therefore it is better to use the semicolon as parameter separators, instead of the comma in the LibreOffice.
Tibor Kovacs, Hungary; LO6.1.6 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.5; AOO4.1.6
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: 8137
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Plz translate excel formula to openoffice

Postby Villeroy » Mon Jul 15, 2019 11:23 am

Install https://libreoffice.org and your formula will work with IFERROR and with commas. LibreOffice is the successor to OpenOffice.
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: 26968
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Plz translate excel formula to openoffice

Postby John_Ha » Mon Jul 15, 2019 4:03 pm

Villeroy wrote:... LibreOffice is the successor to OpenOffice.

I don't think that is correct. An Airbus 380 is not a successor to a Boeing 747 - there is zero relationship between them.

Similarly AOO and LO are completely different products designed and built by completely different organisations and there is zero relationship between them.

I think it is better to say "LibreOffice is a more up to date and continuing to be developed alternative to ApacheOpenOffice".
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 6717
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: Plz translate excel formula to openoffice

Postby Villeroy » Mon Jul 15, 2019 5:13 pm

Image

LO forked from version 3.3. Versions 3.3 of both products were identical.
AOO released 3 more versions with the side bar being the only visible feature compared to 3.3.
LO developed steadily introducing hundreds of improvements and major changes, some of them questionable though. For spreadsheet users, particularly from the anglosaxon sphere, very high compatibility with XL is certainly a valuable set of features.
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: 26968
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Plz translate excel formula to openoffice

Postby statiyo » Mon Jul 15, 2019 6:42 pm

Thanks for your help!

I'm closer than I was but am getting incorrect results - how to fix formula?

Plz see attached file
Attachments
not right.ods
(9.91 KiB) Downloaded 6 times
OpenOffice 4.1 on Windows XP
statiyo
 
Posts: 4
Joined: Mon Jul 15, 2019 3:56 am

Re: Plz translate excel formula to openoffice

Postby gerard24 » Mon Jul 15, 2019 7:09 pm

https://www.ablebits.com/office-addins-blog/2017/02/22/vlookup-multiple-values-excel/ wrote:enter one of the following array formulas in the formula bar, and press Ctrl + Shift + Enter
LibreOffice 6.2.4 on Windows 10
gerard24
Volunteer
 
Posts: 948
Joined: Sat Oct 30, 2010 5:12 pm
Location: France

Re: Plz translate excel formula to openoffice

Postby statiyo » Mon Jul 15, 2019 9:16 pm

thx I did CSE but the curly brackets do not show up and same result...any ideas??
OpenOffice 4.1 on Windows XP
statiyo
 
Posts: 4
Joined: Mon Jul 15, 2019 3:56 am

Re: Plz translate excel formula to openoffice

Postby Villeroy » Mon Jul 15, 2019 9:25 pm

Pivot tables are easy.
Attachments
right.ods
(13.4 KiB) Downloaded 5 times
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: 26968
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Plz translate excel formula to openoffice

Postby statiyo » Mon Jul 15, 2019 9:37 pm

Interesting, thx Villeroy for making it right!!
OpenOffice 4.1 on Windows XP
statiyo
 
Posts: 4
Joined: Mon Jul 15, 2019 3:56 am

Re: Plz translate excel formula to openoffice

Postby Villeroy » Mon Jul 15, 2019 9:41 pm

https://wiki.openoffice.org/wiki/Docume ... /DataPilot (data pilot and pivot table are the same)

I added column headers above the data, called menu:Data>Pivot>Create... and plotted names in row fields against items in column fields with a data field couting the items.
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: 26968
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Plz translate excel formula to openoffice

Postby John_Ha » Mon Jul 15, 2019 10:29 pm

Villeroy wrote:LO forked from version 3.3. Versions 3.3 of both products were identical.

I think we are in violent agreement and the correct statement is, as you suggest, "LibreOffice is a successor to OpenOffice.org". Similarly, AOO is a successor to OpenOffice.org.

I know it's semantics but I am a long-standing member of The Pedants' Society :-) .
AOO 4.1.6, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the Writer guide.

Remember: Always save your Writer files as .odt files. - see here for the many reasons why.
John_Ha
Volunteer
 
Posts: 6717
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK


Return to Calc

Who is online

Users browsing this forum: No registered users and 14 guests