OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Migration issues : Calc's idiosyncrasies
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Fri Apr 06, 2007 3:17 pm    Post subject: Migration issues : Calc's idiosyncrasies Reply with quote

A thread has been suggested. The topic has arisen in two recent threads and there are, evidently, a number of frequent contributors with an interest in the subject.

I thought a sticky would be appropriate. There was a sticky in Macros & API on a similar theme but it has been deleted.

Major edit:

Since entering the original threads I've come across other posts, so I've arranged the lot (to date) in some sort of alphabetical order. Please add further posts to the thread if you know of other instances where Calc differs from the software you have been using.

*** ### ***

Editing

Simple delete : http://www.oooforum.org/forum/viewtopic.phtml?p=218116#218116

Contemporaneous editing : http://www.oooforum.org/forum/viewtopic.phtml?t=54139

Dragging a single cell : http://www.oooforum.org/forum/viewtopic.phtml?t=55299

*** ### ***

Text and text files, manipulating cells

Numbers entered as text : http://www.oooforum.org/forum/viewtopic.phtml?t=55386
Text to numbers - remove a leading apostrophe : http://www.oooforum.org/forum/viewtopic.phtml?p=228574#228574

Text to Columns
Text to Columns : http://www.ooomacros.org/user.php#104183
Text2Columns - fixed width : http://www.ooomacros.org/user.php#133089

Sorting : http://www.oooforum.org/forum/viewtopic.phtml?t=55487

Text file - Opening a tab delimited file : http://www.oooforum.org/forum/viewtopic.phtml?t=55512

*** ### ***

Special Cases

Arrays - Copying part of an array not possible :
http://www.oooforum.org/forum/viewtopic.phtml?t=56489
http://www.oooforum.org/forum/viewtopic.phtml?t=56632

File links : http://www.oooforum.org/forum/viewtopic.phtml?t=57191
http://www.oooforum.org/forum/viewtopic.phtml?t=57956
(see for Excel term Workspaces)

Hyperlink : Calc has a HYPERLINK function, not recognised by Excel : http://www.oooforum.org/forum/viewtopic.phtml?p=229014#229014

Special Cells :
ooomacros wrote:
SpecialCells provides two dialogues for selecting and navigating spreadsheet-ranges by contents, visibility and formatting. Languages (GUI and help): German, English. Localization to other languages is possible.
Villeroy wrote:
SpecialCells is more like Excel's special cells dialog. It just shows and selects the locations of many different kinds of cells. It does not change or save anything in the document.
http://www.ooomacros.org/user.php#221020

SUM button : http://www.oooforum.org/forum/viewtopic.phtml?p=228805#228805

VLOOKUP : http://www.oooforum.org/forum/viewtopic.phtml?t=55351
_________________
search forum by month
Back to top
View user's profile Send private message
exactt
General User
General User


Joined: 14 Mar 2007
Posts: 15

PostPosted: Tue Jun 12, 2007 2:16 am    Post subject: Reply with quote

i would like to add thread http://www.oooforum.org/forum/viewtopic.phtml?t=15255 about drawing/deleting cell borders using something like the pencil/rubber function in excel. i just posted to the dev-api mailing lists about creating the functions. i wlll keep the mentioned thread updated.

thx
Back to top
View user's profile Send private message
OfficeProfessor
General User
General User


Joined: 10 Oct 2007
Posts: 6

PostPosted: Thu Oct 11, 2007 3:37 am    Post subject: Reply with quote

And certainly this thread:

http://www.oooforum.org/forum/viewtopic.phtml?t=64019

About 3D charts from Calc and the brand new charting engine in 2.3 looking ugly and jagged in prints and PDF. Useless.
Back to top
View user's profile Send private message
huwg
Super User
Super User


Joined: 14 Feb 2007
Posts: 890

PostPosted: Thu Oct 11, 2007 3:51 am    Post subject: Reply with quote

Accounting format : http://www.oooforum.org/forum/viewtopic.phtml?t=25688
Back to top
View user's profile Send private message
vasjpan2
Newbie
Newbie


Joined: 18 Oct 2007
Posts: 4

PostPosted: Thu Oct 18, 2007 2:30 pm    Post subject: References and Calculations $B$6 Reply with quote

I put myself on a limb and recommended Calc for a nonprofit.
We had a function like .34*b6/sum($b$6:$b$254) in Excel in cells g6:g254
Calc did not give the result we had in Excel, only Zero.
After weeks of my assuring them Calc would be fine,
they had to go out near a board deadline and buy Excel.
I not only have egg on my face,
but I have absolutely no idea why this would not work.
It does not make sence. It happens with both the PortableApps
version and the regular version of Calc.
All the docs for OO say it should work, no?

