Match(NamedRange,NamedRange,0) doesn't work

[Jacophile]

Hi all, I hope someone can spot the problem here.

I have an array formula: {=SUM(IF(MATCH(Group,Group,0)=(ROW(Group)-ROW($C2)+1),1,0))}

where Group is: OFFSET(Groups!$C$2,0,0,Groups!$D$1)

and the array formula is entered in Groups!E1 and returns an #N/A error.

If I select the formula in the Formula bar and press F9 it evaluates to 48 which is the result I am looking for.

If it calculates OK using F9, why can't excel resolve it in the cell??

When I enter the match part as an array {=MATCH(Group,Group,0)} in an appropriately sized range, it works fine and returns the index of the first occurrence of the elements of the Groups array as it should.

The Groups array in column C is an array formula entered over 388 rows and contains:
{=IF($A2:$A389="","",INDEX(XCorel!$IC$2:$IC$232,$A2:$A389))}
where XCorel is another sheet in the same workbook.

Can anyone suggest why this might be?
If I replace the array formula in column C (Group) with its values the problem goes away, but I want it to be dynamic.

 

[Aladin Akyurek]

What is in Groups!$D$1?

And what does the array formula compute?

 

[Jacophile]

Hi, thanks for the reply, I have just finished installing excel jeanie so here is the layout:

Excel Workbook
	A	B	C	D	E	F	G	H
1	0.8	Count	Group	201	#N/A	48	#N/A	201
2	3	3	139		1	1		1
Groups

Here is a snippet from the table in the XCorel sheet that these formulae are reading:

Excel Workbook
	HZ	IA	IB	IC
1	Group Count	Group'	Leaf	Group
2	0		FALSE
3	0		FALSE
XCorel

If I do copy, paste values into column IC here, the problem also goes away.

 

[Aladin Akyurek]

Please provide the answers without Excel Jeanie...

 

[Jacophile]

Basically I have 231 items, some of which are co-related, the ones that are co-related are in a group which is uniquely defined by the ultimate member of that group.

Groups!D1 contains the count of the 231 items that are in a group, in this case 201. In other words 30 of the items are not co-related with any other item.

Column A in the Groups sheet contains a list of all items that qualify as a group member and Column C contains which group they are in.

The array formula is to compute the number of unique elements in column C. So it just counts the number of elements from the match(group,group,0) formula that match the index number in group, i.e. it counts the first occurrences of the elements in group.

 

[Jacophile]

In other words Groups!D1 is the number of elements in the list in column C...

 

[Aladin Akyurek]

In other words Groups!D1 is the number of elements in the list in column C...

If that cell house a formula, I do want to see that...

BTW, a better way of counting distinct items is:

=SUM(IF(FREQUENCY(IF(A1:A5<>"",MATCH("~"&A1:A5,A1:A5&"",0)),ROW(A1:A5)-ROW(A1)+1),1))

 

[Jacophile]

Groups!D1 is a reference to a named range in the XCorel sheet containing the formula =COUNT(IC2:IC232). The development of column IC is shown in the excel jeanie post above.

 

[Jacophile]

The other method you suggest for computing the count has the exact same behavior as mine: when I enter it initially it has the correct result, but as soon as I hit F9 and the sheet recalculates, it reverts to #N/A. It is in I1 below.

Excel Workbook
	A	B	C	D	E	F	I
1	0.8	Count	Group	201	#N/A	48	#N/A
2	3	3	139	231	1	1
Groups

 

[Aladin Akyurek]

Groups!D1 is a reference to a named range in the XCorel sheet containing the formula =COUNT(IC2:IC232). The development of column IC is shown in the excel jeanie post above.

Is the result of this formula

=COUNT(IC2:IC232)

used in the definition of Group, which is:

OFFSET(Groups!$C$2,0,0,Groups!$D$1)

If so, why are you not using the C-range itself in Groups directly?