Allocate points based on win, loss or draw

I am in charge of a sports league with about 8 teams in 4 grades, so they each need to be scored separately.

WIN = 4 points
LOSS = 0 points
DRAW = 2 points
Teams can loose 2, 4, or 8 points if they are naughty (like don't referee, or play in the wrong team)

If you look at the attachment, Draw.xls, sheet 9March.

This is a draw complete with scores and any applicable penalties.

So, A2 - JG WEGC played JG Chilton. JG WEGC won 5-4. Accordingly, JG WEGC is awarded 4 points, JG Chilton gets 0.


[I]A3 - JG SHC A has drawn with JG QMC, 5-5. The point allocation should be 2 each, but I can't find a way to make the formula do that. Any ideas? The current formula is =(IF((B3-B15)> 0, "4", "0")), but really I want =IF(B3-B15)> 0, "4 - if true", "2 - if equal to 0", "0 - if false".

Is there a way to do this using the =IF formula, or another formula?

Hi Lmsmith,

Put this formula in G2 and fill down to G13 (sheet '9March'):

=LOOKUP(B2-E2,{-20,0,1},{0,2,4})

Put this formula in H2 and fill down to H13:

=4-G2

The first formula assume that one team would never beat another by more than 20 points. If that is remotely possible, just change the -20 to something even smaller, e.g. -100.

The second formula assumes that the total points for both the winning and losing teams is 4. So if team Black has 4, then team White has 0, or 2 and 2, or 0 and 4.

=LOOKUP(B2-E2,{-20,0,1},{0,2,4})

The LOOKUP function searches for a value in one array of values, and returns a result from the same position in the result set array.

=LOOKUP( what_to_lookup , array_to_search , result_array )

So we're looking up the difference of B2-E2. Each of the numbers in the "array_to_search" serve as the lower boundary for the lookup range. Assuming no team will beat another by more than 20 (B2-E2 would be less than -20), the lower range of -20 should handle all matches. So if B2-E2 is between -20 and -1, it will look in the "result_array" and return the value from the same position in the array, which is 0 denoting they lost. (Note: Both arrays need to have the same number of items, and the "array_to_search" needs to be in ascending order).

If B2-E2 is 0 (a draw), then it returns the second value from the result_array, or 2. If B2-E2 is 1 or larger (a win), the result of 4 is returned.

Hopefully that makes sense!