Please forgive my hesitation. Last time I tried to explain how this system worked I got zero responses. This really should be in a proper database application, which I just haven't had the time to code... And I'd come back to the same question - how to handle effective dates in SQL. One day I hope to get around to writing the web application for this over MySQL.
Anyway. <deep sigh=""> For context. This is for tracking basketball umpiring (refereeing). Apologies for the length of this post.
There are 7 days a week, 2 sessions per day and and up to 8 games in a session. The below code shows the manually entered game games ([ref stats v8.xlsx]Games!)
| | | | | | | Slot 1 | Slot 2 | Slot 3 | Slot 4 | Slot 5 | Slot 6 | Slot 7 | Slot 8 (Wed only) | Game | | Outcomes | | | | | | | My | Chngd | My | Train | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | |
ID | WD | Game Date | Sess | Ven | Gndr | Pattern | Crt | Ref | Exp | Gr | Age | Crt | Ref | Exp | Gr | Age | Crt | Ref | Exp | Gr | Age | Crt | Ref | Exp | Gr | Age | Crt | Ref | Exp | Gr | Age | Crt | Ref | Exp | Gr | Age | Crt | Ref | Exp | Gr | Age | Crt | Ref | Exp | Gr | Age | Type | Super | (T,U,D,R,N) | TF | UF | RPT | Inj | Eval | Rost | Grade | Game | Exp | Games |
1332 | 4 | 3/10/2019 | 2 | L | W | GGG | 4 | 296 | 3 | ER | 99 | 3 | 296 | 3 | C | 99 | 2 | 357 | 4 | B | 99 | | | | | | | | | | | | | | | | | | | | | | | | | | RS | 93 | NNN | 000 | 000 | 000 | | | 11 | AR | | 5 | |
1333 | 2 | 8/10/2019 | 1 | K | G | GGGG | 1 | 393 | 4 | A | 11 | 1 | 393 | 4 | AR | 11 | 1 | 393 | 4 | A | 11 | 1 | 393 | 4 | AR | 11 | | | | | | | | | | | | | | | | | | | | | GR | 155 | NNNN | 0000 | 0000 | 0000 | | | 226 | AR | | 5 | |
1334 | 3 | 9/10/2019 | 2 | L | G | NGGGG | | | | | | 1 | 315 | 4 | AR | 16 | 2 | 289 | 4 | B | 16 | 1 | 389 | 4 | BR | 16 | 4 | 383 | 4 | B | 16 | | | | | | | | | | | | | | | | GR | 93 | NNNNN | 00000 | 00000 | 00000 | | | 226 | AR | | 5 | |
1335 | 4 | 10/10/2019 | 2 | L | W | GWG | 3 | 354 | 4 | D | 99 | 4 | 380 | 4 | ER | 99 | 2 | 301 | 4 | BR | 99 | | | | | | | | | | | | | | | | | | | | | | | | | | RS | 12 | NNN | 000 | 000 | 000 | | | 11 | AR | | 5 | |
1336 | 6 | 12/10/2019 | 1 | O | B | GGGBGG | 4 | 171 | 4 | BR | 13 | 2 | 342 | 4 | C | 13 | 4 | 328 | 4 | B | 13 | | | | | | 3 | 328 | 4 | B | 12 | 5 | 394 | 3 | BR | 12 | | | | | | | | | | | GR | 41 | NNNNNN | 000000 | 000000 | 000000 | | | 226 | AR | | 5 | |
1337 | 2 | 15/10/2019 | 1 | K | G | GGGG | 1 | 393 | 4 | AR | 11 | 1 | 393 | 4 | AR | 11 | 1 | 393 | 4 | A | 11 | 1 | 393 | 4 | A | 11 | | | | | | | | | | | | | | | | | | | | | GR | 155 | NNNN | 0000 | 0000 | 0000 | | | 226 | AR | | 5 | |
1338 | 3 | 16/10/2019 | 2 | L | G | GGBGG | 3 | 375 | 4 | CR | 14 | 1 | 147 | 5 | AR | 16 | | | | | | 2 | 315 | 4 | B | 16 | 4 | 299 | 4 | B | 21 | | | | | | | | | | | | | | | | GR | 93 | NNNNN | 00000 | 00000 | 00000 | | | 226 | AR | | 5 | |
1339 | 4 | 17/10/2019 | 2 | L | W | GG | 3 | 351 | 3 | ER | 99 | 3 | 372 | 3 | ER | 99 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | SF | 12 | NN | 01 | 01 | 00 | | | 11 | AR | | 5 | |
1340 | 6 | 19/10/2019 | 1 | O | B | GGGBGG | 3 | 296 | 3 | BR | 13 | 3 | 388 | 3 | C | 13 | 4 | 395 | 4 | B | 13 | | | | | | 3 | 390 | 3 | B | 12 | 5 | 395 | 4 | BR | 12 | | | | | | | | | | | GR | 41 | NNNNNN | 000000 | 000000 | 000000 | | | 226 | AR | | 5 | |
1341 | 2 | 22/10/2019 | 1 | K | G | GGGG | 6 | 393 | 4 | AR | 11 | 6 | 393 | 4 | A | 11 | 6 | 393 | 4 | A | 11 | 6 | 393 | 4 | AR | 11 | | | | | | | | | | | | | | | | | | | | | GR | 155 | NNNN | 0000 | 0000 | 0000 | | | 226 | AR | | 5 | |
1342 | 3 | 23/10/2019 | 2 | L | G | GGGGG | 1 | 315 | 4 | AR | 14 | 4 | 226 | 4 | C | 16 | 2 | 314 | 4 | B | 16 | 2 | 336 | 4 | B | 16 | 1 | 171 | 4 | B | 21 | | | | | | | | | | | | | | | | GR | 93 | NNNNN | 00000 | 00000 | 00000 | | | 226 | AR | | 5 | |
1343 | 4 | 24/10/2019 | 2 | K | W | GG | 3 | 341 | 4 | D | 99 | 3 | 351 | 4 | ER | 99 | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | | GF | 2 | NN | 00 | 00 | 00 | | | 11 | AR | | 5 | |
1344 | 6 | 26/10/2019 | 1 | O | B | GGGBGG | 3 | 333 | 3 | BR | 13 | 3 | 382 | 3 | C | 13 | 4 | 396 | 2 | B | 13 | | | | | | 3 | 396 | 2 | B | 12 | 5 | 391 | 3 | BR | 12 | | | | | | | | | | | GR | 41 | NNNNNN | 000000 | 000000 | 000000 | | | 226 | AR | | 5 | |
1345 | 2 | 29/10/2019 | 1 | K | G | GGGG | 6 | 393 | 4 | AR | 11 | 6 | 393 | 4 | A | 11 | 6 | 393 | 4 | A | 11 | 6 | 393 | 4 | AR | 11 | | | | | | | | | | | | | | | | | | | | | GR | 155 | NNNN | 0000 | 0000 | 0000 | | | 226 | AR | | 5 | |
1346 | 3 | 30/10/2019 | 2 | L | G | GGBGG | 2 | 149 | 4 | B | 14 | 1 | 314 | 3 | A | 16 | | | | | | 2 | 171 | 4 | B | 16 | 3 | 375 | 3 | B | 16 | | | | | | | | | | | | | | | | GR | 93 | NNNNN | 00000 | 01000 | 00000 | | | 226 | AR | | 5 | |
1347 | 4 | 31/10/2019 | 2 | L | W | GGG | 3 | 351 | 3 | C | 99 | 2 | 351 | 3 | D | 99 | 3 | 314 | 3 | B | 99 | | | | | | | | | | | | | | | | | | | | | | | | | | GR | 12 | NNN | 000 | 000 | 000 | | | 11 | AR | | 5 | |
WD=Weekday, Ref=number of referee in the [Ref Stats v8.xlsx]Referees! sheet. Pattern: G=Game,N=Not allocated, B=Break, A=After School Game
This data is referenced/summarized in [Ref Finances.xlsx]Summary!
DOW | Ven | Gender | Date | Pattern | Total | A/Grp | G/Typ | Year | Month | Games | Bench | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | Sex | | Sess | Rate | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | | Pay | Outcomes | | | | | | | |
3 | K | G | 11/09/2019 | GGNN | 39.00 | J | F | 2019 | 9 | 2 | | 12 | 12 | | | | | | | F | ARB | 2 | 3 | 3 | | | | | | | 12 | 12 | | | | | | | | $ 39.00 | NNNN |
4 | L | W | 12/09/2019 | GGG | 50.10 | S | R | 2019 | 9 | 3 | | 99 | 99 | 99 | | | | | | F | D C B | 2 | 5 | 5 | 5 | | | | | | 99 | 99 | 99 | | | | | | | $ 50.10 | NNN |
6 | K | B | 14/09/2019 | GGG | 58.50 | J | F | 2019 | 9 | 3 | | 9 | 11 | 10 | | | | | | M | A B AR | 1 | 3 | 3 | 3 | | | | | | 9 | 11 | 10 | | | | | | | $ 58.50 | NNN |
2 | O | G | 17/09/2019 | GG | 39.00 | J | F | 2019 | 9 | 2 | | 9 | 11 | | | | | | | F | B AR | 1 | 3 | 3 | | | | | | | 9 | 11 | | | | | | | | $ 39.00 | NN |
3 | K | G | 18/09/2019 | NNNN | | J | F | 2019 | 9 | | | | | | | | | | | F | | 1 | | | | | | | | | | | | | | | | | | $ - | NNNN |
4 | L | W | 19/09/2019 | GGG | 50.10 | S | R | 2019 | 9 | 3 | | 99 | 99 | 99 | | | | | | F | D BRBR | 2 | 5 | 5 | 5 | | | | | | 99 | 99 | 99 | | | | | | | $ 50.10 | NNN |
6 | K | B | 21/09/2019 | GGGNN | 58.50 | J | F | 2019 | 9 | 3 | | 9 | 10 | 12 | | | | | | M | A ARB | 1 | 3 | 3 | 3 | | | | | | 9 | 10 | 12 | | | | | | | $ 58.50 | NNNNN |
4 | L | W | 26/09/2019 | GGG | 50.10 | S | R | 2019 | 9 | 3 | | 99 | 99 | 99 | | | | | | F | ERBRB | 2 | 5 | 5 | 5 | | | | | | 99 | 99 | 99 | | | | | | | $ 50.10 | NNN |
4 | L | W | 3/10/2019 | GGG | 50.10 | S | R | 2019 | 10 | 3 | | 99 | 99 | 99 | | | | | | F | ERC B | 2 | 5 | 5 | 5 | | | | | | 99 | 99 | 99 | | | | | | | $ 50.10 | NNN |
2 | K | G | 8/10/2019 | GGGG | 44.00 | J | R | 2019 | 10 | 4 | | 11 | 11 | 11 | 11 | | | | | F | A ARA AR | 1 | 3 | 3 | 3 | 3 | | | | | 11 | 11 | 11 | 11 | | | | | | $ 44.00 | NNNN |
3 | L | G | 9/10/2019 | NGGGG | 62.40 | J | R | 2019 | 10 | 4 | | | 16 | 16 | 16 | 16 | | | | F | ARB BRB | 2 | | 3 | 3 | 3 | 3 | | | | | 16 | 16 | 16 | 16 | | | | | $ 62.40 | NNNNN |
4 | L | W | 10/10/2019 | GWG | 50.10 | S | R | 2019 | 10 | 3 | | 99 | 99 | 99 | | | | | | F | D ERBR | 2 | 5 | 5 | 5 | | | | | | 99 | 99 | 99 | | | | | | | $ 50.10 | NNN |
6 | O | B | 12/10/2019 | GGGBGG | 78.00 | J | R | 2019 | 10 | 5 | | 13 | 13 | 13 | | 12 | 12 | | | M | BRC B B BR | 1 | 3 | 3 | 3 | | 3 | 3 | | | 13 | 13 | 13 | | 12 | 12 | | | | $ 78.00 | NNNNNN |
2 | K | G | 15/10/2019 | GGGG | 50.00 | J | R | 2019 | 10 | 4 | | 11 | 11 | 11 | 11 | | | | | F | ARARA A | 1 | 3 | 3 | 3 | 3 | | | | | 11 | 11 | 11 | 11 | | | | | | $ 50.00 | NNNN |
3 | L | G | 16/10/2019 | GGBGG | 63.50 | J | R | 2019 | 10 | 4 | | 14 | 16 | | 16 | 21 | | | | F | CRAR B B | 2 | 3 | 3 | | 3 | 4 | | | | 14 | 16 | | 16 | 21 | | | | | $ 63.50 | NNNNN |
4 | L | W | 17/10/2019 | GG | 43.00 | S | F | 2019 | 10 | 2 | | 99 | 99 | | | | | | | F | ERER | 2 | 5 | 5 | | | | | | | 99 | 99 | | | | | | | | $ 43.00 | NN |
6 | O | B | 19/10/2019 | GGGBGG | 78.00 | J | R | 2019 | 10 | 5 | | 13 | 13 | 13 | | 12 | 12 | | | M | BRC B B BR | 1 | 3 | 3 | 3 | | 3 | 3 | | | 13 | 13 | 13 | | 12 | 12 | | | | $ 78.00 | NNNNNN |
2 | K | G | 22/10/2019 | GGGG | 50.00 | J | R | 2019 | 10 | 4 | | 11 | 11 | 11 | 11 | | | | | F | ARA A AR | 1 | 3 | 3 | 3 | 3 | | | | | 11 | 11 | 11 | 11 | | | | | | $ 50.00 | NNNN |
3 | L | G | 23/10/2019 | GGGGG | 79.10 | J | R | 2019 | 10 | 5 | | 14 | 16 | 16 | 16 | 21 | | | | F | ARC B B B | 2 | 3 | 3 | 3 | 3 | 4 | | | | 14 | 16 | 16 | 16 | 21 | | | | | $ 79.10 | NNNNN |
4 | K | W | 24/10/2019 | GG | 43.00 | S | F | 2019 | 10 | 2 | | 99 | 99 | | | | | | | F | D ER | 2 | 5 | 5 | | | | | | | 99 | 99 | | | | | | | | $ 43.00 | NN |
6 | O | B | 26/10/2019 | GGGBGG | | J | R | 2019 | 10 | 5 | | 13 | 13 | 13 | | 12 | 12 | | | M | BRC B B BR | | | | | | | | | | | | | | | | | | | $ - | |
G/Type is R=Regular Season (GR, RS), F=Finals (PF, SF, GF) Rate is currently determined by the Age group except where the game type = "A" (After School). This sloppy and does not work well. The rate number for each game is a simple vlookup based on the age.
Code:
=IF(AG1332=0,"",IF(MID($F1332,COLUMN()-24,1)="A",VLOOKUP(AG1332,Rates!$Z$18:$AA$22,2,0),VLOOKUP(AG1332,Rates!$Z$4:$AA$16,2,0)))
That rate number is then used in the [Ref Finances.xls]Payments! Sheet to vlookup the payment for that game/time slot.
The existing rates table only works by year and month and doesn't cater for changes mid month or gender.
Payments snippet...
| | | | | | | | | Calculated | | Actual | Mentoring | |
Game Date | Game 1 | Game 2 | Game 3 | Game 4 | Game 5 | Game 6 | Game 7 | Game 8 | Pay | | Pay | Trainees | Varaiance |
10/09/2019 | $ 19.50 | $ 19.50 | $ - | $ - | $ - | $ - | $ - | $ - | $ 39.00 | | $ 39.00 | | $ - |
11/09/2019 | $ 19.50 | $ 19.50 | $ - | $ - | $ - | $ - | $ - | $ - | $ 39.00 | | $ 39.00 | | $ - |
12/09/2019 | $ 16.70 | $ 16.70 | $ 16.70 | $ - | $ - | $ - | $ - | $ - | $ 50.10 | | $ 50.10 | | $ - |
14/09/2019 | $ 19.50 | $ 19.50 | $ 19.50 | $ - | $ - | $ - | $ - | $ - | $ 58.50 | | $ 58.50 | | $ - |
17/09/2019 | $ 19.50 | $ 19.50 | $ - | $ - | $ - | $ - | $ - | $ - | $ 39.00 | | $ 39.00 | | $ - |
18/09/2019 | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | $ - | | $ - | | $ - |
19/09/2019 | $ 16.70 | $ 16.70 | $ 16.70 | $ - | $ - | $ - | $ - | $ - | $ 50.10 | | $ 50.10 | | $ - |
21/09/2019 | $ 19.50 | $ 19.50 | $ 19.50 | $ - | $ - | $ - | $ - | $ - | $ 58.50 | | $ 58.50 | | $ - |
26/09/2019 | $ 16.70 | $ 16.70 | $ 16.70 | $ - | $ - | $ - | $ - | $ - | $ 50.10 | | $ 50.10 | | $ - |
3/10/2019 | $ 16.70 | $ 16.70 | $ 16.70 | $ - | $ - | $ - | $ - | $ - | $ 50.10 | | $ 50.10 | | $ - |
8/10/2019 | $ 11.00 | $ 11.00 | $ 11.00 | $ 11.00 | $ - | $ - | $ - | $ - | $ 44.00 | | $ 44.00 | | $ - |
9/10/2019 | $ - | $ 15.60 | $ 15.60 | $ 15.60 | $ 15.60 | $ - | $ - | $ - | $ 62.40 | | $ 62.40 | | $ - |
10/10/2019 | $ 16.70 | $ 16.70 | $ 16.70 | $ - | $ - | $ - | $ - | $ - | $ 50.10 | | $ 50.10 | | $ - |
12/10/2019 | $ 15.60 | $ 15.60 | $ 15.60 | $ - | $ 15.60 | $ 15.60 | $ - | $ - | $ 78.00 | | $ 78.00 | | $ - |
15/10/2019 | $ 12.50 | $ 12.50 | $ 12.50 | $ 12.50 | $ - | $ - | $ - | $ - | $ 50.00 | | $ 50.00 | | $ - |
16/10/2019 | $ 15.60 | $ 15.60 | $ - | $ 15.60 | $ 16.70 | $ - | $ - | $ - | $ 63.50 | | $ 63.50 | | $ - |
17/10/2019 | $ 21.50 | $ 21.50 | $ - | $ - | $ - | $ - | $ - | $ - | $ 43.00 | | $ 43.00 | | $ - |
19/10/2019 | $ 15.60 | $ 15.60 | $ 15.60 | $ - | $ 15.60 | $ 15.60 | $ - | $ - | $ 78.00 | | $ 78.00 | | $ - |
22/10/2019 | $ 12.50 | $ 12.50 | $ 12.50 | $ 12.50 | $ - | $ - | $ - | $ - | $ 50.00 | | $ 50.00 | | $ - |
23/10/2019 | $ 15.60 | $ 15.60 | $ 15.60 | $ 15.60 | $ 16.70 | $ - | $ - | $ - | $ 79.10 | | $ 79.10 | | $ - |
24/10/2019 | $ 21.50 | $ 21.50 | $ - | $ - | $ - | $ - | $ - | $ - | $ 43.00 | | $ 43.00 | | $ - |
The current rates table... ([Ref Finances.xls]Rates!)
| | | Regular Season | Finals | | | | | | | | | | | | |
Mth | Year | Gr | Minball | A/School | GU9-GU11 | U9 - U16 | U18 - U21 | Seniors | Hoop Time | Minball | A/School | U9 - U16 | U18 - U21 | Seniors | Hoop Time | Scoring |
2 | 2018 | AR | $ 9.70 | $ 9.30 | | $ 13.80 | $ 14.90 | $ 14.90 | $ 10.30 | | $ 9.30 | $ 17.40 | $ 20.00 | $ 20.00 | | $ 10.00 |
3 | 2018 | AR | $ 10.00 | $ 10.20 | | $ 14.60 | $ 15.70 | $ 15.70 | $ 10.20 | | $ 10.20 | $ 18.50 | $ 20.80 | $ 20.50 | | $ 10.00 |
4 | 2018 | AR | $ 10.00 | $ 10.20 | | $ 14.60 | $ 15.70 | $ 15.70 | $ 10.20 | | $ 10.20 | $ 18.50 | $ 20.80 | $ 20.50 | | $ 10.00 |
5 | 2018 | AR | $ 10.00 | $ 10.20 | | $ 14.60 | $ 15.70 | $ 15.70 | $ 10.20 | | $ 10.20 | $ 18.50 | $ 20.80 | $ 20.50 | | $ 10.00 |
6 | 2018 | AR | $ 10.00 | $ 10.20 | | $ 14.60 | $ 15.70 | $ 15.70 | $ 10.20 | | $ 10.20 | $ 18.50 | $ 20.80 | $ 20.50 | | $ 10.00 |
7 | 2018 | AR | $ 10.00 | $ 10.20 | | $ 14.60 | $ 15.70 | $ 15.70 | $ 10.20 | | $ 10.20 | $ 18.50 | $ 20.80 | $ 20.50 | | $ 10.00 |
8 | 2018 | AR | $ 10.00 | $ 10.20 | | $ 14.60 | $ 15.70 | $ 15.70 | $ 10.20 | | $ 10.20 | $ 18.50 | $ 20.80 | $ 20.50 | | $ 10.00 |
9 | 2018 | AR | $ 10.00 | $ 10.20 | | $ 14.60 | $ 15.70 | $ 15.70 | $ 10.20 | | $ 10.20 | $ 18.50 | $ 20.80 | $ 20.50 | | $ 10.00 |
10 | 2018 | AR | $ 10.00 | $ 10.20 | | $ 14.60 | $ 15.70 | $ 15.70 | $ 10.20 | | $ 10.20 | $ 18.50 | $ 20.80 | $ 20.50 | | $ 10.00 |
11 | 2018 | AR | $ 10.00 | $ 10.20 | | $ 14.60 | $ 15.70 | $ 15.70 | $ 10.20 | | $ 10.20 | $ 18.50 | $ 20.80 | $ 20.50 | | $ 10.00 |
12 | 2018 | AR | $ 10.00 | $ 10.20 | | $ 14.60 | $ 15.70 | $ 15.70 | $ 10.20 | | $ 10.20 | $ 18.50 | $ 20.80 | $ 20.50 | | $ 10.00 |
1 | 2019 | AR | $ 9.70 | $ 10.20 | | $ 14.60 | $ 15.70 | $ 15.70 | $ 10.20 | | $ 10.20 | $ 17.40 | $ 20.00 | $ 20.00 | | $ 10.00 |
2 | 2019 | AR | $ 9.70 | $ 10.20 | | $ 14.60 | $ 15.70 | $ 15.70 | $ 10.20 | | $ 10.20 | $ 17.40 | $ 20.00 | $ 20.00 | | $ 10.00 |
3 | 2019 | AR | $ 10.00 | $ 11.00 | | $ 15.60 | $ 16.70 | $ 16.70 | $ 11.00 | | $ 12.60 | $ 19.50 | $ 20.50 | $ 21.50 | | $ 10.00 |
4 | 2019 | AR | $ 10.00 | $ 11.00 | | $ 15.60 | $ 16.70 | $ 16.70 | $ 11.00 | | $ 12.60 | $ 19.50 | $ 20.50 | $ 21.50 | | $ 10.00 |
5 | 2019 | AR | $ 10.00 | $ 11.00 | | $ 15.60 | $ 16.70 | $ 16.70 | $ 11.00 | | $ 12.60 | $ 19.50 | $ 20.50 | $ 21.50 | | $ 10.00 |
6 | 2019 | AR | $ 10.00 | $ 11.00 | | $ 15.60 | $ 16.70 | $ 16.70 | $ 11.00 | | $ 12.60 | $ 19.50 | $ 20.50 | $ 21.50 | | $ 10.00 |
7 | 2019 | AR | $ 10.00 | $ 11.00 | | $ 15.60 | $ 16.70 | $ 16.70 | $ 11.00 | | $ 12.60 | $ 19.50 | $ 20.50 | $ 21.50 | | $ 10.00 |
8 | 2019 | AR | $ 10.00 | $ 11.00 | | $ 15.60 | $ 16.70 | $ 16.70 | $ 11.00 | | $ 12.60 | $ 19.50 | $ 20.50 | $ 21.50 | | $ 10.00 |
9 | 2019 | AR | $ 10.00 | $ 11.00 | | $ 15.60 | $ 16.70 | $ 16.70 | $ 11.00 | | $ 12.60 | $ 19.50 | $ 20.50 | $ 21.50 | | $ 10.00 |
10 | 2019 | AR | $ 10.00 | $ 11.00 | $ 11.00 | $ 15.60 | $ 16.70 | $ 16.70 | $ 11.00 | | $ 12.60 | $ 19.50 | $ 20.50 | $ 21.50 | | $ 10.00 |
11 | 2019 | AR | $ 10.00 | $ 11.00 | $ 12.50 | $ 15.60 | $ 16.70 | $ 16.70 | $ 11.00 | | $ 12.60 | $ 19.50 | $ 20.50 | $ 21.50 | | $ 10.00 |
12 | 2019 | AR | $ 10.00 | $ 11.00 | $ 12.50 | $ 15.60 | $ 16.70 | $ 16.70 | $ 11.00 | | $ 12.60 | $ 19.50 | $ 20.50 | $ 21.50 | | $ 10.00 |
Ignore Hoop Time, it's redundant.
The payment is calculated by 'CHOOSING' the rate from the rates table using the rate number on the summary table.
Code:
=IF(V1352>0,V1352,IF(Summary!Y1351="",0,IF(Summary!$I1351="R",SUMIFS(CHOOSE(Summary!Y1351,Rates!$E$4:$E$112,Rates!$F$4:$F$112,Rates!$H$4:$H$112,Rates!$I$4:$I$112,Rates!$J$4:$J$112,Rates!$K$4:$K$112),Rates!$B$4:$B$112,MONTH($B1352),Rates!$C$4:$C$112,YEAR($B1352)),SUMIFS(CHOOSE(Summary!Y1351,Rates!$L$4:$L$112,Rates!$M$4:$M$112,Rates!$N$4:$N$112,Rates!$O$4:$O$112,Rates!$P$4:$P$112,Rates!$Q$4:$Q$112),Rates!$B$4:$B$112,MONTH($B1352),Rates!$C$4:$C$112,YEAR($B1352)))))
Like I said, sloppy, cumbersome and overly complex. This has just evolved over the last 8 years. It's time I fixed it up properly. I added in the change to the U9-11 and that broke the payments calculation, and it doesn't take gender into account, which it must now do.
Rather than maintain a table of rates for 12 entries per year, I figured it was easier to just have dates when the rates change and find the correct rate to apply based on the game date, age, gender, competition type and game type. (Competition types are Juniors, Seniors and After School - J, S, and A).
The goal is only one place is regularly manually updated - that is the games sheet in [Ref Stats V8.xls]. EVERYTHING else is calculated. New rates are added only when they change. Except for 'Actual pay' which is manually entered. If it differs from expected pay there will be a variance which I can then follow up with the office.
Please let me know if I've missed anything.