Greetings again to members of the board.
Several of you helped me out last year when I was trying to create a spreadsheet that tracked Rugby Union scores. It was so good that I have been asked to amend it to cover Netball & Hockey (for inter school games). But they both have oddities.
In Hockey teams get 2 points for a no score draw and 3 points for a score draw.
I am using the array formula:
=SUM(($E$12:$E$23=T3)*($F$12:$F$23<>"")*($F$12:$F$23=$G$12:$G$23)*(1))+SUM(($H$12:$H$23=T3)*($G$12:$G$23<>"")*($G$12:$G$23=$F$12:$F$23)*(1))
to work out a draw, wether it is score or no score draw.
E12:E23 and H12:H23 are the teams and F12:F23 and G12:G23 are the scores.
Question how can I adapt this formula or do I need to add another column to my spread sheet?
In Netball the losing team gets 1 point for 50% or more of the winners score.
In my rugby table I used the array:
=SUM(($E$8:$E$16=V3)*($G$8:$G$16-$F$8:$F$16<8)*($G$8:$G$16>$F$8:$F$16)*(1))+SUM(($H$8:$H$16=V3)*($G$8:$G$16<$F$8:$F$16)*($F$8:$F$16-$G$8:$G$16<8)*(1))
Where E6:E16 and H6:H16 are the teams, F6:F16 and G6:G16 are the scores, which gives a bonus point for losing by 7 points or less.
Question how can I adapt this to 50% or greater?
Many thanks in advance.
Martyn
Several of you helped me out last year when I was trying to create a spreadsheet that tracked Rugby Union scores. It was so good that I have been asked to amend it to cover Netball & Hockey (for inter school games). But they both have oddities.
In Hockey teams get 2 points for a no score draw and 3 points for a score draw.
I am using the array formula:
=SUM(($E$12:$E$23=T3)*($F$12:$F$23<>"")*($F$12:$F$23=$G$12:$G$23)*(1))+SUM(($H$12:$H$23=T3)*($G$12:$G$23<>"")*($G$12:$G$23=$F$12:$F$23)*(1))
to work out a draw, wether it is score or no score draw.
E12:E23 and H12:H23 are the teams and F12:F23 and G12:G23 are the scores.
Question how can I adapt this formula or do I need to add another column to my spread sheet?
In Netball the losing team gets 1 point for 50% or more of the winners score.
In my rugby table I used the array:
=SUM(($E$8:$E$16=V3)*($G$8:$G$16-$F$8:$F$16<8)*($G$8:$G$16>$F$8:$F$16)*(1))+SUM(($H$8:$H$16=V3)*($G$8:$G$16<$F$8:$F$16)*($F$8:$F$16-$G$8:$G$16<8)*(1))
Where E6:E16 and H6:H16 are the teams, F6:F16 and G6:G16 are the scores, which gives a bonus point for losing by 7 points or less.
Question how can I adapt this to 50% or greater?
Many thanks in advance.
Martyn
