Sports league tables

martyn42

New Member
Joined
Jan 17, 2005
Messages
5
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Forum statistics

Threads
1,136,613
Messages
5,676,820
Members
419,653
Latest member
analyticalchemist94

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top