|
Identify which column 'Large' finds a match in
I have a grid that shows categories along the top, and people down the left. For each person, the categories may show zero time, or increments up to 100. I then need the "percent of time" in decending order, so I'm using =large(array,1) followed by =large(array,2), etc. (see example, below) Now that I have all of these values, in addition to knowing what the percents are, I need to know which category each percent represents, so I can use it to color code my sheet. For example, any percents associated with category 1 might be red, regardless of whether that category ended up as the largest, second largest, (etc) number. I can't use MATCH because in some cases, I have multiple cells with the same value. For example: (red) (yel) (blu) (gre) (ora) (pur) (gry) Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7 Large1 Large2 Large3 etc Person A 0 25 50 25 0 0 0 50 25 25 Person B 25 0 25 0 0 25 25 25 25 25 Person C 10 70 0 10 10 0 0 70 10 10 Person D 0 0 0 0 30 40 30 40 30 30 I need to pull over the column info so that the final order is color coded as the following. Large1 Large2 Large3 blu yel gre red blu pur yel red gre pur ora gry How can I find out which cell LARGE is actually returning a value from? Thank you, Keith |
Identify which column 'Large' finds a match in
Let's assume:
B1:H1 = Catx B2:H2 = numbers To get the corresponding Cat for LARGE(B2:H2,{1,2,3}): Assume the first result is to appear in cell A7 then copied across to C7. Array entered** : =INDEX($B$1:$H$1,MATCH(LARGE($B2:$H2-COLUMN($B2:$H2)/10^10,COLUMNS($A7:A7)),$B2:$H2-COLUMN($B2:$H2)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. When then are ties the leftmost tie will be extracted first. -- Biff Microsoft Excel MVP "ker_01" wrote in message ... I have a grid that shows categories along the top, and people down the left. For each person, the categories may show zero time, or increments up to 100. I then need the "percent of time" in decending order, so I'm using =large(array,1) followed by =large(array,2), etc. (see example, below) Now that I have all of these values, in addition to knowing what the percents are, I need to know which category each percent represents, so I can use it to color code my sheet. For example, any percents associated with category 1 might be red, regardless of whether that category ended up as the largest, second largest, (etc) number. I can't use MATCH because in some cases, I have multiple cells with the same value. For example: (red) (yel) (blu) (gre) (ora) (pur) (gry) Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7 Large1 Large2 Large3 etc Person A 0 25 50 25 0 0 0 50 25 25 Person B 25 0 25 0 0 25 25 25 25 25 Person C 10 70 0 10 10 0 0 70 10 10 Person D 0 0 0 0 30 40 30 40 30 30 I need to pull over the column info so that the final order is color coded as the following. Large1 Large2 Large3 blu yel gre red blu pur yel red gre pur ora gry How can I find out which cell LARGE is actually returning a value from? Thank you, Keith |
Identify which column 'Large' finds a match in
First of all, *wow*.
If you don't mind, a quick question- I want to make sure I understand how the formula works, so I can adapt it to my spreadsheet (which is unfortunately a bit more complex than the example I posted). I get Index, and Large, and have no problem with array formulas. What I haven't figured out yet is (a) the purpose of the /10^10 in the formula. I'm totally lost on that one. (b) the first columns piece: COLUMN($B2:$H2) which would have a fixed width of 7, so can I just use the number seven there (assuming my data actually had seven columns)? Thank you!! Keith "T. Valko" wrote: Let's assume: B1:H1 = Catx B2:H2 = numbers To get the corresponding Cat for LARGE(B2:H2,{1,2,3}): Assume the first result is to appear in cell A7 then copied across to C7. Array entered** : =INDEX($B$1:$H$1,MATCH(LARGE($B2:$H2-COLUMN($B2:$H2)/10^10,COLUMNS($A7:A7)),$B2:$H2-COLUMN($B2:$H2)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. When then are ties the leftmost tie will be extracted first. -- Biff Microsoft Excel MVP "ker_01" wrote in message ... I have a grid that shows categories along the top, and people down the left. For each person, the categories may show zero time, or increments up to 100. I then need the "percent of time" in decending order, so I'm using =large(array,1) followed by =large(array,2), etc. (see example, below) Now that I have all of these values, in addition to knowing what the percents are, I need to know which category each percent represents, so I can use it to color code my sheet. For example, any percents associated with category 1 might be red, regardless of whether that category ended up as the largest, second largest, (etc) number. I can't use MATCH because in some cases, I have multiple cells with the same value. For example: (red) (yel) (blu) (gre) (ora) (pur) (gry) Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7 Large1 Large2 Large3 etc Person A 0 25 50 25 0 0 0 50 25 25 Person B 25 0 25 0 0 25 25 25 25 25 Person C 10 70 0 10 10 0 0 70 10 10 Person D 0 0 0 0 30 40 30 40 30 30 I need to pull over the column info so that the final order is color coded as the following. Large1 Large2 Large3 blu yel gre red blu pur yel red gre pur ora gry How can I find out which cell LARGE is actually returning a value from? Thank you, Keith |
Identify which column 'Large' finds a match in
A and B are tied together. As far as using 7 to replace COLUMN($B2:$H2), no.
Here's how it works... $B2:$H2-COLUMN($B2:$H2)/10^10 This is a way to break any ties so that we're able to distinguish between LARGE n and LARGE n to get the correct column header as the result. Let's look at a small example: ...........B..........C..........D 1.....Cat1.....Cat2.....Cat3 2......10.........20.........10 If you want the Cat corresponding to the top 3 values a typical lookup formula would not be able to distinguish between the two values of 10. The typical lookup formula will *always* find the first instance of 10 and the results would be like this: LARGE1 = 20, LOOKUP 20 = Cat2 LARGE2 = 10, LOOKUP 10 = Cat1 LARGE3 = 10, LOOKUP 10 = Cat1 So we use this expression to break any ties and make *every* value in the range a unique value: $B2:$D2-COLUMN($B2:$D2)/10^10 B2 - COLUMN(B2) / 10^10 = 10 - 2 / 10000000000 = 9.9999999998 C2 - COLUMN(C2) / 10^10 = 20 - 3 / 10000000000 = 19.9999999997 D2 - COLUMN(D2) / 10^10 = 10 - 4 / 10000000000 = 9.9999999996 Now we have all unique values to lookup: MATCH(LARGE({9.9999999998,19.9999999997,9.99999999 96},n),{9.9999999998,19.9999999997,9.9999999996},0 ) LARGE1 = 19.9999999997 = Cat2 LARGE2 = 9.9999999998 = Cat1 LARGE3 = 9.9999999996 = Cat3 -- Biff Microsoft Excel MVP "ker_01" wrote in message ... First of all, *wow*. If you don't mind, a quick question- I want to make sure I understand how the formula works, so I can adapt it to my spreadsheet (which is unfortunately a bit more complex than the example I posted). I get Index, and Large, and have no problem with array formulas. What I haven't figured out yet is (a) the purpose of the /10^10 in the formula. I'm totally lost on that one. (b) the first columns piece: COLUMN($B2:$H2) which would have a fixed width of 7, so can I just use the number seven there (assuming my data actually had seven columns)? Thank you!! Keith "T. Valko" wrote: Let's assume: B1:H1 = Catx B2:H2 = numbers To get the corresponding Cat for LARGE(B2:H2,{1,2,3}): Assume the first result is to appear in cell A7 then copied across to C7. Array entered** : =INDEX($B$1:$H$1,MATCH(LARGE($B2:$H2-COLUMN($B2:$H2)/10^10,COLUMNS($A7:A7)),$B2:$H2-COLUMN($B2:$H2)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. When then are ties the leftmost tie will be extracted first. -- Biff Microsoft Excel MVP "ker_01" wrote in message ... I have a grid that shows categories along the top, and people down the left. For each person, the categories may show zero time, or increments up to 100. I then need the "percent of time" in decending order, so I'm using =large(array,1) followed by =large(array,2), etc. (see example, below) Now that I have all of these values, in addition to knowing what the percents are, I need to know which category each percent represents, so I can use it to color code my sheet. For example, any percents associated with category 1 might be red, regardless of whether that category ended up as the largest, second largest, (etc) number. I can't use MATCH because in some cases, I have multiple cells with the same value. For example: (red) (yel) (blu) (gre) (ora) (pur) (gry) Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7 Large1 Large2 Large3 etc Person A 0 25 50 25 0 0 0 50 25 25 Person B 25 0 25 0 0 25 25 25 25 25 Person C 10 70 0 10 10 0 0 70 10 10 Person D 0 0 0 0 30 40 30 40 30 30 I need to pull over the column info so that the final order is color coded as the following. Large1 Large2 Large3 blu yel gre red blu pur yel red gre pur ora gry How can I find out which cell LARGE is actually returning a value from? Thank you, Keith |
Identify which column 'Large' finds a match in
Hi,
You may also use this array formula =INDEX($C$5:$G$6,1,IF(ISERROR(SMALL(IF($C6:$G6=H$6 ,{1,2,3,4,5}),COUNT($H6:H6))),MATCH(H6,$C6:$G6,0), SMALL(IF($C6:$G6=H$6,{1,2,3,4,5}),COUNT($H6:H6)))) C5:G6 has colors and numbers. H6:J6 has the results of the large formula which you have used. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "ker_01" wrote in message ... I have a grid that shows categories along the top, and people down the left. For each person, the categories may show zero time, or increments up to 100. I then need the "percent of time" in decending order, so I'm using =large(array,1) followed by =large(array,2), etc. (see example, below) Now that I have all of these values, in addition to knowing what the percents are, I need to know which category each percent represents, so I can use it to color code my sheet. For example, any percents associated with category 1 might be red, regardless of whether that category ended up as the largest, second largest, (etc) number. I can't use MATCH because in some cases, I have multiple cells with the same value. For example: (red) (yel) (blu) (gre) (ora) (pur) (gry) Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7 Large1 Large2 Large3 etc Person A 0 25 50 25 0 0 0 50 25 25 Person B 25 0 25 0 0 25 25 25 25 25 Person C 10 70 0 10 10 0 0 70 10 10 Person D 0 0 0 0 30 40 30 40 30 30 I need to pull over the column info so that the final order is color coded as the following. Large1 Large2 Large3 blu yel gre red blu pur yel red gre pur ora gry How can I find out which cell LARGE is actually returning a value from? Thank you, Keith |
Identify which column 'Large' finds a match in
Thank you for the additional explanation!
My actual worksheet is a little more complex than the basic example in the original post, and I was unable to correctly adapt the formula syntax to work... but based on your explanation, I took the n*10^10 out of the original data, and then used simplified formulas- I now have the labels in order, along with the data! I appreciate your (and Ashish's) responses. Best, Keith "T. Valko" wrote: A and B are tied together. As far as using 7 to replace COLUMN($B2:$H2), no. Here's how it works... $B2:$H2-COLUMN($B2:$H2)/10^10 This is a way to break any ties so that we're able to distinguish between LARGE n and LARGE n to get the correct column header as the result. Let's look at a small example: ...........B..........C..........D 1.....Cat1.....Cat2.....Cat3 2......10.........20.........10 If you want the Cat corresponding to the top 3 values a typical lookup formula would not be able to distinguish between the two values of 10. The typical lookup formula will *always* find the first instance of 10 and the results would be like this: LARGE1 = 20, LOOKUP 20 = Cat2 LARGE2 = 10, LOOKUP 10 = Cat1 LARGE3 = 10, LOOKUP 10 = Cat1 So we use this expression to break any ties and make *every* value in the range a unique value: $B2:$D2-COLUMN($B2:$D2)/10^10 B2 - COLUMN(B2) / 10^10 = 10 - 2 / 10000000000 = 9.9999999998 C2 - COLUMN(C2) / 10^10 = 20 - 3 / 10000000000 = 19.9999999997 D2 - COLUMN(D2) / 10^10 = 10 - 4 / 10000000000 = 9.9999999996 Now we have all unique values to lookup: MATCH(LARGE({9.9999999998,19.9999999997,9.99999999 96},n),{9.9999999998,19.9999999997,9.9999999996},0 ) LARGE1 = 19.9999999997 = Cat2 LARGE2 = 9.9999999998 = Cat1 LARGE3 = 9.9999999996 = Cat3 -- Biff Microsoft Excel MVP "ker_01" wrote in message ... First of all, *wow*. If you don't mind, a quick question- I want to make sure I understand how the formula works, so I can adapt it to my spreadsheet (which is unfortunately a bit more complex than the example I posted). I get Index, and Large, and have no problem with array formulas. What I haven't figured out yet is (a) the purpose of the /10^10 in the formula. I'm totally lost on that one. (b) the first columns piece: COLUMN($B2:$H2) which would have a fixed width of 7, so can I just use the number seven there (assuming my data actually had seven columns)? Thank you!! Keith "T. Valko" wrote: Let's assume: B1:H1 = Catx B2:H2 = numbers To get the corresponding Cat for LARGE(B2:H2,{1,2,3}): Assume the first result is to appear in cell A7 then copied across to C7. Array entered** : =INDEX($B$1:$H$1,MATCH(LARGE($B2:$H2-COLUMN($B2:$H2)/10^10,COLUMNS($A7:A7)),$B2:$H2-COLUMN($B2:$H2)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. When then are ties the leftmost tie will be extracted first. -- Biff Microsoft Excel MVP "ker_01" wrote in message ... I have a grid that shows categories along the top, and people down the left. For each person, the categories may show zero time, or increments up to 100. I then need the "percent of time" in decending order, so I'm using =large(array,1) followed by =large(array,2), etc. (see example, below) Now that I have all of these values, in addition to knowing what the percents are, I need to know which category each percent represents, so I can use it to color code my sheet. For example, any percents associated with category 1 might be red, regardless of whether that category ended up as the largest, second largest, (etc) number. I can't use MATCH because in some cases, I have multiple cells with the same value. For example: (red) (yel) (blu) (gre) (ora) (pur) (gry) Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7 Large1 Large2 Large3 etc Person A 0 25 50 25 0 0 0 50 25 25 Person B 25 0 25 0 0 25 25 25 25 25 Person C 10 70 0 10 10 0 0 70 10 10 Person D 0 0 0 0 30 40 30 40 30 30 I need to pull over the column info so that the final order is color coded as the following. Large1 Large2 Large3 blu yel gre red blu pur yel red gre pur ora gry How can I find out which cell LARGE is actually returning a value from? Thank you, Keith |
Identify which column 'Large' finds a match in
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "ker_01" wrote in message ... Thank you for the additional explanation! My actual worksheet is a little more complex than the basic example in the original post, and I was unable to correctly adapt the formula syntax to work... but based on your explanation, I took the n*10^10 out of the original data, and then used simplified formulas- I now have the labels in order, along with the data! I appreciate your (and Ashish's) responses. Best, Keith "T. Valko" wrote: A and B are tied together. As far as using 7 to replace COLUMN($B2:$H2), no. Here's how it works... $B2:$H2-COLUMN($B2:$H2)/10^10 This is a way to break any ties so that we're able to distinguish between LARGE n and LARGE n to get the correct column header as the result. Let's look at a small example: ...........B..........C..........D 1.....Cat1.....Cat2.....Cat3 2......10.........20.........10 If you want the Cat corresponding to the top 3 values a typical lookup formula would not be able to distinguish between the two values of 10. The typical lookup formula will *always* find the first instance of 10 and the results would be like this: LARGE1 = 20, LOOKUP 20 = Cat2 LARGE2 = 10, LOOKUP 10 = Cat1 LARGE3 = 10, LOOKUP 10 = Cat1 So we use this expression to break any ties and make *every* value in the range a unique value: $B2:$D2-COLUMN($B2:$D2)/10^10 B2 - COLUMN(B2) / 10^10 = 10 - 2 / 10000000000 = 9.9999999998 C2 - COLUMN(C2) / 10^10 = 20 - 3 / 10000000000 = 19.9999999997 D2 - COLUMN(D2) / 10^10 = 10 - 4 / 10000000000 = 9.9999999996 Now we have all unique values to lookup: MATCH(LARGE({9.9999999998,19.9999999997,9.99999999 96},n),{9.9999999998,19.9999999997,9.9999999996},0 ) LARGE1 = 19.9999999997 = Cat2 LARGE2 = 9.9999999998 = Cat1 LARGE3 = 9.9999999996 = Cat3 -- Biff Microsoft Excel MVP "ker_01" wrote in message ... First of all, *wow*. If you don't mind, a quick question- I want to make sure I understand how the formula works, so I can adapt it to my spreadsheet (which is unfortunately a bit more complex than the example I posted). I get Index, and Large, and have no problem with array formulas. What I haven't figured out yet is (a) the purpose of the /10^10 in the formula. I'm totally lost on that one. (b) the first columns piece: COLUMN($B2:$H2) which would have a fixed width of 7, so can I just use the number seven there (assuming my data actually had seven columns)? Thank you!! Keith "T. Valko" wrote: Let's assume: B1:H1 = Catx B2:H2 = numbers To get the corresponding Cat for LARGE(B2:H2,{1,2,3}): Assume the first result is to appear in cell A7 then copied across to C7. Array entered** : =INDEX($B$1:$H$1,MATCH(LARGE($B2:$H2-COLUMN($B2:$H2)/10^10,COLUMNS($A7:A7)),$B2:$H2-COLUMN($B2:$H2)/10^10,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. When then are ties the leftmost tie will be extracted first. -- Biff Microsoft Excel MVP "ker_01" wrote in message ... I have a grid that shows categories along the top, and people down the left. For each person, the categories may show zero time, or increments up to 100. I then need the "percent of time" in decending order, so I'm using =large(array,1) followed by =large(array,2), etc. (see example, below) Now that I have all of these values, in addition to knowing what the percents are, I need to know which category each percent represents, so I can use it to color code my sheet. For example, any percents associated with category 1 might be red, regardless of whether that category ended up as the largest, second largest, (etc) number. I can't use MATCH because in some cases, I have multiple cells with the same value. For example: (red) (yel) (blu) (gre) (ora) (pur) (gry) Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7 Large1 Large2 Large3 etc Person A 0 25 50 25 0 0 0 50 25 25 Person B 25 0 25 0 0 25 25 25 25 25 Person C 10 70 0 10 10 0 0 70 10 10 Person D 0 0 0 0 30 40 30 40 30 30 I need to pull over the column info so that the final order is color coded as the following. Large1 Large2 Large3 blu yel gre red blu pur yel red gre pur ora gry How can I find out which cell LARGE is actually returning a value from? Thank you, Keith |
| All times are GMT +1. The time now is 11:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com