Selecting on Max Value within a Set

[sinjin]

I wanted Column E of my dataset to read "Max" for the row where the ID contains the max probability for that state. If two IDs for the same State have the same probability, as in Maryland, I wanted "Max" to show for the ID with the minimum Date. If two IDs match on both Probability and Date, then I wanted "Max" to appear for only one ID within each group of States. Below is my dummy dataset of desired results.

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">Column A Column B Column C Column D Column E
State Probability Date ID Formula Field
California 10% 12/31/2016 123
California 20% 1/7/2017 129
California 23% 1/14/2017 135 Max
Colorado 26% 1/21/2017 141
Colorado 38% 12/31/2016 147 Max
Illinois 44% 1/14/2017 153 Max
Illinois 44% 1/14/2017 159
Illinois 20% 1/21/2017 165
Illinois 35% 1/28/2017 171
Maryland 41% 2/4/2017 177
Maryland 41% 12/31/2016 183 Max
Maryland 35% 1/7/2017 189
Michigan 20% 1/14/2017 195
Michigan 35% 1/21/2017 201
Michigan 38% 1/28/2017 207 Max
West Virginia 41% 2/4/2017 213
West Virginia 44% 2/11/2017 219 Max
</code><code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">
I'm using the formula below; however for some records the highest Probability is not correctly being selected. I am correctly using the formula as an array. Finally, H1 = TODAY() here.

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">={IF(ROW(D2)=MIN(IF((($C$2:$C$18-$H$1)=MIN(IF(($A$2:$A$18=A2)*($B$2:$B$18=MAX(IF(($A$2:$A$18=A2),$B$2:$B$18))),($C$2:$C$18-$H$1))))*($A$2:$A$18=A2),ROW($D$2:$D$18))),"Max","")}</code>
</code>

 

[Snakehips]

singing,

Try...

Excel Workbook
	A	B	C	D	E
1	State	Probability	Date	ID	Result
2	California	10%
3	California	20%
4	California	23%			Max
5	Colorado	26%
6	Colorado	38%			Max
7	Illinois	44%			Max
8	Illinois	44%			Max
9	Illinois	20%
10	Illinois	35%
11	Maryland	41%			Max
12	Maryland	41%			Max
13	Maryland	35%
14	Michigan	20%
15	Michigan	35%
16	Michigan	38%			Max
Sheet13

Hope that helps.

 

[Aladin Akyurek]

I think Tony did not notice the date bit...

In E2 control+shift+enter, not just enter, and copy down:

=IF(AND(B2=MAX(IF($A$2:$A$18=A2,$B$2:$B$18)),C2=MIN(IF($A$2:$A$18=A2,
    IF($B$2:$B$18=MAX(IF($A$2:$A$18=A2,$B$2:$B$18)),$C$2:$C$18)))),"max","")

<strike></strike>

 

[Snakehips]

I think Tony did not notice the date bit...

Guilty as charged!

Note to self :- Read the OP's question !

 

[sinjin]

I think Tony did not notice the date bit...

In E2 control+shift+enter, not just enter, and copy down:

=IF(AND(B2=MAX(IF($A$2:$A$18=A2,$B$2:$B$18)),C2=MIN(IF($A$2:$A$18=A2,
    IF($B$2:$B$18=MAX(IF($A$2:$A$18=A2,$B$2:$B$18)),$C$2:$C$18)))),"max","")

<strike></strike>

Everything appears to be working correctly except for instances where within a given state probabilities and dates are the same for two different records. Currently, your formula is showing "Max" for both said instances. In these instances I would like to allocate "Max" to only one of these records (randomly).

 

[Aladin Akyurek]

Everything appears to be working correctly except for instances where within a given state probabilities and dates are the same for two different records. Currently, your formula is showing "Max" for both said instances. In these instances I would like to allocate "Max" to only one of these records (randomly).

Care to post that additional record and the associated desired result?

 

[sinjin]

Care to post that additional record and the associated desired result?

This is included in the original post. See the two Illinois records that both have 44% probability and the same date. Only one of these actually has a "Max" value for the record, as desired.

 

[Aladin Akyurek]

This is included in the original post. See the two Illinois records that both have 44% probability and the same date. Only one of these actually has a "Max" value for the record, as desired.

control+shift+enter...

=IF(AND(B2=MAX(IF($A$2:$A$18=A2,$B$2:$B$18)),C2=MIN(IF($A$2:$A$18=A2,
    IF($B$2:$B$18=MAX(IF($A$2:$A$18=A2,$B$2:$B$18)),$C$2:$C$18)))
    COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2)=1),"max","")