Seek highest value, but reflect column header instead

  • Thread starter Thread starter ManojB
  • Start date Start date
M

ManojB

Hello,

first let me insert the basic framework of my excel sheet:
A: B: C: D: E: F: G: H: I: J:
1: 101 105 805 813 890 1st 2nd 3rd
2: Oct 560 420 390 740 620 813 890 101
3: Nov 120 739 660 320 460 105 805 890

Ok, here's what I'm trying to do. The rows (Oct - in reality they go
from Oct down to the next september) reflect total number of absentee
hours. The header row (101, 105....890) reflects department numbers
(in reality there are over 50 different department numbers/codes).

The columns labeled 1st, 2nd and 3rd should reflect the departments
with the highest, 2nd highest and 3rd highest values.

I've tried using the LARGE function ("=LARGE(B1:F1,1" for 1st and so
forth), but obviously, cells H1 through J1 only reflect the top three
values. What I really want to show in these cells is the
corresponding Department Numbers (101, 105...890)for each month. Over
a long period, this will then allow me to patterns in
attendance/absenteeism, further allowing us to take necessary steps to
manage problem areas.

Further to just being able to see this data, once it's listed under
1st, 2nd and 3rd (and most likely through 5th), I plan to chart this
in a monthly basis, which is why I'd prefer to do each month's 1st all
in one cell (for the month) instead of first looking up the highest
value in 1 cell and then in another cell doing a lookup.

I'm thinking there HAS to be a way, but obviously, I don't know it.

Please help.

If you need to discuss this with me further, just post here (and avoid
email) and I'll respond.

Thanks in advance,

Manoj
 
a b c d e f g
h i
1 2000 1999 1998 1997 1996 1995 High Sales HS Yr
2 apple 50 20 10 30 60 40 60
?
3 pear 10 80 90 30 20 50 9

4 peach 60 40 30 20 80 10 80

cell h2 = Large(b2:g2,1)

How can I return in i2 the year in which the high sales occured?
(in the above example I would want to return "1997"
 
one way where it starts at column N instead of A. Change the ,3 to ,1, and
,2

=OFFSET($N$1,0,MATCH(LARGE($N$2:$Q$2,3),$N$2:$Q$2,0)-1)
 
ManojB,

If I understand you correctly you want to automatically fill in
cells H2:J3.

In H1 =INDEX($B$1:$F$1,1,MATCH((LARGE($B2:$F2,1)),$B2:$F2,0))
In I1 =INDEX($B$1:$F$1,1,MATCH((LARGE($B2:$F2,2)),$B2:$F2,0))
In J1 =INDEX($B$1:$F$1,1,MATCH((LARGE($B2:$F2,3)),$B2:$F2,0))

The match returns the column # for the match with the 1st, 2nd or 3rd
largest and the index returns the value from the header.

You can drag this down for each row.

Dan E
 
Dan,

Thank you very much. If I were a woman, and I liked men, I'd offer to
have your children, but in the absence of these criteria, I very
masculine thanks very much for this will have to do. You've saved me
lots of work!

Manoj
 
Back
Top