Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 IF Statements not mutually exclusive | Excel Discussion (Misc queries) | |||
Excel MS Query multiple select statements | Excel Discussion (Misc queries) | |||
UDFunctions and nested If-the-else statements | Excel Worksheet Functions | |||
Linking two IF statements together | Excel Discussion (Misc queries) | |||
Nested IF statements | Excel Worksheet Functions |