LibreOffice calc formulas?

Chat about Linux in general
Forum rules
Do not post support questions here. Before you post read the forum rules. Topics in this forum are automatically closed 6 months after creation.
Locked
Jaydemir

LibreOffice calc formulas?

Post by Jaydemir »

I just started using Linux and I love it, but I was curious about something.

I'm sure I'm not the only one, but I'm living in a world full of Microsoft users. It's rare, but once in a while I may need to prepare spreadsheets that do calculations, and possibly share them.

That being said, does libreoffice use the same formulas as excel and Google sheets? My concern is that I put together something that someone else can't even open.

Is open office a better choice?
Last edited by LockBot on Wed Dec 28, 2022 7:16 am, edited 1 time in total.
Reason: Topic automatically closed 6 months after creation. New replies are no longer allowed.
User avatar
xenopeek
Level 25
Level 25
Posts: 29507
Joined: Wed Jul 06, 2011 3:58 am

Re: LibreOffice calc formulas?

Post by xenopeek »

Jaydemir wrote:Is open office a better choice?
Nope. OpenOffice is much less active project, it has but a fraction of the developers working on LibreOffice. If you want good compatibility with Microsoft, upgrade to LibreOffice 4.4. You can do that by adding the LibreOffice "Fresh" PPA to your system, or by upgrading to Linux Mint 17.2 which comes with LibreOffice 4.4.3. You'll be able to upgrade to Linux Mint 17.2 from 17.1 using Update Manager after the final release is done (it's currently a release candidate [public testing]).

With any LibreOffice version you can save to Microsoft file formats, but with LibreOffice 4.4 it is all much improved.
Image
Cosmo.
Level 24
Level 24
Posts: 22968
Joined: Sat Dec 06, 2014 7:34 am

Re: LibreOffice calc formulas?

Post by Cosmo. »

In the settings for calc (LO) there is section for formulas, where you can choose between different settings for syntax and delimiters.
shengchieh

Re: LibreOffice calc formulas?

Post by shengchieh »

If you haven't yet, download the calc manual from

https://www.libreoffice.org/get-help/documentation/

Also, the LO forum is at

http://en.libreofficeforum.org/forum

Ask the Calc geeks for hard questions.

Finally, you may want to do a seach for

LibreOffice calc tutorial
LibreOffice calc wiki
LibreOffice calc documentation
etc.

Sheng-Chieh
scryan

Re: LibreOffice calc formulas?

Post by scryan »

Jaydemir wrote:I just started using Linux and I love it, but I was curious about something.

I'm sure I'm not the only one, but I'm living in a world full of Microsoft users. It's rare, but once in a while I may need to prepare spreadsheets that do calculations, and possibly share them.

That being said, does libreoffice use the same formulas as excel and Google sheets? My concern is that I put together something that someone else can't even open.

Is open office a better choice?
It will GENERALLY be ok.
If its just kinda initial work with peers, were if one time a document were to just not work you could give an "oops sorry I'll re-upload right now" you could probably even send it off with out even checking.

Stuff that needs to be right with out fail, I still often do in libreoffice. I just make sure I reboot to windows and check it before I send it out (and try and leave some time in case things need fixing). I think I have heard people complain that calc/excel do have the most problems, but I have been fine with the basic average,sum, std deviation type stuff. I have experienced the most problems with going from Power Point to the Libreoffice version, specifically with font size and placement (Titles overlap, bullet points run off slide)

I find going from google docs/drive to MS Office to be worse then going from Libreoffice to MS office. In fact for documents I have often had to download from Google Docs as odt, then save as docx in libreoffice to preserve formatting that google was screwing up (mostly tables)

It does well. Check things that are absolutely critical.
User avatar
Fred Barclay
Level 12
Level 12
Posts: 4185
Joined: Sat Sep 13, 2014 11:12 am
Location: USA primarily

Re: LibreOffice calc formulas?

Post by Fred Barclay »

And if worst comes to worst, you can always install MS Office in Linux using Wine or PlayOnLinux.
Image
"Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy."
- Albert Einstein
Habitual

Re: LibreOffice calc formulas?

Post by Habitual »

Fred Barclay wrote:And if worst comes to worst, you can always install MS Office in Linux using Wine or PlayOnLinux.
You don't have to swear at the guy ;)
User avatar
Fred Barclay
Level 12
Level 12
Posts: 4185
Joined: Sat Sep 13, 2014 11:12 am
Location: USA primarily

Re: LibreOffice calc formulas?

Post by Fred Barclay »

Hey (ouch!), at least I said it would be the worst solution. And I meant it! ;)
Image
"Once you can accept the universe as matter expanding into nothing that is something, wearing stripes with plaid comes easy."
- Albert Einstein
brainout

Re: LibreOffice calc formulas?

