Hi all.
I have a fairly complicated question and I've been racking my brain for a while. I have the following data set:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 146px"><col width="79"><col width="83"><col width="73"><col width="58"><col width="58"><col width="84"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]Drew[/TD]
[TD="align: center"]Ruben[/TD]
[TD="align: center"]Hussain[/TD]
[TD="align: center"]Push Ups[/TD]
[TD="align: center"]Sit Ups[/TD]
[TD="align: center"]Week Ending Date[/TD]
[/TR]
[TR]
[TD]50% Normal for Week?[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]4/1/18[/TD]
[/TR]
[TR]
[TD]10% Triangle for Week?[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]575[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]4/1/18[/TD]
[/TR]
[TR]
[TD]20% Wide for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]225[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4/1/18[/TD]
[/TR]
[TR]
[TD]50% Normal for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/8/18[/TD]
[/TR]
[TR]
[TD]10% Triangle for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/8/18[/TD]
[/TR]
[TR]
[TD]20% Wide for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/8/18[/TD]
[/TR]
[TR]
[TD]50% Normal for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/15/18[/TD]
[/TR]
[TR]
[TD]10% Triangle for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/15/18[/TD]
[/TR]
[TR]
[TD]20% Wide for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/15/18[/TD]
[/TR]
[TR]
[TD]50% Normal for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/22/18[/TD]
[/TR]
[TR]
[TD]10% Triangle for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/22/18[/TD]
[/TR]
[TR]
[TD]20% Wide for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/22/18[/TD]
[/TR]
[TR]
[TD]50% Normal for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/29/18[/TD]
[/TR]
[TR]
[TD]10% Triangle for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/29/18[/TD]
[/TR]
[TR]
[TD]20% Wide for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/29/18[/TD]
[/TR]
</tbody>[/TABLE]
Drew has 200 push ups, Ruben has 575, and Hussain has 225. Essentially, I want to return the name of the person who has the maximum number of push ups AND who meets all 3 criteria (3 Yes') for each date. So I need to come up with a formula that will return "Hussain" as my answer.
I've tried various MAX/IF functions, INDEX/MATCH variables, SUMPRODUCT formulas, and I think I just have too many criteria. Or maybe I need to organize my data better (probably this).
Any help would be appreciated. Thank you.
I have a fairly complicated question and I've been racking my brain for a while. I have the following data set:
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>[TABLE="width: 0"]
<colgroup><col style="width: 146px"><col width="79"><col width="83"><col width="73"><col width="58"><col width="58"><col width="84"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: center"]Drew[/TD]
[TD="align: center"]Ruben[/TD]
[TD="align: center"]Hussain[/TD]
[TD="align: center"]Push Ups[/TD]
[TD="align: center"]Sit Ups[/TD]
[TD="align: center"]Week Ending Date[/TD]
[/TR]
[TR]
[TD]50% Normal for Week?[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]200[/TD]
[TD="align: center"]250[/TD]
[TD="align: center"]4/1/18[/TD]
[/TR]
[TR]
[TD]10% Triangle for Week?[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]575[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"]4/1/18[/TD]
[/TR]
[TR]
[TD]20% Wide for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]Yes[/TD]
[TD="align: center"]225[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]4/1/18[/TD]
[/TR]
[TR]
[TD]50% Normal for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/8/18[/TD]
[/TR]
[TR]
[TD]10% Triangle for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/8/18[/TD]
[/TR]
[TR]
[TD]20% Wide for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/8/18[/TD]
[/TR]
[TR]
[TD]50% Normal for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/15/18[/TD]
[/TR]
[TR]
[TD]10% Triangle for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/15/18[/TD]
[/TR]
[TR]
[TD]20% Wide for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/15/18[/TD]
[/TR]
[TR]
[TD]50% Normal for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/22/18[/TD]
[/TR]
[TR]
[TD]10% Triangle for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/22/18[/TD]
[/TR]
[TR]
[TD]20% Wide for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/22/18[/TD]
[/TR]
[TR]
[TD]50% Normal for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/29/18[/TD]
[/TR]
[TR]
[TD]10% Triangle for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/29/18[/TD]
[/TR]
[TR]
[TD]20% Wide for Week?[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD="align: center"]No[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]4/29/18[/TD]
[/TR]
</tbody>[/TABLE]
Drew has 200 push ups, Ruben has 575, and Hussain has 225. Essentially, I want to return the name of the person who has the maximum number of push ups AND who meets all 3 criteria (3 Yes') for each date. So I need to come up with a formula that will return "Hussain" as my answer.
I've tried various MAX/IF functions, INDEX/MATCH variables, SUMPRODUCT formulas, and I think I just have too many criteria. Or maybe I need to organize my data better (probably this).
Any help would be appreciated. Thank you.