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

xls foumulae

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
tc
Newbie
Newbie


Joined: 10 Oct 2006
Posts: 3

PostPosted: Tue Oct 10, 2006 1:46 am    Post subject: xls foumulae Reply with quote

Hi all,

I'm new to Open Office and this is my first post in these forums, so I'm sure you'll all be nice to me. Very Happy It maybe that the question I'm about to ask has been answered elsewhere and I apologise for that, but the information on the forums is quite detailed and a search has not provided me with the answer that I need. If the question has been answered previously I would be grateful for a link to the post.

I am looking to use Open Office at work in place of Office 2000 and am pretty certain that using Writer will not be a problem for me. However, the problem that I do have is with forms that I have created in Excel and converting them to Calc. I've looked on the forums and following that I have run the Document Converter Wizard, however, some of the formulae in the spreadsheets do not work an I assume is down to some sort of convention. I would therefore be grateful if someone could look at the following sums and tell me whether converting it to OO is going to be easy or whether the formula will have to be completely re-written:

=SUM(IF($'CLAIM DETAILS'.$E$18:$E$44="AT";IF($'CLAIM DETAILS'.$F$18:$F$44=1;$'CLAIM DETAILS'.$G$18:$G$44;0);0))

=INDEX($'LOOK UP TABLES'.$B$4:$R$14;MATCH(E18;$'LOOK UP TABLES'.$B$4:$B$14);MATCH(F18;$'LOOK UP TABLES'.$B$4:$R$4))

There are other lookup functions and tables within the form that work fine under OO, however, the above sums don't.

Any help, hints or tips would be most welcome
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Tue Oct 10, 2006 3:10 am    Post subject: Reply with quote

You will find Help for those functions under the names SUMIF, INDEX and MATCH.

Whether SUMIF has displaced SUM(IF, I don't know.

This is an INDEX ... MATCH formula from one of my spreadsheets:
=INDEX(_TableCodes;MATCH("SBS";_TableCodes;0))

The INDEX function can contain a 3rd parameter (column) and a 4th (range).
_________________
search forum by month
Back to top
View user's profile Send private message
tc
Newbie
Newbie


Joined: 10 Oct 2006
Posts: 3

PostPosted: Tue Oct 10, 2006 4:36 am    Post subject: Reply with quote

Hhhmmmm,

Been messing around with the formulae and think I have the answer, in that there appears to be nothing wrong with them at all.

What I've found is that the table needs to be sorted in OO for the formula to work. In Excel, the rows were unsorted and arranged as such : -

X
AT
PR
AD
TR
WA
LE
AC
TC
AF

and this works fine. However, in OO I have had to sort the rows into ascending order to get the lookup function to work. The rows now look like this: -

AC
AD
AF
AT
LE
PR
TC
TR
WA
X

I have put in some test values and the formula appears to be working fine. I thought I'd post what I found here in case someone else has a similar problem. Of course, ANY help, tips and tricks would still be welcome.
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Tue Oct 10, 2006 4:53 am    Post subject: Reply with quote

tc wrote:

What I've found is that the table needs to be sorted in OO for the formula to work. In Excel, the rows were unsorted and arranged as such : -


A search through this forum might show you that you would not need to actually sort, but need a fourth parameter in the LOOKUP() function.

David.
Back to top
View user's profile Send private message
noranthon
Super User
Super User


Joined: 07 Jul 2005
Posts: 3318

PostPosted: Tue Oct 10, 2006 5:12 am    Post subject: Reply with quote

The function MATCH, if you look at Help, has a third parameter which relates in part to sort order. Since you did not have any third parameter, it is assumed that the column is sorted in ascending order.

Further, according to Help:
Quote:
This corresponds to the same function in Microsoft Excel.


It's all there in black and white (well, navy blue and off white on my desktop).

The other functions do not have such a parameter.

No need for anyone to read this thread if they read Help.
_________________
search forum by month
Back to top
View user's profile Send private message
tc
Newbie
Newbie


Joined: 10 Oct 2006
Posts: 3

PostPosted: Tue Oct 10, 2006 5:17 am    Post subject: Reply with quote

noranthon wrote:
The function MATCH, if you look at Help, has a third parameter which relates in part to sort order. Since you did not have any third parameter, it is assumed that the column is sorted in ascending order.

Further, according to Help:
Quote:
This corresponds to the same function in Microsoft Excel.


It's all there in black and white (well, navy blue and off white on my desktop).

The other functions do not have such a parameter.

No need for anyone to read this thread if they read Help.


My apologies for being so stupid then
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Tue Oct 10, 2006 7:29 am    Post subject: Reply with quote

tc wrote:
My apologies for being so stupid then


If you are, we all are. I'm forever looking in the refrigerator saying there's nothing to eat, then my wife points to the pile of food front and center. Smile

David.
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
Page 1 of 1

 
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