Post by brainout »

Jaydemir wrote:I just started using Linux and I love it, but I was curious about something.

I'm sure I'm not the only one, but I'm living in a world full of Microsoft users. It's rare, but once in a while I may need to prepare spreadsheets that do calculations, and possibly share them.

That being said, does libreoffice use the same formulas as excel and Google sheets? My concern is that I put together something that someone else can't even open.

Is open office a better choice?
For spreadsheets, there's really no substitute for DOS Lotus 123 version 2.x (which runs in DOSbox), not even version 3 is better, and Excel can't touch it. I made my own commutations tables on it (actuarial junk, projecting lifetime payouts for pensions). There's not a spreadsheet alive today which can compare, so I'm stuck on it. Excel is not better, but Excel can READ those files, which many Departments of the US Government still require (wk1 format). None of the Open Office can read them. Further, calculations and formulas which Excel can do, none of the Open Office flavors can touch.

So for your purposes, it matters to know what kind of spreadsheet calculations you need to make. All the Open Office type versions in any flavor (Libre, Open, Apache, Calligra, Kingsoft) are clunky and basic. So if you only do basic stuff like tabulating, then fine. But if you have sophisticated formatting or formulas, you need Excel.

Since you can get Office 365 online (I hate it, but it is the latest version), you don't need Linux to run it; or conversely, it might run natively on Linux. So then you don't have to concern yourself with Wine.

I just this afternoon, got Wine to work in Mint 17, by following these steps: http://www.howtogeek.com/171565/how-to- ... -on-linux/ Since MS Office is $50 or less on Amazon, it's a good deal and is the worldwide standard for law and accounting, governments, etc. And that's how you can get 100% compatibility.

Also download from Microsoft for free, 'fileconverters.exe' which translates 2003 files into later MS Office versions. You'd run that in Wine the same way as the guy explains in that link, by right-clicking on the executable, and Mint will have a context menu with an option that says 'Wine Program Loader'. Run it. Takes five seconds, and it updates your MS Office to be able to read later files.

Then you will see how much better it is versus Open Office, etc. If you are a glutton for punishment, go look up 'brainout' and 'vimeo', pick some boring channel of videos I do on exegesis like https://vimeo.com/channels/lukemeter -- I use MS Word in nearly every video, so you can turn the sound off and just skip through it to see its abilities. It's 100% customizable, so what you see on screen is very dissimilar from what comes out of the package. No Linux product excepting WordPerfect (which you should prefer first, if you can find it) can compare. None.

I wish I could get my copies of WordPerfect for Linux to install. :) That will surely translate MS Word files, as WordPerfect is the other standard in the world.

Yell at me if I'm being obtuse? Thank you for your time, and good luck choosing!
User avatar
xenopeek
Level 25
Level 25
Posts: 29507
Joined: Wed Jul 06, 2011 3:58 am

Re: LibreOffice calc formulas?

Post by xenopeek »

brainout wrote:I made my own commutations tables on it (actuarial junk, projecting lifetime payouts for pensions). There's not a spreadsheet alive today which can compare, so I'm stuck on it. [...] None of the Open Office can read them. Further, calculations and formulas which Excel can do, none of the Open Office flavors can touch. [...] All the Open Office type versions in any flavor (Libre, Open, Apache, Calligra, Kingsoft) are clunky and basic. So if you only do basic stuff like tabulating, then fine. But if you have sophisticated formatting or formulas, you need Excel.
That's a rather bleak opinion. Can you give some examples of missing formulas or missing formatting options? My experience has been the exact opposite so I'm trying to understand the shortcomings you have encountered. I'm using LibreOffice (version 4.4; earlier versions aren't as good) and have done some (for me :)) pretty complex stuff with it (calculations for income tax, mortgage, life annuity, stocks, and so on; using sliding scale tables and whatnot).
Image
brainout

Re: LibreOffice calc formulas?

Post by brainout »

xenopeek wrote:
brainout wrote:I made my own commutations tables on it (actuarial junk, projecting lifetime payouts for pensions). There's not a spreadsheet alive today which can compare, so I'm stuck on it. [...] None of the Open Office can read them. Further, calculations and formulas which Excel can do, none of the Open Office flavors can touch. [...] All the Open Office type versions in any flavor (Libre, Open, Apache, Calligra, Kingsoft) are clunky and basic. So if you only do basic stuff like tabulating, then fine. But if you have sophisticated formatting or formulas, you need Excel.
That's a rather bleak opinion. Can you give some examples of missing formulas or missing formatting options? My experience has been the exact opposite so I'm trying to understand the shortcomings you have encountered. I'm using LibreOffice (version 4.4; earlier versions aren't as good) and have done some (for me :)) pretty complex stuff with it (calculations for income tax, mortgage, life annuity, stocks, and so on; using sliding scale tables and whatnot).
Yeah it is bleak. I don't like having to keep using 30-year-old black screens. :)

