How to reference variable range?

WhatsUp31415 · External Since: May 17, 2009 Posts: 2

I have one column of data (B9:B721) and a parallel column of tags (C9:C721).
The data are arranged with all of "Tag1" data, followed by all of "Tag2"
data, etc.

I want to be able to reference all of the data for "Tag1", or all of the
data for "Tag2", etc. For example:

=FREQUENCY(tagrange,H1:H15)

Currently, I use INDIRECT to construct tagrange:

INDIRECT("B" & MIN(IF(C9:C721="TAG2",ROW(C9:C721))) &
":B" & MAX(IF(C9:C721="TAG2",ROW(C9:C721))))

Is there a better way?

The ideal variable reference would not require an array formula, as the
MIN/IF usage above does, even though the example, FREQUENCY, must be an
array formula anyway.

Gary''s Student · External Since: Oct 01, 2005 Posts: 3350

Your idea is clever. There are other ways, but they are not necessarily
"better". For example, a set of formulas like:

=SUMPRODUCT((B9:B721<H1)*(C9:C721="Tag2"))
=SUMPRODUCT((B9:B721>=H1)*(B9:B721<H2)*(C9:C721="Tag2"))

is another way, but is it "better" ?? Your technique works because your
data is pre-sorted by tag.

--
Gary''s Student - gsnu2007xx

"WhatsUp31415" wrote:

> I have one column of data (B9:B721) and a parallel column of tags (C9:C721).
> The data are arranged with all of "Tag1" data, followed by all of "Tag2"
> data, etc.
>
> I want to be able to reference all of the data for "Tag1", or all of the
> data for "Tag2", etc. For example:
>
> =FREQUENCY(tagrange,H1:H15)
>
> Currently, I use INDIRECT to construct tagrange:
>
> INDIRECT("B" & MIN(IF(C9:C721="TAG2",ROW(C9:C721))) &
> ":B" & MAX(IF(C9:C721="TAG2",ROW(C9:C721))))
>
> Is there a better way?
>
> The ideal variable reference would not require an array formula, as the
> MIN/IF usage above does, even though the example, FREQUENCY, must be an
> array formula anyway.
>
>

T. Valko · External Since: Nov 24, 2006 Posts: 3429

Couple of ways...

This method is shorter but is volatile (recalculates *every* time a
calculation is triggered):

G1 = TagX

=FREQUENCY(OFFSET(B9,MATCH(G1,C9:C721,0)-1,,COUNTIF(C9:C721,G1)),H1:H5)

This method is longer but isn't volatile:

=FREQUENCY(INDEX(B9:B721,MATCH(G1,C9:C721,0)):INDEX(B9:B721,MATCH(G1,C9:C721,0)+COUNTIF(C9:C721,G1)-1),H1:H5)

--
Biff
Microsoft Excel MVP

"WhatsUp31415" <whatsup31415.TakeThisOut@live.com> wrote in message
news:%233TcD8s1JHA.4632@TK2MSFTNGP02.phx.gbl...
>I have one column of data (B9:B721) and a parallel column of tags
>(C9:C721). The data are arranged with all of "Tag1" data, followed by all
>of "Tag2" data, etc.
>
> I want to be able to reference all of the data for "Tag1", or all of the
> data for "Tag2", etc. For example:
>
> =FREQUENCY(tagrange,H1:H15)
>
> Currently, I use INDIRECT to construct tagrange:
>
> INDIRECT("B" & MIN(IF(C9:C721="TAG2",ROW(C9:C721))) &
> ":B" & MAX(IF(C9:C721="TAG2",ROW(C9:C721))))
>
> Is there a better way?
>
> The ideal variable reference would not require an array formula, as the
> MIN/IF usage above does, even though the example, FREQUENCY, must be an
> array formula anyway.

WhatsUp31415 · External Since: May 17, 2009 Posts: 2

"T. Valko" <biffinpitt.RemoveThis@comcast.net> wrote:
> =FREQUENCY(OFFSET(B9,MATCH(G1,C9:C721,0)-1,,COUNTIF(C9:C721,G1)),H1:H5)

Thanks. I had wanted to use OFFSET, but I could not make it work, even with
constants. I must have made some mistakes. In any case, I had not thought
of using MATCH.

> This method is longer but isn't volatile:
> =FREQUENCY(INDEX(B9:B721,MATCH(G1,C9:C721,0)):INDEX(B9:B721,MATCH(G1,C9:C721,0)+COUNTIF(C9:C721,G1)-1),H1:H5)

I like the fact that this is not volatile.

Thanks again. And thanks for understanding the question.

T. Valko · External Since: Nov 24, 2006 Posts: 3429

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

"WhatsUp31415" <whatsup31415.RemoveThis@live.com> wrote in message
news:%23bW74%23w1JHA.1716@TK2MSFTNGP03.phx.gbl...
> "T. Valko" <biffinpitt.RemoveThis@comcast.net> wrote:
>> =FREQUENCY(OFFSET(B9,MATCH(G1,C9:C721,0)-1,,COUNTIF(C9:C721,G1)),H1:H5)
>
> Thanks. I had wanted to use OFFSET, but I could not make it work, even
> with constants. I must have made some mistakes. In any case, I had not
> thought of using MATCH.
>
>
>> This method is longer but isn't volatile:
>> =FREQUENCY(INDEX(B9:B721,MATCH(G1,C9:C721,0)):INDEX(B9:B721,MATCH(G1,C9:C721,0)+COUNTIF(C9:C721,G1)-1),H1:H5)
>
> I like the fact that this is not volatile.
>
> Thanks again. And thanks for understanding the question.