Now, as I was testing I found out another thing that didn't work like most
Spreadsheets I'd used before (I go back to MultiPlan and SUperCalc.. well,
even ADR EMpires, but it wasn't on-screen): I hit a + and then point to
another cell to get the address and it just goes to the cell, not give me the address.


I'm hoping all of this is because of some compatibility indicator I have to switch on, or equally simple..
Back to top
View user's profile Send private message
acknak
Moderator
Moderator


Joined: 13 Aug 2004
Posts: 4295
Location: ~ 40°N,75°W

PostPosted: Thu Oct 18, 2007 5:29 pm    Post subject: Reply with quote

There is no such thing as a "drop in replacement" for any piece of software as complex as a spreadsheet, and certainly Calc is not one. Such a migration should not be attempted without planning for mysterious things to fail--however, most of the mysteries can be solved, with time. If you go in without the time and expertise to solve the problems, you are doomed to be stuck with Excel.

Quote:
Calc did not give the result we had in Excel, only Zero.

You can check the thread linked above: "Numbers entered as text" for more detail and some suggestions, but I'll bet this is our old friend "numeric text": in Excel, text that looks like a number is converted to the value of the number, while in Calc, it is converted to zero. The situation in Excel is almost always a mistake during data entry, but Excel does you a favor and performs the calculation anyway, where Calc is unforgiving.

Quote:
... I hit a + and then point to
another cell to get the address and it just goes to the cell, not give me the address.

This feature is due to arrive in the next release of Calc.

If you want any firm answers to debugging the problems, you'll need to find a place to share the document (or a sample of it).
Back to top
View user's profile Send private message
CHPCASTELLON
Power User
Power User


Joined: 10 Sep 2007
Posts: 51
Location: SPAIN

PostPosted: Wed Oct 24, 2007 1:24 am    Post subject: Re: Migration issues : Calc's idiosyncrasies Reply with quote

Good morning,
I'm beginning to migrate from MS Excel to Calc. The users who already have migrated, are telling me the issues that they have seen.
For example:
1.- to insert a formula, they have to write (=) when in excel it's not necessary
2.- when they have two columns with two cells in the same file combined, they can't insert a column between the two columns. In excel they can.
3.- when they open a excel document with calc, the width of the columns is not the same as the original width.

If you want I tell you the issues emerging, let me know.

Thanks.
_________________
Lorena Monraval
Consorcio Hospitalario Provincial Castellon
Back to top
View user's profile Send private message
huwg
Super User
Super User


Joined: 14 Feb 2007
Posts: 890

PostPosted: Wed Oct 24, 2007 1:47 am    Post subject: Reply with quote

CHPCASTELLON wrote:
1.- to insert a formula, they have to write (=) when in excel it's not necessary
2.- when they have two columns with two cells in the same file combined, they can't insert a column between the two columns. In excel they can.
3.- when they open a excel document with calc, the width of the columns is not the same as the original width.

  1. issue 20496, in next release (see acknaks post directly above yours).
  2. Issue 8302, which is only a proposal on Calc's To Do List, so don't hold your breath.
  3. Has been discussed on this forum more than once. Unfortunately not a surprise it remains a problem. Issue 76881?

Edit: added quote for clarity


Last edited by huwg on Tue Nov 27, 2007 2:02 am; edited 1 time in total
Back to top
View user's profile Send private message
jrkrideau
Super User
Super User


Joined: 08 Aug 2005
Posts: 6732
Location: Kingston ON Canada

PostPosted: Wed Oct 24, 2007 4:39 am    Post subject: Re: References and Calculations $B$6 Reply with quote

vasjpan2 wrote:
I put myself on a limb and recommended Calc for a nonprofit.
We had a function like .34*b6/sum($b$6:$b$254) in Excel in cells g6:g254
Calc did not give the result we had in Excel, only Zero.
After weeks of my assuring them Calc would be fine,
they had to go out near a board deadline and buy Excel.
I not only have egg on my face,
but I have absolutely no idea why this would not work.


It does not work because somewhere in the Excel spreadsheet they have included a text formated cell in the equation. Calc and Excel handle these differently. IMO both are wrong but that's another matter.

See http://ca.geocities.com/jrkrideau/OpenOffice/spreadsheet_problems.pdf


Quote:
It does not make sence. It happens with both the PortableApps
version and the regular version of Calc.
All the docs for OO say it should work, no?