Commutations functions are very complicated. You 're essentially blending a bunch of life probabilities together based on interest and longevity assumptions. Uncle Sammy changes the THREE TIERED interest rates each month, and you have to do the calculations per person: the annuity factors end up being BLENDED in three periods together, so you can't just multiply by an APR, like you would for a simple life annuity. This is a far more complex version than the 'life annuity' formula built into most spreadsheets, which USED TO BE the way everyone did it, until the Pension Protection Act. (And it was done to bail out corporations who didn't properly fund their retirement plans, frankly.)

So if your needs aren't that complex, if whatever YOU need the spreadsheet to do it does, then I'm happy for you. But for me, I tried them all (for obvious reasons), and they just don't cut it. Further, the macros I wrote in Lotus 123 makes navigation a breeze. None of that is really doable in Excel or even later Lotus 123. Symphony is no good, either (there is a Linux version of it, and it's more like Excel than the others).

So as you put it, you just need income tax, mortgage, etc. If what you got works for that, fine. Folks like hrlngrv in ZDnet and Computerworld (a savvy commenter who's keen on both Windows and Linux) will perhaps better explain the many flaws he finds in the Linux versions, so he too remains with Excel, but would like to leave it.

As for missing formatting options, that's a long answer and my post is long already. Suffice it to say that if you like the formatting options you have, then that's enough for you. But for many of us, it's nowhere near enough. For example, I have to format my actuarial spreadsheets for audits, and since it's Lotus 123, only Excel can read it. The formatting in Excel is to create a bunch of windows and 'snapshots' artfully placed so when the auditor opens the file, he/she can see at a glance, the relevant data. Saves the client money and auditor and me time. I can't do any of that in the Linux knock-offs. I wish I could. Then there are the spiffing up the printout options which frankly the Linux knock-offs lack, for professional printing and presentation. Finally and really most importantly,the Linux knock-offs have really bad navigational tools, like the lack of just hitting F5 and typing in the cell you want to go to. In Libre and the others, if you hit F5 you then have to click your mouse on a box, type the column then click again type a row, and only THEN go where you want. That triples your time working.

In short, this isn't to bash (pun?) Linux. It's just stating the case. For what it's worth, I did just buy Crossover and did install Wine and a DOSbox, so I can try to prepare for the next 10 years, which frankly will belong to Linux. But it's not going to be an either-or situation.

Nor should it be. Windows needs Linux and vice versa, and as soon as folks realize the symbiotic benefit, Linux will be in every home computer. :)
User avatar
xenopeek
Level 25
Level 25
Posts: 29507
Joined: Wed Jul 06, 2011 3:58 am

Re: LibreOffice calc formulas?

Post by xenopeek »

I've never used Lotus 123 and don't grasp why commutations functions would be something you can't do with LibreOffice (not a native speaker). Googling around for some examples of commutations functions to grasp it better, I find this example http://www.glxspace.com/2013/12/24/pens ... ous-years/. It opens up fine in LibreOffice 4.4 and works. I doubt you can't do what you want to do with LibreOffice but indeed importing from Lotus 123 might not work. LibreOffice 4.4 has code from Lotus Symphony donated by IBM. I don't know one Lotus from another; word association with "Lotus" is "Esprit" for me :lol:
Image
scryan

Re: LibreOffice calc formulas?

Post by scryan »

Brain out, not to argue that libreoffice has complete compatibility... but is it possible your experience is a bit dated at this point? I noticed you kept mentioning open office... Its been a good few years since that was in common use and libreoffice now vs open office back when it was the standard open source office suite is quite a bit different compatibility wise...

Just stuck out to me that you were referencing Open Office, been some years since I've heard it mentioned for any reason other then to say use libre instead...
brainout

Re: LibreOffice calc formulas?

Post by brainout »

scryan wrote:Brain out, not to argue that libreoffice has complete compatibility... but is it possible your experience is a bit dated at this point? I noticed you kept mentioning open office... Its been a good few years since that was in common use and libreoffice now vs open office back when it was the standard open source office suite is quite a bit different compatibility wise...

Just stuck out to me that you were referencing Open Office, been some years since I've heard it mentioned for any reason other then to say use libre instead...
I was using the name Open Office generically, as a genre, most folks recognize the whole genre by that name. My information is a year old, because a year ago I gave up trying to find a Linux distro. But in a way it's only two weeks old, as my latest distro installations were the last two weeks, and I tested Calligra and Libre during that time, found both awful (for what I am used to as ease, awful by that standard, maybe not by your standard).

Hope this helps.
Locked

Return to “Chat about Linux”