[Solved] Excel formula conversion for OpenOffice

Discuss the spreadsheet application
Post Reply
MrLeeBert
Posts: 3
Joined: Tue Jun 08, 2021 5:29 am

[Solved] Excel formula conversion for OpenOffice

Post by MrLeeBert »

I have a formula from Excel that I can't seem to get working in Calc. The formula resides in Column A and references the columns labeled "Quantity in Stock" and "Reorder Level" and "Discontinued?". How it works in Excel is that if the Quantity in Stock is less than the Reorder Level it shows a flag in A1 and highlights the row yellow. Also if a "yes" is entered under "Discontinued?" it changes font of that row to strikethrough and highlights the row light gray.
Here is the Excel formula: =IFERROR(([@[Quantity in Stock]]<=[@[Reorder Level]])*([@[Discontinued?]]="")*@valHighlight,0)

Any help to get this working is greatly appreciated as I am an Excel/Calc newbie and trying to get my small business going! :crazy:
Last edited by MrProgrammer on Fri Jun 18, 2021 2:32 am, edited 3 times in total.
Reason: Add green tick
OpenOffice 4.1.10 on Windows 10
User avatar
Zizi64
Volunteer
Posts: 11352
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Excel formula conversion for Open Office

Post by Zizi64 »

Use LibreOffice if you need the IFERROR()function. The Calc application of the LO has IFERROR() function, but the AOO Calc has not. (I have not tried the AOO 4.1.10 yet...)


Otherwise you can mofify the formula: Use IF(ISERROR()) functions instead of the function IFERROR(). The AOO Calc has IF() and ISERROR() functions.


Please upload a real (ODF type) sample file here. That will contain more informations than the description (or the screenshots) can contain. It is important for a detailed help...
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
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.
FJCC
Moderator
Posts: 9248
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Excel formula conversion for Open Office

Post by FJCC »

Calc does not use that formula syntax but it is easily translated. Also, Calc does not have the IFERROR function but there is a simple alternative. Actually, I suspect the IFERROR is not really needed, but that is just a guess. If you post a small example of your spreadsheet layout, someone could translate the formula.
To upload a file, click PostReply and look for the Upload Attachment tab just below the box where you type a response.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
MrLeeBert
Posts: 3
Joined: Tue Jun 08, 2021 5:29 am

Re: Excel formula conversion for Open Office

Post by MrLeeBert »

Here is the spreadsheet in question... Thanks again!
Attachments
inventory.zip
(13.42 KiB) Downloaded 129 times
OpenOffice 4.1.10 on Windows 10
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Excel formula conversion for OpenOffice

Post by Villeroy »

LibreOffice Calc loads the file without problems.

https://www.libreoffice.org/discover/li ... penoffice/
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
MrLeeBert
Posts: 3
Joined: Tue Jun 08, 2021 5:29 am

Re: Excel formula conversion for OpenOffice

Post by MrLeeBert »

Ok it looks like LibreOffice is the answer... Thanks!
OpenOffice 4.1.10 on Windows 10
Post Reply