Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Nested array functions?

Using Excel 2007 Beta 2.

I have two cell ranges (B2:G2 and N1:N13), each of which contains a list of
non-repeating integer values in ascending order. I want a formula that
computes the number of values in the first range that appear in the second
range.

If I use scratch cells (say, AA2:AE2), I can create an array formula:
{=MATCH(B2:G2,N1:N13,0)}
that fills the scratch cells with an index for each value in B2:G2 that
matches a value in N1:N13, and #N/A for each value in B2:G2 that is absent
from N1:N13. The value I want can then be computed by:
=COUNT(AA2:AE2)

Is there a way to nest these formulas so I don't have to define scratch
cells for the intermediate step? Is there some simpler means of computation
that I've overlooked?

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default Nested array functions?

Try this (normally entered, not an array):

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:G2,N1:N13,0))))

Biff

"John Beyer" wrote in message
...
Using Excel 2007 Beta 2.

I have two cell ranges (B2:G2 and N1:N13), each of which contains a list
of
non-repeating integer values in ascending order. I want a formula that
computes the number of values in the first range that appear in the second
range.

If I use scratch cells (say, AA2:AE2), I can create an array formula:
{=MATCH(B2:G2,N1:N13,0)}
that fills the scratch cells with an index for each value in B2:G2 that
matches a value in N1:N13, and #N/A for each value in B2:G2 that is absent
from N1:N13. The value I want can then be computed by:
=COUNT(AA2:AE2)

Is there a way to nest these formulas so I don't have to define scratch
cells for the intermediate step? Is there some simpler means of
computation
that I've overlooked?



  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default Nested array functions?

Or, simply:

=SUMPRODUCT(COUNTIF(N1:N13,B2:G2))

Or, the shortest formula (array entered):

=SUM(COUNTIF(N1:N13,B2:G2))

Biff

"Biff" wrote in message
...
Try this (normally entered, not an array):

=SUMPRODUCT(--(ISNUMBER(MATCH(B2:G2,N1:N13,0))))

Biff

"John Beyer" wrote in message
...
Using Excel 2007 Beta 2.

I have two cell ranges (B2:G2 and N1:N13), each of which contains a list
of
non-repeating integer values in ascending order. I want a formula that
computes the number of values in the first range that appear in the
second
range.

If I use scratch cells (say, AA2:AE2), I can create an array formula:
{=MATCH(B2:G2,N1:N13,0)}
that fills the scratch cells with an index for each value in B2:G2 that
matches a value in N1:N13, and #N/A for each value in B2:G2 that is
absent
from N1:N13. The value I want can then be computed by:
=COUNT(AA2:AE2)

Is there a way to nest these formulas so I don't have to define scratch
cells for the intermediate step? Is there some simpler means of
computation
that I've overlooked?





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Array Formula w/ COLUMN & MATCH FUNCTIONS SJT Excel Discussion (Misc queries) 4 June 26th 06 06:17 PM
large formula question - Max nested functions PCLIVE Excel Worksheet Functions 3 October 17th 05 04:20 PM
Nested IF and MID functions Jan Buckley Excel Worksheet Functions 2 June 16th 05 09:46 PM
Array functions ARGHH! Marc Fleury Excel Worksheet Functions 19 March 16th 05 10:43 PM
Array Functions from Alan Beban Josh O. Excel Worksheet Functions 13 February 5th 05 01:54 AM


All times are GMT +1. The time now is 06:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"