[Solved] Create Bowls comp score sheet

[OXONBOD]

I am trying to create a score sheet for a charity Bowls competition.
I need to award points as follows: If team A scores more shots than team B, team A will be awarded 2 points and team B will be awarded none. The reverse is also true. Then, if both teams score the same number of shots, they will both be awarded 1 point each.

I have got as far as the following bit of code =IF(A2>B2;2;0) that awards points if A scores more than B or none if the score I less. What I cant figure out is how to include a test for equal scores that then awards one point each.

[MrProgrammer]

Hi, and welcome to the forum.

If team A scores more shots than team B, team A will be awarded 2 points and team B will be awarded none. The reverse is also true. Then, if both teams score the same number of shots, they will both be awarded 1 point each.

Assuming A's score in A2 and B's score in B2:
A's points: =SIGN(A2-B2)+1
B's points: =SIGN(B2-A2)+1
«or»
A's points: =(A2>B2)+(A2>=B2)
B's points: =(B2>A2)+(B2>=A2)

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[OXONBOD]

Many thanks. The second set of code works fine. Could you explain how it works?

[robleyd]

From Section 1 of the tutorial MrProgrammer linked above:

The special values TRUE and FALSE are really numbers. TRUE has the value 1 and FALSE has the value 0

A2>B2 is either TRUE or FALSE; similarly A2>=B2 is either TRUE or FALSE.