Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX - MATCH - OFFSET By Reference
Hi All,
I'm having troubles writing this array based on criteria I hold in Column B. {=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 2:$AW2,,,,COLUMN($W2:$AW2)-MIN(COLUMN($W2:$AW2))+1))$R2,0))} Example: Row 1 is The Header Row. Cell B2 = FRED Cell B3 = GREG Cell B4 = GREG Cell B5 = GREG Cell B6 = GREG Cell B7 = HAZY Cell B8 = HAZY If I use the formula above, the calcualtion for "FRED" is correct because I'm only looking at Row 2 in the formula. For "GREG" though, i need the formula to change to this in EACH of the GREG rows. {=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 3:$AW6,,,,COLUMN($W3:$AW6)-MIN(COLUMN($W3:$AW6))+1))SUM(R3:R6),0))} For "HAZY" {=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 7:$AW8,,,,COLUMN($W7:$AW8)-MIN(COLUMN($W7:$AW8))+1))SUM(R7:R8),0))} etc... Column B is always sorted alphabetical if that makes it easier. Any help would be much appreciated?? Cheers, Bam. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX - MATCH - OFFSET By Reference
Is this what you mean? Since the data is sorted by Column B, in
ascending order, assuming that B15 contains Fred, B16 contains Greg, and B17 contains Hazy, first define the following... Select C15 Insert Name Define Name: Col_R Refers to: =INDEX($R$2:$R$8,MATCH($B15,$B$2:$B$8,0)):INDEX($R $2:$R$8,MATCH($B15,$B$2 :$B$8,1)) Click Add Name: Data Refers to: =INDEX($W$2:$W$8,MATCH($B15,$B$2:$B$8,0)):INDEX($A W$2:$AW$8,MATCH($B15,$B $2:$B$8,1)) Click Ok Then try... C15, confirmed with CONTROL+SHIFT+ENTER, and copied down: =INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET(Dat a,,,,COLUMN(Data)-MIN(C OLUMN(Data))+1))SUM(Col_R),0)) or =INDEX($W$1:$AW$1,MATCH(TRUE,MMULT(TRANSPOSE(ROW(D ata)^0),MMULT(IF(Data< "",Data,0),(COLUMN(Data)=TRANSPOSE(COLUMN(Data))) +0))SUM(Col_R),0)) Adjust the ranges accordingly. Note that the second formula avoids the volatile function OFFSET. However, MMULT will return #VALUE! if the output of cells exceeds 5,460 for PC or 4,095 for the Mac. If this is the case, download and install the free add-in, Morefunc.xll, and use MMULT.EXT instead. Unfortunately, the add-in is not compatible with the Macintosh computer. -- Domenic http://www.xl-central.com In article , Bam wrote: Hi All, I'm having troubles writing this array based on criteria I hold in Column B. {=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 2:$AW2,,,,COLUMN($W2:$AW2)-M IN(COLUMN($W2:$AW2))+1))$R2,0))} Example: Row 1 is The Header Row. Cell B2 = FRED Cell B3 = GREG Cell B4 = GREG Cell B5 = GREG Cell B6 = GREG Cell B7 = HAZY Cell B8 = HAZY If I use the formula above, the calcualtion for "FRED" is correct because I'm only looking at Row 2 in the formula. For "GREG" though, i need the formula to change to this in EACH of the GREG rows. {=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 3:$AW6,,,,COLUMN($W3:$AW6)-M IN(COLUMN($W3:$AW6))+1))SUM(R3:R6),0))} For "HAZY" {=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 7:$AW8,,,,COLUMN($W7:$AW8)-M IN(COLUMN($W7:$AW8))+1))SUM(R7:R8),0))} etc... Column B is always sorted alphabetical if that makes it easier. Any help would be much appreciated?? Cheers, Bam. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
INDEX - MATCH - OFFSET By Reference
Thanks Domenic.
Once I started understanding what you were doing, i've manged to get it to do what i "think" i need. I hadn't defined names previously, so you've just enlightened me! I can use it on a lot of my spreadsheets, so much appreciated. Cheers, Bam. "Domenic" wrote: Is this what you mean? Since the data is sorted by Column B, in ascending order, assuming that B15 contains Fred, B16 contains Greg, and B17 contains Hazy, first define the following... Select C15 Insert Name Define Name: Col_R Refers to: =INDEX($R$2:$R$8,MATCH($B15,$B$2:$B$8,0)):INDEX($R $2:$R$8,MATCH($B15,$B$2 :$B$8,1)) Click Add Name: Data Refers to: =INDEX($W$2:$W$8,MATCH($B15,$B$2:$B$8,0)):INDEX($A W$2:$AW$8,MATCH($B15,$B $2:$B$8,1)) Click Ok Then try... C15, confirmed with CONTROL+SHIFT+ENTER, and copied down: =INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET(Dat a,,,,COLUMN(Data)-MIN(C OLUMN(Data))+1))SUM(Col_R),0)) or =INDEX($W$1:$AW$1,MATCH(TRUE,MMULT(TRANSPOSE(ROW(D ata)^0),MMULT(IF(Data< "",Data,0),(COLUMN(Data)=TRANSPOSE(COLUMN(Data))) +0))SUM(Col_R),0)) Adjust the ranges accordingly. Note that the second formula avoids the volatile function OFFSET. However, MMULT will return #VALUE! if the output of cells exceeds 5,460 for PC or 4,095 for the Mac. If this is the case, download and install the free add-in, Morefunc.xll, and use MMULT.EXT instead. Unfortunately, the add-in is not compatible with the Macintosh computer. -- Domenic http://www.xl-central.com In article , Bam wrote: Hi All, I'm having troubles writing this array based on criteria I hold in Column B. {=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 2:$AW2,,,,COLUMN($W2:$AW2)-M IN(COLUMN($W2:$AW2))+1))$R2,0))} Example: Row 1 is The Header Row. Cell B2 = FRED Cell B3 = GREG Cell B4 = GREG Cell B5 = GREG Cell B6 = GREG Cell B7 = HAZY Cell B8 = HAZY If I use the formula above, the calcualtion for "FRED" is correct because I'm only looking at Row 2 in the formula. For "GREG" though, i need the formula to change to this in EACH of the GREG rows. {=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 3:$AW6,,,,COLUMN($W3:$AW6)-M IN(COLUMN($W3:$AW6))+1))SUM(R3:R6),0))} For "HAZY" {=INDEX($W$1:$AW$1,MATCH(TRUE,SUBTOTAL(9,OFFSET($W 7:$AW8,,,,COLUMN($W7:$AW8)-M IN(COLUMN($W7:$AW8))+1))SUM(R7:R8),0))} etc... Column B is always sorted alphabetical if that makes it easier. Any help would be much appreciated?? Cheers, Bam. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/Match/Offset/Sum (maybe?) | Excel Discussion (Misc queries) | |||
OFFSET vs INDEX(MATCH(...)) | Excel Discussion (Misc queries) | |||
INDEX, MAX, OFFSET, MATCH | Excel Worksheet Functions | |||
index match offset? | Excel Worksheet Functions | |||
Index, Match, Offset? Not sure which to use | Excel Worksheet Functions |