Sports Comp Ladder tabulating help needed

[shaunl]

Hi,
I have posted here once before with great success and hope that I ca
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 t
compiling the table I would like to automate but have been unable t
find out how via the excel documentation.

The aspects are.

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

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

SITUATION ON
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 th
winning team scored 24 points. Therefore the team lost but lost b
seven points and is therefore entitled to one (1) bonus point.
SOLUTION NEEDED - I need a formula that will calculate whether cell
is seven or less than cell 1, and if so automatically place a total o
"1" in cell 3.

SITUATION
Cell 1 = Tries Scored (eg. 5)
In this instance the team has scored five tries. They have scored mor
than four tries or more and is therefore entitled to one (1) bonu
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 automaticall
placed in Cell 2.

I have looked into the excel help but to no avail and the search term
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

 

[Pete_UK]

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

 

[shaunl]

Hi Peter,
Many thanks - your calculations have helped enormously however I thin
I have failed to properly explain the requirements as the calculation
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 fo
losing as neither team has lost. However, if the winning team ha
scored 24 points then the losing team will need to have scored anywher
between 23 to 17 points to get a bonus point. So in this instance an
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. The
need to score a minimum of four tries to get a bonus point - but an
value above 4 will get them a bonus point. So any value of 4 and abov
will earn them a "1" bonus point.

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

thanks
Shaun in Sydne

 

[Pete_UK]

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

 

[shaunl]

WOW!
It worked - thanks so much.

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

Thanks
Shaun in Sydne

 

[shaunl]

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

 

[Pete_UK]

Thanks for feeding back, Shaun. Glad you got it working.

Pete

 

[shaunl]

Hi Pete,
I used the spreadsheet over the weekend with great success. Again my
thanks.

Whilst using I realised that I can further automate the calculations,
so I attempted to do so using your formulas but with no success and was
wondering if you could take a look at the following for me.

When a match is played the following is recorded.
A1
points for
A2
points against
A3
Won
A4
drawn
A5
lost

If A1 is greater than A2, A3 should be assigned a "1"
If A2 is greater than A1, A5 should be assigned a "1"
If A1 is equal to A2, A4 should be assigned a "1"

It seemed to me that I should be able to figure out the above formulas
on the basis of the calculations you supplied but I was getting stuck
on the "<=" area.

Any help is appreciated.

Thanks

 

[Pete_UK]

Hello again, Shaunl.

Try these out:

A3: IF(A1>A2,1,0)
A4: IF(A2>A1,1,0)
A5: IF(A1=A2,1,0)

I have assumed that you would want 0 in the cells as an opposite of 1,
but you can change this to "" if you want them to be blank.

It seems strange having the data going down a column, but then, if you
are down-under anyway ... <bg>

Hope this helps.

Pete

 

[shaunl]

Hi Pete,
Thanks again. The order of the cells were more to do with me explaining
the scenario rather than me being on the upside down end of the world.


All worked brilliantly except for one hiccup with the A5, when two
numbers equal each other.

What is happening is that in that, in the instance that a game is yet
to be played and there is no entry in the cells (ie.0-0), the formula
gives a point to the draw column.

This is a problem in that it obviously attributes a draw when a game
has not yet been played.

Thankfully not may draws occur, so if needed I can manually update
these. However, is there a way of telling excel to only calculate if
there is an entry within the cell. So if a cell is blank it is ignored
by the formulas?

Cheers
Shaun in upside down Sydney

 

[Pete_UK]

Try this in A5:

=IF(AND(ISNUMBER(A1),ISNUMBER(A2),A1=A2),1,0)

With this, both A1 and A2 have to be numbers as well as being equal, so
0 0 will count as a draw but 1 (or 2) blanks won't.

Hope this solves it - bed time now.

Pete

 

[shaunl]

Hi Pete,
I thought i would show you the result of your handiwork. Visit
http://www.waratahs.com.au/DrawsandCompetitions/TooheysNewSuper14/Super14Ladder.aspx
and check out the competition ladder.

I have one further issue to work out and it again relates to an
instance when there is no data in a cell - the formula attributes a
"1". I have tried experimenting with the (AND(ISNUMBER formula to no
success.

The problem is in the Bye column.

Presently the formula is =IF(AN42=0,1,0). I need the formula to change
so that if there is no data in cell AN42 a "0" and not a "1" will
appear.

Again - my thanks. Oh a tip for the web page where the draw can be
found - if you click on any of the cell headers the data will
reorganise itself. Very neat!

Cheers

 

[northernbeachesoztag]

Thanks Pete you're a lifesaver.
From 8 years later
Just for the record if you want to auto calculate a bye from blank entries in the for and against fields use this

=IF(AND(ISNUMBER(A1),ISNUMBER(A2)),0,1)