Maximum Function

  • Thread starter Thread starter junkboy
  • Start date Start date
J

junkboy

Is there a way to return the argument number when using a Max function?
For instance if I have:

=MAX(80, 14, 82, 10)

82 is what it will return, but is there a way that I can get it to
return 3 which is the argument number (number in the list) ?


Or maybe I should rephrase. I have four calculations that I have to
determine which total is the maximum and then assign a 1, 2, 3, or 4
depending on which calc was the largest. I figured I'd start with the
max function and see what I could do, but maybe there is another way. I
can do this in multiple cells, but I was hoping to do it in one.

Any Ideas?
 
One way:

=INDEX({1,2,3,4},MATCH(MAX(80,14,82,10),{80,14,82,10},0))

HTH
Jason
Atlanta, GA
 
Is there a way to return the argument number when using a Max function?
For instance if I have:

=MAX(80, 14, 82, 10)

82 is what it will return, but is there a way that I can get it to
return 3 which is the argument number (number in the list) ?
Click to expand...
...

Are you entering these numbers (or formulas evaluating to them) as separate
arguments to MAX, or could you combine them in a range? If the latter, then

=MATCH(MAX(Rng),Rng,0)

will give you want you want.
 
Thanks for the help, everyone, but it still doesn't work. The problem i
I do not have constant values that I can put in the array, each valu
is a different equation. Here is my max equation (I've separated th
lines so the individual formulas are easier to read):

=MAX(
((AG2*-0.1423)+(AH2*0.4053)+(AI2*1.7822)+(AJ2*1.3221)+(AK2*4.4537)+(AL2*0.9387)+(-22.1462)),

((AG2*0.3497)+(AH2*0.6079)+(AI2*0.8154)+(AJ2*1.1333)+(AK2*4.0757)+(AL2*1.0537)+(-16.8775)),

((AG2*1.191)+(AH2*0.352)+(AI2*1.9455)+(AJ2*1.3082)+(AK2*3.8938)+(AL2*1.2975)+(-24.7977)),

((AG2*0.6719)+(AH2*1.1796)+(AI2*1.1236)+(AJ2*0.7113)+(AK2*3.2245)+(AL2*1.8583)+(-15.627)))


I tried to do the Match plugging each formula into the array, but tha
doesn't work. (It tells me the formula has an error)

Any other ideas
 
...
...
=MAX(
((AG2*-0.1423)+(AH2*0.4053)+(AI2*1.7822)+(AJ2*1.3221)+(AK2*4.4537)+(AL2*0.9387)+(-22.1462)),
((AG2*0.3497)+(AH2*0.6079)+(AI2*0.8154)+(AJ2*1.1333)+(AK2*4.0757)+(AL2*1.0537)+(-16.8775)),
((AG2*1.191)+(AH2*0.352)+(AI2*1.9455)+(AJ2*1.3082)+(AK2*3.8938)+(AL2*1.2975)+(-24.7977)),
((AG2*0.6719)+(AH2*1.1796)+(AI2*1.1236)+(AJ2*0.7113)+(AK2*3.2245)+(AL2*1.8583)+(-15.627)))

I tried to do the Match plugging each formula into the array, but that
doesn't work. (It tells me the formula has an error)
Click to expand...

You could try

=MATCH(MAX(MMULT(IF(COLUMN(AG2:AM2)<COLUMN(AM2),AG2:AM2,1),
{-0.1423,0.3497,1.191,0.6719;
0.4053,0.6079,0.352,1.1796;
1.7822,0.8154,1.9455,1.1236;
1.3221,1.1333,1.3082,0.7113;
4.4537,4.0757,3.8938,3.2245;
0.9387,1.0537,1.2975,1.8583;
-22.1462,-16.8775,-24.7977,-15.627})),
MMULT(IF(COLUMN(AG2:AM2)<COLUMN(AM2),AG2:AM2,1),
{-0.1423,0.3497,1.191,0.6719;
0.4053,0.6079,0.352,1.1796;
1.7822,0.8154,1.9455,1.1236;
1.3221,1.1333,1.3082,0.7113;
4.4537,4.0757,3.8938,3.2245;
0.9387,1.0537,1.2975,1.8583;
-22.1462,-16.8775,-24.7977,-15.627}),0)

Or better yet, give the array

{-0.1423,0.3497,1.191,0.6719;
0.4053,0.6079,0.352,1.1796;
1.7822,0.8154,1.9455,1.1236;
1.3221,1.1333,1.3082,0.7113;
4.4537,4.0757,3.8938,3.2245;
0.9387,1.0537,1.2975,1.8583;
-22.1462,-16.8775,-24.7977,-15.627}

a defined name, like Array, then use

=MATCH(MAX(MMULT(IF(COLUMN(AG2:AM2)<COLUMN(AM2),AG2:AM2,1),Array)),
MMULT(IF(COLUMN(AG2:AM2)<COLUMN(AM2),AG2:AM2,1),Array),0)

Better still would be to enter the array into a 4-row by 7 column range. If
there's nothing yet in AM2, enter 1 in it. Then the formula would reduce to

=MATCH(MAX(MMULT(AG2:AM2,Array)),MMULT(AG2:AM2,Array),0)

The point here is that the terms in your MAX formula come from different in a
straightforward mantrix product, so they can be made into an array if you use
MMULT to produce the values.
 
Put each of those individual long formulas into a cell by itself, say
Z1:Z4. Then, use the MATCH(MAX(Rng),Rng,0) suggestion from various
people. Note that in my example Rng will be where Rng is Z1:Z4.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top