Identify which column 'Large' finds a match in

[ker_01]

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

 

[T. Valko]

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.

 

[ker_01]

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]

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.9999999996},n),{9.9999999998,19.9999999997,9.9999999996},0)

LARGE1 = 19.9999999997 = Cat2
LARGE2 = 9.9999999998 = Cat1
LARGE3 = 9.9999999996 = Cat3

 

[Ashish Mathur]

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]

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]

You're welcome. Thanks for the feedback!