It's sloppy programming in Excel. The same sloppy programming in Calc gets you another result. Neither is good.

Quote:
Now, as I was testing I found out another thing that didn't work like most
Spreadsheets I'd used before (I go back to MultiPlan and SUperCalc.. well,
even ADR EMpires, but it wasn't on-screen): I hit a + and then point to
another cell to get the address and it just goes to the cell, not give me the address.


I'm hoping all of this is because of some compatibility indicator I have to switch on, or equally simple..

_________________
jrkrideau
Kingston ON Canada
Currently using Windows 7 & OOo 3.4.0 and Ubuntu 12.04 & LibreOffice 3.5.2.2
Back to top
View user's profile Send private message
huwg
Super User
Super User


Joined: 14 Feb 2007
Posts: 890

PostPosted: Wed Nov 07, 2007 1:30 am    Post subject: Reply with quote

Regular Expressions / Regex / Regexp :
Regular Expressions in Calc - OO.o Wiki
Find cells NOT containing ...
Finding whitespace
How are line breaks and carriage returns handled?
Why is the OOo Regular Expressions syntax so odd?
Back to top
View user's profile Send private message
lufavara
Newbie
Newbie


Joined: 03 Dec 2007
Posts: 1

PostPosted: Mon Dec 03, 2007 7:33 am    Post subject: Reply with quote

huwg wrote:
CHPCASTELLON wrote:
1.- to insert a formula, they have to write (=) when in excel it's not necessary
2.- when they have two columns with two cells in the same file combined, they can't insert a column between the two columns. In excel they can.
3.- when they open a excel document with calc, the width of the columns is not the same as the original width.

  1. issue 20496, in next release (see acknaks post directly above yours).
  2. Issue 8302, which is only a proposal on Calc's To Do List, so don't hold your breath.
  3. Has been discussed on this forum more than once. Unfortunately not a surprise it remains a problem. Issue 76881?

Edit: added quote for clarity


Why using Openoffice in Linux Mandriva we can insert a formula whithout usign (=) but (+) ore (-) ... In windows xp and ubuntu we can put (=)?????
Back to top
View user's profile Send private message
huwg
Super User
Super User


Joined: 14 Feb 2007
Posts: 890

PostPosted: Mon Dec 03, 2007 8:12 am    Post subject: Reply with quote

lufavara wrote:
Why using Openoffice in Linux Mandriva we can insert a formula whithout usign (=) but (+) ore (-) ... In windows xp and ubuntu we can put (=)?????
Some Linux distros don't include the standard OpenOffice - they have a customised compile of it. The current standard release of OpenOffice still requires = at the beginning of an input equation. Release 2.4 is expected to accept formulas without = at the start.
Back to top
View user's profile Send private message
JJJoseph
Power User
Power User


Joined: 25 Sep 2005
Posts: 81

PostPosted: Thu Jan 03, 2008 7:44 am    Post subject: OO Open File Reply with quote

I hope that OO can fix the "File Open" dialog. OO tries to intelligently open files, but the tool is more trouble than it's worth. It will open an unrecognized tab delimited file in OO Writer for example, when I want it to open as a spreadsheet. The resulting hassle is usually much worse than a simple error message would be. OO also will list ALL file-types in "Recent Documents" instead of just spreadsheets.This, too, is a really irritating module!
Back to top
View user's profile Send private message
mfaynberg
General User
General User


Joined: 20 Dec 2007
Posts: 28
Location: Campbell, CA USA

PostPosted: Wed Jan 09, 2008 11:32 am    Post subject: Another migration issue Reply with quote

You are writing about the Calc HYPERLINK function, which Excel does not recognize. What about the opposite? I open an Excel spreadsheet during the migration from MS Office to StartOffice - and alas! all Excel hyperlinks are lost! Sad Any ideas?
Thanks,
Mike
Back to top
View user's profile Send private message
KentG
Newbie
Newbie


Joined: 23 May 2008
Posts: 2

PostPosted: Fri May 23, 2008 11:06 am    Post subject: Re: OO Open File Reply with quote

JJJoseph wrote:
I hope that OO can fix the "File Open" dialog. OO tries to intelligently open files, but the tool is more trouble than it's worth. It will open an unrecognized tab delimited file in OO Writer for example, when I want it to open as a spreadsheet. The resulting hassle is usually much worse than a simple error message would be. OO also will list ALL file-types in "Recent Documents" instead of just spreadsheets.This, too, is a really irritating module!


I have the same problem with thousands of colon ( : ) delimited .dat files. I am currently scanning the forum for a elegant solution. Any ideas?
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group