Sports Comp Ladder tabulating help needed

Hi,
I have posted here once before with great success and hope that I can experience the same this time round - my thanks in advance.

This should be easy but is beyond my newbish capacities in excel.

I am updating a sports competition ladder. There are two aspects to compiling the table I would like to automate but have been unable to find out how via the excel documentation.

The aspects are.

1. When a team loses by a margin of seven points or less below the total points scored by the winning team then the team is awarded a single "1" bonus point that contributes to their overall competition points tally.
2. When a team scores four tries or more they are likewise awarded a bonus point.

I require formulas that will automatically calculate the bonus points into a cell.

Situation One
cell 1 = Points For (eg. 17)
Cell 2 = Points Against (eg. 24).
In this instance the team did not win. They scored 17 points and the winning team scored 24 points. Therefore the team lost but lost by seven points and is therefore entitled to one (1) bonus point.
SOLUTION NEEDED - I need a formula that will calculate whether cell 2 is seven or less than cell 1, and if so automatically place a total of "1" in cell 3.

Situation 2
Cell 1 = Tries Scored (eg. 5)
In this instance the team has scored five tries. They have scored more than four tries or more and is therefore entitled to one (1) bonus point.
SOLUTION NEEDED - I need a formula that will scan the number in cell 1. If this number is 4 or more, then a total of "1" should be automatically placed in Cell 2.

I have looked into the excel help but to no avail and the search terms are so broad it was a nightmare trying to search the forums.

My thanks in advance to anyone that can help me.

Kind Regards
Shaun in Sydney.

In Situation 1, you can use this formula in your third cell:

=IF(Cell2-Cell1>=7,1,0)

In Situation 2 (assume tries scored is in Cell4 to avoid confusion),
put this formula in Cell5:

=IF(Cell4>=4,1,0)

The bonus points for this team are thus:

=Cell3 + Cell5

Hope this helps.

Pete

Hi Peter,
Many thanks - your calculations have helped enormously however I think I have failed to properly explain the requirements as the calculations still need to accommodate a couple of additional matters.

I fear that my previous explanations might not have been sufficient - sorry about that!

The following might offer a better explanation.

Bonus Point for losing by seven points or less.
1. If two teams draw 24-24 then neither will get a bonus point for losing as neither team has lost. However, if the winning team has scored 24 points then the losing team will need to have scored anywhere between 23 to 17 points to get a bonus point. So in this instance any value between 17 and 23 will be sufficient to award a "1" bonus point.

Bonus point for scoring four tries or more.
2. If a team scores more than four tries they get a bonus point. They need to score a minimum of four tries to get a bonus point - but any value above 4 will get them a bonus point. So any value of 4 and above will earn them a "1" bonus point.

These complicate your calculations no doubt but if you could take another look for me I would very very grateful (presenlty errors are creeping into the maintenance of the comp ladders and I need to automate it completely.

thanks
Shaun in Sydney

Shaun,

this will correct the calculation for Situation 1:

=IF(AND((Cell2-Cell1)<=7,(Cell2-Cell1)>0),1,0)

I think the formula for Situation 2 is correct, unless I have
misunderstood. If a team scores 6 tries do they get 1 or 3 bonus
points?

Pete

WOW!
It worked - thanks so much.

To answer the tries question. The most you can you be awarded for scoring four tries or more is one point. So if you score 6 tries you can only score one point.

Thanks
Shaun in Sydney

Hi Peter - my apologies. Your second formula was spot on.

I would like to thank you for taking the time to help me. Your input is very appreciated.

The best aspect of these formulas is that it removes the element of human error!

Thanks you.

Shaun in Sydney