How to reference variable range?
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)):INDE X(B9:B721,MATCH(G1,C9:C721,0)+COUNTIF(C9:C721,G1)-1),H1:H5)
--
Biff
Microsoft Excel MVP
"WhatsUp31415" wrote in message
...
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.
|