Returning Value Based on Various Criteria

[kanemitchell99]

Hi,

Wasn't too sure what to name the title so apologies if it is not correct.

I need to analyse the frequency that a particular promo for a betting agency pays out on Australian Rules Footy. The promo is if your team gets 24 points up at any stage during the game but looses the game, the bet will still pay out.

I have the data of what the scores were for each team at each quarter for the last 7 years. I need to return YES if at any stage a team was winning by 24 points at a quarter break and went on to loose, otherwise NO.

In my head the pseudocode for this would be something like IF(team A=team B - 24pts (at Q1)) OR (team A=Team B - 24pts (at Q2)) OR (team A=team B - 24pts (at Q3)) AND (team A<team B (final score)) return TRUE otherwise FALSE)

I think this is a pretty simple formula to work this out but I am relatively inexperienced with excel hence why I have come here to ask some experts!!

I hope this question makes sense!

Thanks!

 

[Peter_SSs]

Welcome to the MrExcel board!

Could you show us how the data is laid out by posting a small set of copyable dummy sample data? My signature block below has a link with suggestions for how to do that.

 

[kanemitchell99]

Hi Peter,

I hope the below table is what you were asking for and hopefully it helps!

You can see that in match 4, Port Adelaide were up by more than 24 points in the 2nd quarter and went on to loose so this would be a case where the formula should return YES. The others should all return NO.


[TABLE="class: grid, width: 500"]
<colgroup><col style="width:48pt" width="64" span="2"> <col style="mso-width-source:userset;mso-width-alt:4278;width:88pt" width="117"> <col style="width:48pt" width="64" span="4"> </colgroup><tbody>[TR]
[TD="width: 64"]Round
[/TD]
[TD="width: 64"]Match[/TD]
[TD="width: 117"]Team[/TD]
[TD="width: 64"]Q1 Score[/TD]
[TD="width: 64"]Q2 Score[/TD]
[TD="width: 64"]Q3 Score[/TD]
[TD="width: 64"]Q4 Score[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1
[/TD]
[TD]Carlton[/TD]
[TD]27[/TD]
[TD]46[/TD]
[TD]76[/TD]
[TD]89[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]Richmond[/TD]
[TD]45[/TD]
[TD]83[/TD]
[TD]101[/TD]
[TD]132[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]Collingwood
[/TD]
[TD]11[/TD]
[TD]51[/TD]
[TD]65[/TD]
[TD]86[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]2[/TD]
[TD]Adelaide
[/TD]
[TD]31[/TD]
[TD]55[/TD]
[TD]90[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]St Kilda[/TD]
[TD]38[/TD]
[TD]50[/TD]
[TD]63[/TD]
[TD]90
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD]Melbourne[/TD]
[TD]15[/TD]
[TD]58[/TD]
[TD]97[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[TD]Sydney[/TD]
[TD]27
[/TD]
[TD]32
[/TD]
[TD]79
[/TD]
[TD]110
[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]4[/TD]
[TD]Port Adelaide[/TD]
[TD]26
[/TD]
[TD]59
[/TD]
[TD]67
[/TD]
[TD]102
[/TD]
[/TR]
</tbody>[/TABLE]


Thanks,
Kane.

 

[Peter_SSs]

I hope the below table is what you were asking for and hopefully it helps!

Yes, exactly what I was after, thanks.

Try this formula, copied down.

Excel Workbook
	A	B	C	D	E	F	G	H
1	Round	Match	Team	Q1 Score	Q2 Score	Q3 Score	Q4 Score
2	1	1	Carlton	27	46	76	89	No
3	1	1	Richmond	45	83	101	132
4	1	2	Collingwood	11	51	65	86	No
5	1	2	Adelaide	31	55	90	100
6	1	3	St Kilda	38	50	63	90	No
7	1	3	Melbourne	15	58	97	120
8	1	4	Sydney	27	32	79	110
9	1	4	Port Adelaide	26	59	67	102	Yes
AFL

 

[kanemitchell99]

Thanks Peter that works perfectly!

Cheers,
Kane

 

[Peter_SSs]

Thanks Peter that works perfectly!

Cheers,
Kane

You're very welcome. Thanks for letting us know.