finding max amounts

  • Thread starter Thread starter snax500
  • Start date Start date
S

snax500

In Excel2000, I have the following:

Weeks 1 2 3 Max Wk #
contract1 20 3 40 40
contract2 20 3 10 20

I want to find the max amount for each contract. I can do this using
the =max function. I also want to find the week that coincides with
the max amount. So I want the above to look like this:

Weeks 1 2 3 Max Wk #
contract1 20 3 40 40 3
contract2 20 3 10 20 1

What function can I use to find the max amount and then look up the
week?

Thanks
 
if "Weeks" is in A1, then enter this in F2:
=MATCH(E2,B2:D2,0)
and fill down.
Bob Umlas
Excel MVP
 
If your headers are in A1:F1, then insert this into F2,
press ctrl/shift/enter, and fill down:

=MAX((B2:D2=MAX(B2:D2))*$B$1:$D$1)

HTH
Jason
Atlanta, GA
 
Assuming your data including headings is in A1:D3

in E2: =Max(B2:D2)

in F2: =MATCH(E2,B2:D2)

Fill down E2:F2

Alan Beban
 
Back
Top