Formula help....

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to find the following with one formula:


NAME Score
A1: Larry B1: 28
A2: Joe B2: 45
A3: Hugo B3: 25
A4: Jim B4: 42
A5: Joe B5: 33
A6: Jim B6: 22
A7: Larry B7: 31
A8: Larry B8: 42
A9: Hugo B9: 41


C1= Larry (inputted)


I need a formula (D1) that looks in column A for all of
Larry's possible point totals then can caluclate the
maximum number of points Larry got (seen in column B) - 42


TIA.

Mike
 
Hi Mike!

Try:

=SUMPRODUCT(MAX(($A$1:$A$9=C1)*($B$1:$B$9)))

Seasons Greetings

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Try:

=SUMPRODUCT(MAX(($A$1:$A$9=C1)*($B$1:$B$9)))
Click to expand...
...

Why the SUMPRODUCT call? It actually gives incorrect results when there are
multiple instances of the highest value. Better to use the array formula

=MAX(IF(A1:A9=C1,B1:B9))
 
The array formula (use ctrl-shift-enter when you enter the formula)
should work.

=max((c1=a1:a9)*b1:b9)
 
Thanks Harlan!

I tried:

=SUMPRODUCT(MAX(($A$1:$A$9=C1)*($B$1:$B$9)))

With multiple instances of 42 (the max) in B1:B9 it works OK. So when
do I get a problem?



--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
Harlan Grove said:
...
..

Why the SUMPRODUCT call? It actually gives incorrect results when there are
multiple instances of the highest value. Better to use the array formula

=MAX(IF(A1:A9=C1,B1:B9))

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup
Click to expand...
archives.
 
Norman Harker said:
I tried:

=SUMPRODUCT(MAX(($A$1:$A$9=C1)*($B$1:$B$9)))

With multiple instances of 42 (the max) in B1:B9 it works OK. So when
do I get a problem?
Click to expand...
....

I misread the parentheses. Your formula works, but the SUMPRODUCT call is
still unnecessary. Plain entry of

=MAX(($A$1:$A$9=C1)*($B$1:$B$9))

would be sufficient.
 
Hi Harlan!

Relieved! Thought I was missing something.

Agreed that SUMPRODUCT call is otiose. It's only advantage is that it
does not have to be array entered.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Back
Top