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

Using a Excel created INDEX and MATCH formula

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
silver rose
General User
General User


Joined: 16 Aug 2008
Posts: 5

PostPosted: Sun Aug 17, 2008 4:12 am    Post subject: Using a Excel created INDEX and MATCH formula Reply with quote

Hi

Could someone help me with this problem I have created a excel workbook comprising of numerous worksheets with 4 weeks to a worksheet. Each Week Staff enter customer and job details with invoice costing details into the required weeks worksheet. This file works brillantly in Excel. However it has to be converted to Open office.

First problem I have saved the Excel file created in Excel 2007 as a Excel 97 - 2003 file. I then opened this file in Open Office 2.4. With the following results


Problem 1.

=INDEX(WeeksRange;MATCH(A1;Week;0);MATCH(A2;DateRange;0))

= Err:504


This formula is located in Worksheet called "Wk 1 to 4" it looks up the Worksheet called "Changeable Fields", with the first match it looks up the word "Week" (aka data in cell A1 of worksheet Wk 1 to 4) from the name range "week" (located in worksheet Changeable Fields) , then with the second match it looks up the given date of cell A2 ( located in worksheet "Wk 1 to 4", and looks for the date in A2 in the name range DateRange (in worksheet Changeable Fields), once it has located the week range based on the date given in A2 the formula will return the week number. eg. 1 for Week 1.

I have used this same formula to look up other ranges and on the Changeable Fields worksheet returning the result to the relevant worksheet eg. "Wk 1 to 4" they all have returned the Err:504 when importing workbook into OpenOffice.

The Range "DateRange" is a header name for data located on the same row located below that is The Range "Week" is a header name for data located on the same row
looking along the DateRange row return the value below that date which equals the week number eg. 1.

Problem 2.

=INDEX(ServiceChargeRange;MATCH(P2;ServiceFeeRange;0);MATCH($B$1;ServiceFeeWeekRange;0))

same principle data is located in ranges located on Changeable Fields Worksheet

ServiceFeeWeekRange is again the number of the week eg 1,2,3 etc located along a row.
except this time the ServiceFeeRange is a defined range of Service fee codes going down column A with data located in multiple columns to the right of the first column.
again look up the week than look up the service fee code and return the given value eg. price in $.

One thing to note with both problem 1 & 2 column A is frozen using Freeze Pane. to enable easy scrolling.

Problem 3.

=IF(ISERROR((G451-M451)/M451),0,(G451-M451)/M451)

looks up the given weekly target and returns the gross % profit

*Note(cell M451 has this formula =INDEX(TargetRange,MATCH(I450,WeeklyLabourTarget,0),MATCH(L451,WeeksRange,0))
used to return the Weekly target looks up week number and returns value in Weekly Labour Target range.

Problem 4.

=SUMIFS($AM$348:$AM$447,$B$348:$B$447,"Mon",$C$348:$C$447,$U$450)

open office converted to this

=_xlfn.SUMIFS($E$6:$E$105;$B$6:$B$105;"Mon";$C$6:$C$105;$U$108)

I put in absolute cell ranges rather than range names hoping to minimise range use in openoffice. But to no success.
basically this funtion was written to ignore errors like #n/a. and to look up $B$6:$B$105 and look for "Mon" and lookup $C$6:$C$105 and look for $U$108 which is a persons name. and than for every Mon that that person worked total the invoice values corresponding to those days and return that total value.



Any Ideas how to make this and the other functions work would be very much appreciated.

Silver Rose
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sun Aug 17, 2008 5:13 am    Post subject: Reply with quote

A few points, not in any particular order:

1. You Saved As Excel 97-2003. Did you open it again it Excel before opening it in Calc? If not, it's worth trying, to see if it's a problem caused from Excel 2007 rather than in Calc opening it. It might also be useful to open it in Excel 2003 if you have a machine with a copy, to make sure it's converted back OK.

2. You have the error code 504. If you look in the Calc Help and Find "error codes", it will tell you:

Function parameter is not valid, for example, text instead of a number, or a domain reference instead of cell reference.

Calc is much fussier / more rigorous about numbers and text, and will not do any calculations on text even if it looks like a number. Excel is quite blase about doing calculations on text. Maybe some of your numbers are actually text. I think Excel has a feature to highlight such cells, so maybe revisit the file in Excel.

3. Excel 2003 on my work laptop doesn't have a SUMIFS( ) function. I guess on that basis, Calc won't know what it is either, hence the "_xlfn.SUMIFS". Is it a custom Excel function written by a colleague, or some sort of Add-In?

If you can address these, it might help us find some solutions. Sorry if this sounds unhelpful...
Back to top
View user's profile Send private message
silver rose
General User
General User


Joined: 16 Aug 2008
Posts: 5

PostPosted: Sun Aug 17, 2008 5:45 am    Post subject: A few points, not in any particular order: Reply with quote

Thankyou for your reply,
I have reopened and used the file many times as a Excel 97-2003 file in 2007 works fine. I am aware of the "Function parameter is not valid, for example, text instead of a number, or a domain reference instead of cell reference" however I can not get around the fact that its text as the search criteria.

So I was wonder what formula allows me to look up text?

The SUMIFS is a new feature for Excel to ignore errors if criteria is not meet with formula

I am hoping someone has a work around for that particular problem.

For the Range Names I converted some formulas to absolute cell references and it still seems unable to work?

any input is appreciated
Silver Rose
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sun Aug 17, 2008 6:59 am    Post subject: Reply with quote

I suggest breaking the formula down, to see which part doesn't work, unless you already know. Split

=INDEX(ServiceChargeRange;MATCH(P2;ServiceFeeRange;0);MATCH($B$1;ServiceFeeWeekRange;0))

into:

=INDEX(ServiceChargeRange; some spare cell ; some other spare cell)

and

=MATCH(P2;ServiceFeeRange;0)

=MATCH($B$1;ServiceFeeWeekRange;0)

in those spare cells.

This should help to see which are the cause rather than a symptom.

Also MATCH( ) requires the 2nd argument to be "a single row or column, or part of a single row or column." so make it one column? (I don't use MATCH very often.) Maybe INDEX is similar - I've not looked.
Back to top
View user's profile Send private message
silver rose
General User
General User


Joined: 16 Aug 2008
Posts: 5

PostPosted: Sun Aug 17, 2008 7:50 am    Post subject: Reply with quote

Hi

I went through the formula with these results

=INDEX(ServiceChargeRange; some spare cell ; some other spare cell) =Err:501

and

=MATCH(P2;ServiceFeeRange;0) =1

=MATCH($B$1;ServiceFeeWeekRange;0) =Err:504

what do those results conclude?

Silver rose
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Sun Aug 17, 2008 10:26 am    Post subject: Reply with quote

What should the second MATCH give? Temporarily overwrite that cell with the correct answer, and then see if the INDEX error is cured.

If so, then either change the named range to refer only to the first column of the ServiceFeeRange, or just create a new named range that refers only to the first column.

(See my previous post that MATCH seems to want to work with only a single column range.)

If no joy, please keep posting with your results.
Back to top
View user's profile Send private message
silver rose
General User
General User


Joined: 16 Aug 2008
Posts: 5

PostPosted: Sun Aug 17, 2008 5:22 pm    Post subject: Reply with quote

Hi this is what I am doing in the formula

Col A B C D E
Row 1 Job No. Day Tech Invoice $ Num Hrs to complete
2 100 Mon Bill 100 2
3 101 Mon Bob 50 1
4 102 Mon Bill 50 1
5 103 Mon Bob 150 1.5
6 104 Tue Bob 300 3
7 105 Wed Bill 50 1
8 106 Wed Bill 50 1
9 107 Thu Bob 100 2
10 108 Fir Bob 150 1.5

I want to look up the Day of the week.
Than lookup Bill
Than Total all invoices for Bill for that Day of Week

Any help is appreciated
Thanks
Silver Rose
Back to top
View user's profile Send private message
silver rose
General User
General User


Joined: 16 Aug 2008
Posts: 5

PostPosted: Sun Aug 17, 2008 6:10 pm    Post subject: Index and Match from Excel to Open Office Reply with quote

Hi

I thought if I would try and explain the problems in more detail.

Problem 1.
this is how the formula converted from Excel 2007 into OpenOffice 2.4

=_xlfn.SUMIFS($E$6:$E$105;$B$6:$B$105;"Mon";$C$6:$C$105;$U$108)

error #MACRO?

orginal formula
=SUMIFS($E$6:$E$105;$B$6:$B$105;"Mon";$C$6:$C$105;"Bill")


_________Col A _________B _____C______D_______ __E

Row ___1 ___Job No.____ Day____ Tech___Invoice $ ___Num Hrs to complete

-----------2----- 100---------- Mon------- Bill------100------------ 2
-----------3 -----101---------- Mon------- Bob---- 50------------- 1
-----------4----- 102---------- Mon------- Bill------ 50------------- 1
-----------5----- 103---------- Mon------- Bob---- 150------------ 1.5
-----------6----- 104---------- Tue-------- Bob---- 300------------ 3
-----------7------105---------- Wed------- Bill------ 50------------- 1
-----------8------ 106--------- Wed------- Bill------- 50------------ 1
-----------9------ 107--------- Thu-------- Bob------ 100---------- 2
----------10------ 108--------- Fri--------- Bob------ 150----------- 1.5

I want to look up the Day of the week.
Than lookup Bill
Than Total all invoices for Bill for that Day of Week

I normally would have sorted Column B first in order of Day of Week
Also in the actual working sheet not all columns are adjacent

Problem 2.

=INDEX(WeeksRange;MATCH(A1;Week;0);MATCH(A2;DateRange;0))

= Err:504
________A_______B__________C___________D___________E____________F__________G___------ETC
ROW-1---Date-----30/06/2008----06/07/2008----12/07/2008-----18/07/2008------24/07/2008---30/07/2008
ROW-2---Week----1---------------2-----------------3-----------------4-----------------5---------------6
(Data in Changeable Fields worksheet)

Row 1 is name defined as DateRange
Row 2 is name defined as WeeksRange

result should return week number eg. 1, 2, 3 etc


This formula is located in Worksheet called "Wk 1 to 4" it looks up the Worksheet called "Changeable Fields", with the first match it looks up the word "Week" (aka data in cell A1 of worksheet Wk 1 to 4) from the name range "week" (located in worksheet Changeable Fields) , then with the second match it looks up the given date of cell A2 ( located in worksheet "Wk 1 to 4", and looks for the date in A2 in the name range DateRange (in worksheet Changeable Fields), once it has located the week range based on the date given in A2 the formula will return the week number. eg. 1 for Week 1.

I have used this same formula to look up other ranges and on the Changeable Fields worksheet returning the result to the relevant worksheet eg. "Wk 1 to 4" they all have returned the Err:504 when importing workbook into OpenOffice.

The Range "DateRange" is a header name for data located on the same row located below that is The Range "Week" is a header name for data located on the same row
looking along the DateRange row return the value below that date which equals the week number eg. 1.

Problem 3.

Data
________A_______B__________C___________D___________E____________F__________G___------ETC
ROW-1---Date-----30/06/2008----06/07/2008----12/07/2008-----18/07/2008------24/07/2008---30/07/2008
ROW-2---Week----1---------------2-----------------3-----------------4-----------------5---------------6
ServiceChargeFee
------------SRC-1---90-------------90-----------------90---------------95----------------95
------------SRC-2---45-------------45-----------------45---------------45----------------45
------------SRC-3---50-------------50-----------------50---------------50-----------------50
------------SRC-4---100-----------100----------------100--------------105---------------105
------------SRC-5---120-----------120----------------120--------------125---------------125
------------SRC-6---90-------------90-----------------90----------------90----------------90
------------SRC-7---0---------------0------------------20----------------20----------------20
------------SRC-8---0---------------0------------------40----------------40----------------40

=INDEX(ServiceChargeRange;MATCH(P2;ServiceFeeRange;0);MATCH($B$1;ServiceFeeWeekRange;0))

Result should return price eg. SRC-4 on Week 1 is $100

same principle data is located in ranges located on Changeable Fields Worksheet

ServiceFeeWeekRange is again the number of the week eg 1,2,3 etc upto 52 weeks located along a row.
except this time the ServiceFeeRange is a defined range of Service fee codes going down column A with data located in multiple columns to the right of the first column.
again look up the week than look up the service fee code and return the given value eg. price in $.

One thing to note with all problems column A is frozen using Freeze Pane. to enable easy scrolling.

Any help is appreciated
Thanks
Silver Rose
Back to top
View user's profile Send private message
RickRandom
Super User
Super User


Joined: 27 Jan 2006
Posts: 1082
Location: UK

PostPosted: Mon Aug 18, 2008 12:49 pm    Post subject: Reply with quote

Problem 1:

=SUMPRODUCT( cell range with names in it = cell with Bill in it ; cell range with invoice values in it )

will give the total for Bill, and so on.

No need to sort.

Problem 2:

I can't follow what your formula does. Are you simply trying to get the week number. If so try:

=HLOOKUP( cell with date ; B1:G2 or whatever range ; 2 ; FALSE )

if the date is an exact match, or don't use the last FALSE if the nearest date will do.

Problem 3:

We'll look at this if we can progress 1 and 2....
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