max number with conditionals

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

How would I get the max number from a number of row, but want to use
some type of selection / conditionals?

example


Fname lname age married
frank smith 40 yes
john smith 21 no
mary jones 44 no
tom jomes 33 yes
sam smith 35 yes

Get me the oldest smith (need the age for formaula)that is married, i.e.
Frank Smith 40 yes
 
Not trivial (at least, not for me!).

At the bottom of column A, array-enter:

=INDEX(A2:A6,MATCH(MAX(($B2:$B6="smith")*($D2:$D6="yes")*($C2:$C6)),(($B2:$B
6="smith")*($D2:$D6="yes")*($C2:$C6)),0))

using <Ctrl> <Shift> <Enter>. Adjust the cell references appropriately, of
course. Then copy across through column D. You should see the correct row
reproduced.
 
Frank,

A bit simpler than Vasant's I hope, non-array

=SUMPRODUCT(MAX((B2:B100="smith")*(D2:D100="yes"),(C2:C100)))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob:

I wasn't sure if he just wanted the number or the entire line ... his
requirements were a bit ambiguous <g>.

Reading it again, your interpretation seems to be more likely to be the
right one.

Regards,

Vasant.
 
=LARGE(--(B2:B100="smith")*--(D2:D100="yes")*C2:C100,1)
Array enter with CSE
This should return the age you are looking for....you can take it a
step further with the Index function to get maybe the first name and
change the "smith" and "yes" to a reference cell like G2 and I2 so
that it can become more dynamic

=INDEX(A2:A100,MATCH(LARGE(--(B2:B100=G2)*--(D2:D100=I2)*C2:C100,1),C2:C100,0))
Ken
 
I don't think the simple INDEX/MATCH approach would work as there may be an
identical age appearing earlier in the table that does not fulfill the
criteria. That's why my solution was so complicated!
 
I just want the number, i.e. 40. I tried Bob's example and for some
reason I get 44 (i.e. must be picking mary). Tripled check it looks the
same formula.
 
Post the data that is being evaluated in a tabular form with the columns and
row numbers.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
I think Bob's formula might have had a typo. Try:

=SUMPRODUCT(MAX((B2:B6="smith")*(D2:D6="yes")*(C2:C6)))
 
Back
Top