[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
tc
Newbie

Joined: 10 Oct 2006
Posts: 3

 Posted: Tue Oct 10, 2006 1:46 am    Post subject: xls foumulae 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. 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
noranthon
Super User

Joined: 07 Jul 2005
Posts: 3318

 Posted: Tue Oct 10, 2006 3:10 am    Post subject: 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
tc
Newbie

Joined: 10 Oct 2006
Posts: 3

 Posted: Tue Oct 10, 2006 4:36 am    Post subject: 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.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Tue Oct 10, 2006 4:53 am    Post subject:

 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.
noranthon
Super User

Joined: 07 Jul 2005
Posts: 3318

Posted: Tue Oct 10, 2006 5:12 am    Post subject:

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.

_________________
search forum by month
tc
Newbie

Joined: 10 Oct 2006
Posts: 3

Posted: Tue Oct 10, 2006 5:17 am    Post subject:

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.

My apologies for being so stupid then
David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Tue Oct 10, 2006 7:29 am    Post subject:

 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.

David.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
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