Rugby Union Bonus Point Calculation

[fullstackshack]

Hi there. I'm very much a novice when it comes to Excel. I have created a score tracking spreadsheet which calculates all scores within a rugby season for a league table. For those of you that don't know much about the sport of rugby, a team can be awarded bonus points within a match depending upon certain criteria.

I'm having difficulty calculating the bonus point system in my spreadsheet. I need to create a formula that calculates if either team has scored 4 or more tries in a fixture AND if the losing team should be awarded a bonus point or not for being within 7 points of the winners total match score.

The scoring system is as follows:

5 points for a try
2 points for a conversion
3 points for a penalty
3 points for a drop goal

In league tables, teams earn league points for a Win, Draw, Bonus Point Try and Bonus Point Losing Score.

Win = 4 Points
Draw = 2 Points
Loss = 0 Points
Score 4 tries (or more) = 1 Bonus Point
Lose by 7 points (or less) = 1 Bonus Point

So an example match score could be:

Team A Match Score: 27 (T:20, C:4, P:3, DP:0)
Tries - 4
Conversions - 2
Penalties - 1
Drop Goals - 0

Team A League Points
Team A Win - 4
Bonus Point Score - 1

Total League Points Awarded to Team A: 4 + 1 = 5

VS

Team B Match Score: 21 (T:15, C:6, P:0, DP:0)
Tries - 3
Conversions - 3
Penalties - 0
Drop Goals - 0

Team B League Points
Losing Bonus Point Score - 1

Total League Points Awarded to Team B: 1

The MAX Bonus Points a winning team could be awarded is 1. The MAX bonus Points a losing team could be awarded is 2. (1 for scoring 4 or more tries and 1 for being within 7 points of the winning team).

I hope that makes sense to anyone out there willing to give me some help with this. At the moment, I'm adding in the Bonus Points manually.

Thanks in advance.