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>
<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>