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.


--
shaunl
------------------------------------------------------------------------
shaunl's Profile: http://www.excelforum.com/member.php...o&userid=25140
View this thread: http://www.excelforum.com/showthread...hreadid=527886

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


--
shaunl
------------------------------------------------------------------------
shaunl's Profile: http://www.excelforum.com/member.php...o&userid=25140
View this thread: http://www.excelforum.com/showthread...hreadid=527886

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


--
shaunl
------------------------------------------------------------------------
shaunl's Profile: http://www.excelforum.com/member.php...o&userid=25140
View this thread: http://www.excelforum.com/showthread...hreadid=527886

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


--
shaunl
------------------------------------------------------------------------
shaunl's Profile: http://www.excelforum.com/member.php...o&userid=25140
View this thread: http://www.excelforum.com/showthread...hreadid=527886

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

Pete

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


--
shaunl
------------------------------------------------------------------------
shaunl's Profile: http://www.excelforum.com/member.php...o&userid=25140
View this thread: http://www.excelforum.com/showthread...hreadid=527886

Hello again, Shaunl.

Try these out:

A3: IF(A1A2,1,0)
A4: IF(A2A1,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

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


--
shaunl
------------------------------------------------------------------------
shaunl's Profile: http://www.excelforum.com/member.php...o&userid=25140
View this thread: http://www.excelforum.com/showthread...hreadid=527886

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

Hi Pete,
I thought i would show you the result of your handiwork. Visit
http://www.waratahs.com.au/DrawsandC...r14Ladder.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


--
shaunl
------------------------------------------------------------------------
shaunl's Profile: http://www.excelforum.com/member.php...o&userid=25140
View this thread: http://www.excelforum.com/showthread...hreadid=527886

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)