MAX, IF statements?
 

I have 3 columns of data:

Column E Column G Column J
Bell, Charlie 48 .176
Davis, Johnny 34 .500
Smith, Tom 22 .335
Williams, Bill 56 .450
McElroy, Mike 62 .210

I want a formula that will identify the largest value in column J among all
values in column G that are greater or equal to 48, and display the
corresponding name from column E in the cell containing the formula. In this
example, the answer is "Williams, Bill" because he has the highest value
(.450) in column J among all rows that are 48 or more in column G.

Can anyone help?

Thanks,
Bob

MAX, IF statements?
 

Try this *array* formula:

=INDEX(E1:E5,MATCH(MAX((G1:G5=48)*(J1:J5)),J1:J5, 0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"bob" wrote in message
...
I have 3 columns of data:

Column E Column G Column J
Bell, Charlie 48 .176
Davis, Johnny 34 .500
Smith, Tom 22 .335
Williams, Bill 56 .450
McElroy, Mike 62 .210

I want a formula that will identify the largest value in column J among
all
values in column G that are greater or equal to 48, and display the
corresponding name from column E in the cell containing the formula. In
this
example, the answer is "Williams, Bill" because he has the highest value
(.450) in column J among all rows that are 48 or more in column G.

Can anyone help?

Thanks,
Bob

MAX, IF statements?
 

That works except when two values in column J are equally the largest. In
that case, the formula ignores the 48 qualifier and displays the name that
corresponds to the first of the largest values.

Another issue is that in some cases I need to display the name based on the
lowest value in column J, but when I substitute MIN for MAX, the formula
again ignores the 48 qualifier and goes to the first lowest value in the
column.

"RagDyer" wrote:

Try this *array* formula:

=INDEX(E1:E5,MATCH(MAX((G1:G5=48)*(J1:J5)),J1:J5, 0))

--
Array formulas must be entered with CSE, <Ctrl <Shift <Enter, instead of
the regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually. Also, you must use CSE when
revising the formula.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"bob" wrote in message
...
I have 3 columns of data:

Column E Column G Column J
Bell, Charlie 48 .176
Davis, Johnny 34 .500
Smith, Tom 22 .335
Williams, Bill 56 .450
McElroy, Mike 62 .210

I want a formula that will identify the largest value in column J among
all
values in column G that are greater or equal to 48, and display the
corresponding name from column E in the cell containing the formula. In
this
example, the answer is "Williams, Bill" because he has the highest value
(.450) in column J among all rows that are 48 or more in column G.

Can anyone help?

Thanks,
Bob

MAX, IF statements?
 

Try this:

=INDEX(E1:E5,MATCH(MAX(IF(G1:G5=48,J1:J5)),J1:J5, 0))

ctrlshiftenter (not just enter)



"bob" wrote:

I have 3 columns of data:

Column E Column G Column J
Bell, Charlie 48 .176
Davis, Johnny 34 .500
Smith, Tom 22 .335
Williams, Bill 56 .450
McElroy, Mike 62 .210

I want a formula that will identify the largest value in column J among all
values in column G that are greater or equal to 48, and display the
corresponding name from column E in the cell containing the formula. In this
example, the answer is "Williams, Bill" because he has the highest value
(.450) in column J among all rows that are 48 or more in column G.

Can anyone help?

Thanks,
Bob

MAX, IF statements?
 

One way to get it up in descending & ascending order
which caters for the possibility of ties/multiple ties in the col J values

---- "MAX" -------
Auto-listing names from col E in descending order by col J values
(only for names with col G values =48)

In K1:
=IF(G1="","",IF(G1=48,J1-ROW()/10^10,""))

In L1:
=IF(ROW()COUNT(K:K),"",INDEX(E:E,MATCH(LARGE(K:K, ROW()),K:K,0)))

Select K1:L1, fill down to cover the max expected extent of source data.
Hide away col K. Col L returns the full list of names, neatly bunched at the
top. Names with tied col J values will be listed in the same relative order
that they appear within the source data.

------- "MIN" -------
Auto-listing names from col E in ascending order by col J values
(only for names with col G values =48)

In N1:
=IF(G1="","",IF(G1=48,J1+ROW()/10^10,""))

In O1:
=IF(ROW()COUNT(N:N),"",INDEX(E:E,MATCH(SMALL(N:N, ROW()),N:N,0)))

Select N1:O1, fill down to cover the max expected extent of source data.
Hide away col N. Col O returns the full list of names, neatly bunched at the
top. Names with tied col J values will be listed in the same relative order
that they appear within the source data.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"bob" wrote:
That works except when two values in column J are equally the largest. In
that case, the formula ignores the 48 qualifier and displays the name that
corresponds to the first of the largest values.

Another issue is that in some cases I need to display the name based on the
lowest value in column J, but when I substitute MIN for MAX, the formula
again ignores the 48 qualifier and goes to the first lowest value in the
column

MAX, IF statements?
 

What about the column G value?

Shouldn't someone with a higher column G value be listed first if they also
have the highest column J value?

Column E Column G Column J
Bell, Charlie 48 .450
Davis, Johnny 34 .500
Smith, Tom 22 .335
Williams, Bill 56 .450
McElroy, Mike 62 .210

Biff

"Max" wrote in message
...
One way to get it up in descending & ascending order
which caters for the possibility of ties/multiple ties in the col J values

---- "MAX" -------
Auto-listing names from col E in descending order by col J values
(only for names with col G values =48)

In K1:
=IF(G1="","",IF(G1=48,J1-ROW()/10^10,""))

In L1:
=IF(ROW()COUNT(K:K),"",INDEX(E:E,MATCH(LARGE(K:K, ROW()),K:K,0)))

Select K1:L1, fill down to cover the max expected extent of source data.
Hide away col K. Col L returns the full list of names, neatly bunched at
the
top. Names with tied col J values will be listed in the same relative
order
that they appear within the source data.

------- "MIN" -------
Auto-listing names from col E in ascending order by col J values
(only for names with col G values =48)

In N1:
=IF(G1="","",IF(G1=48,J1+ROW()/10^10,""))

In O1:
=IF(ROW()COUNT(N:N),"",INDEX(E:E,MATCH(SMALL(N:N, ROW()),N:N,0)))

Select N1:O1, fill down to cover the max expected extent of source data.
Hide away col N. Col O returns the full list of names, neatly bunched at
the
top. Names with tied col J values will be listed in the same relative
order
that they appear within the source data.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"bob" wrote:
That works except when two values in column J are equally the largest. In
that case, the formula ignores the 48 qualifier and displays the name
that
corresponds to the first of the largest values.

Another issue is that in some cases I need to display the name based on
the
lowest value in column J, but when I substitute MIN for MAX, the formula
again ignores the 48 qualifier and goes to the first lowest value in
the
column

MAX, IF statements?
 

Col G's values were used simply as pre-qualifiers, from my interp on the
OP's:
.. a formula that will identify the largest value in column J among all
values in column G that are greater or equal to 48, and display the
corresponding name from column E
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Biff" wrote in message
...
What about the column G value?

Shouldn't someone with a higher column G value be listed first if they
also have the highest column J value?

Column E Column G Column J
Bell, Charlie 48 .450
Davis, Johnny 34 .500
Smith, Tom 22 .335
Williams, Bill 56 .450
McElroy, Mike 62 .210

Biff

MAX, IF statements?
 

=INDEX( E1:E5, MATCH( MAX( IF( G1:G5 = 48, J1:J5 ) ), IF( G1:G5 = 48, J1:J5
), 0 ) )
--
Regards,
Luc.

"Festina Lente"


"bob" wrote:

I have 3 columns of data:

Column E Column G Column J
Bell, Charlie 48 .176
Davis, Johnny 34 .500
Smith, Tom 22 .335
Williams, Bill 56 .450
McElroy, Mike 62 .210

I want a formula that will identify the largest value in column J among all
values in column G that are greater or equal to 48, and display the
corresponding name from column E in the cell containing the formula. In this
example, the answer is "Williams, Bill" because he has the highest value
(.450) in column J among all rows that are 48 or more in column G.

Can anyone help?

Thanks,
Bob

MAX, IF statements?
 

Sorry but this ignores the " 48" qualifier and just lists the first of the
two highest values that are equal. Thank you anyway.

Bob

"Teethless mama" wrote:

Try this:

=INDEX(E1:E5,MATCH(MAX(IF(G1:G5=48,J1:J5)),J1:J5, 0))

ctrlshiftenter (not just enter)



"bob" wrote:

I have 3 columns of data:

Column E Column G Column J
Bell, Charlie 48 .176
Davis, Johnny 34 .500
Smith, Tom 22 .335
Williams, Bill 56 .450
McElroy, Mike 62 .210

I want a formula that will identify the largest value in column J among all
values in column G that are greater or equal to 48, and display the
corresponding name from column E in the cell containing the formula. In this
example, the answer is "Williams, Bill" because he has the highest value
(.450) in column J among all rows that are 48 or more in column G.

Can anyone help?

Thanks,
Bob

MAX, IF statements?
 

Bob,

I've suggested a non-array way to handle ties / multiple ties in response to
your feedback to RD in the other branch. Let me know how it went for you.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---