[Solved] Conversion of formula from excel to open office

Discuss the spreadsheet application

[Solved] Conversion of formula from excel to open office

Postby Nicole11 » Mon Nov 04, 2019 11:50 pm

Good Morning Everyone,

I have just started a new job as segment producer for a television series and it's sink or swim. I have been provided with an excel document that i need to populate with people's receipts that i receive and i cannot get the bottom part of the document to populate with totals of specific account numbers. The formulas are there but it does not work. Can anyone please assist? I have tried so many things and now i am in the thivk of it and NEED it today. Can anyone assist? I have uploaded the open office version. The tempate was sent to me via email but i cannot download as i do not have excel but can forward to an email address if anyone can assit. Thanks so much.
Attachments
TEST.ods
(40.52 KiB) Downloaded 19 times
Last edited by MrProgrammer on Sun Dec 08, 2019 9:22 pm, edited 2 times in total.
Reason: Moved from Beginners forum to Calc; Tagged ✓ [Solved]
Apache Open Office 4.1.1
OS X 10.9.5 (13F1911)
Nicole11
 
Posts: 1
Joined: Mon Nov 04, 2019 11:45 pm

Re: Conversion of formula from excel to open office

Postby Villeroy » Tue Nov 05, 2019 12:04 am

All formulas work for me.
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: 27555
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Conversion of formula from excel to open office

Postby MrProgrammer » Tue Nov 05, 2019 12:37 am

Hi, and welcome to the forum.

There is no IFERROR function in OpenOffice: [Solved] Plz translate Excel formula to OpenOffice
Instead of =IFERROR(expression;"") use =IF(ISERROR(expression);"";expression).

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.
Mr. Programmer
AOO 4.1.7 Build 9800 on MacOS 10.14.6.   The locale for any menus or Calc formulas in my posts is English (USA).
User avatar
MrProgrammer
Moderator
 
Posts: 3939
Joined: Fri Jun 04, 2010 7:57 pm
Location: Wisconsin, USA

Re: Conversion of formula from excel to open office

Postby Zizi64 » Tue Nov 05, 2019 12:47 am

The formulas are there but it does not work.

Your sample file works for me in my LO 6.2.8

What is the symptom?

If the formulas (the string of the formula) appeared instead of the calculated results, then you need switch off the showing the formulas:
In the LibreOffice menu: View - Show formula
In the AOO menu: Tools - Options - OpenOffice Calc - View - Display - Formulae
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.3.4; 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: 8644
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Conversion of formula from excel to open office

Postby RusselB » Tue Nov 05, 2019 2:16 am

The problem is OpenOffice's lack of the IFERROR function.
Every cell in that spreadsheet that reports #VALUE! contains a formula that has the IFERROR function....or references a cell that returns #VALUE! for this reason.

Your easiest solution is to switch to LibreOffice, which has the IFERROR function....otherwise you're going to have to do a lot of formula rewriting.
OpenOffice 4.1.7 and LibreOffice 6.3.3.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: 5787
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Conversion of formula from excel to open office

Postby Grah_am » Mon Nov 25, 2019 8:26 am

Convert the spreadsheet to a basic version of Microsoft Excel from the OpenOffice.org interface. Open the spreadsheet in OpenOffice.org Calc. Click File at the top of the screen then select Save As. Choose the Microsoft Excel 97/2000/XP option to save the spreadsheet with an .xls extension.
NeoOffice 2.2.3 with MacOS 10.4 UPSers
Grah_am
 
Posts: 1
Joined: Mon Nov 25, 2019 8:23 am

Re: Conversion of formula from excel to open office

Postby RusselB » Tue Nov 26, 2019 2:33 am

This will give a version of the spreadsheet with the common functions as a Excel spreadsheet, but won't resolve the problem of the OpenOffice's lack of an IFERROR function.
As I already stated, using LibreOffice is the easiest way to resolve that problem.
OpenOffice 4.1.7 and LibreOffice 6.3.3.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: 5787
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: No registered users and 5 guests