If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to reference variable range?
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. |
#2
|
|||
|
|||
How to reference variable range?
Your idea is clever. There are other ways, but they are not necessarily
"better". For example, a set of formulas like: =SUMPRODUCT((B9:B721H1)*(C9:C721="Tag2")) =SUMPRODUCT((B9:B721=H1)*(B9:B721H2)*(C9:C721="T ag2")) 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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
How to reference variable range?
"T. Valko" 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)):INDE X(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. |
#5
|
|||
|
|||
How to reference variable range?
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "WhatsUp31415" wrote in message ... "T. Valko" 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)):INDE X(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. |
Thread Tools | |
Display Modes | |
|
|