Finding a value based on an effective date with multiple criteria

Yanta

Board Regular
Joined
Aug 28, 2011
Messages
59
I have a sheet with pay rates. There are about 80 different pay rates that differ for age, gender and competition type.

Rather than have a sheet with a rate for every day of the year for the past 12 years I'm wondering if there is a way to do this with effective dates.

So when I'm looking up a pay rate I will look at the age, gender and competition type for a team where the date of the game is >= the most recent pay rate change for those criteria.

Pay rates could change for any gender, age or competition type at any time. There is a regular rise March 1 each year, but adhoc changes also occur.

I have the date split into 3 columns (Year, Month, Day), thinking maybe that was the easiest way to do it. Maybe it's best to just use a single date field?

Would be grateful for some pointers.

Thanks

A small snippet...

Effective DateBoysGirls
YearMthDayGrU9U10U11U12U13U14U15U16U18U21U9U10U11U12U13U14U15U16U18U21
201261C $ 9.70 $ 9.70 $ 9.70 $ 9.70 $ 9.70 $ 9.70 $ 9.70 $ 9.70 $ 10.30 $ 10.30 $ 9.70 $ 9.70 $ 9.70 $ 9.70 $ 9.70 $ 9.70 $ 9.70 $ 9.70 $ 10.30 $ 10.30
201311B $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 12.90 $ 12.90 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 12.90 $ 12.90
201331B $ 11.30 $ 11.30 $ 11.30 $ 11.30 $ 11.30 $ 11.30 $ 11.30 $ 11.30 $ 13.20 $ 13.20 $ 11.30 $ 11.30 $ 11.30 $ 11.30 $ 11.30 $ 11.30 $ 11.30 $ 11.30 $ 12.90 $ 12.90
201431B $ 11.60 $ 11.60 $ 11.60 $ 11.60 $ 11.60 $ 11.60 $ 11.60 $ 11.60 $ 13.50 $ 13.50 $ 11.60 $ 11.60 $ 11.60 $ 11.60 $ 11.60 $ 11.60 $ 11.60 $ 11.60 $ 13.50 $ 13.50
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
If I have understood correctly, then don't break up the dates into year/month/day just make sure that the dates are in ascending order.

See if this, copied down, does what you want. It does rely on the age groups for boys and girls being identical - which is the case with your sample.

Yanta.xlsm
ABCDEFGHIJKLMNOPQRSTUV
1BoysGirls
2GrU9U10U11U12U13U14U15U16U18U21U9U10U11U12U13U14U15U16U18U21
31/06/2012C9.79.79.79.79.79.79.79.710.310.39.79.79.79.79.79.79.79.710.310.3
41/01/2013B111111111111111112.912.9111111111111111112.912.9
51/03/2013B11.311.311.311.311.311.311.311.313.213.211.311.311.311.311.311.311.311.312.912.9
61/03/2014B11.611.611.611.611.611.611.611.613.513.511.611.611.611.611.611.611.611.613.513.5
7
8
925/02/2013BBoysU1211
1025/02/2013CGirlsU159.7
111/11/2019BBoysU2113.5
Lookup Rate
Cell Formulas
RangeFormula
E9:E11E9=INDEX($C$3:$V$6,AGGREGATE(14,6,(ROW(A$3:A$6)-ROW(A$3)+1)/(($B$3:$B$6=B9)*($A$3:$A$6<=A9)),1),MATCH(C9,$C$1:$V$1,0)+MATCH(D9,$C$2:$V$2,0)-1)
 
Last edited:
Upvote 0
Sorry, no the age groups for boys and girls are not identical. There are no U13 or U15 girls. Until April 2019 there were no U11 girls. From April 2019 there are no longer any U10 girls -it's been replaced by U11.

I've included them because "One day there 'could be' and U13/U15 girls"

And in my sample you will see that the rates for all U9 - U16 are the same. But they "could" be different. In October 2019 Girls U9 - U11 changed from $15.60 to $11.00 then to $12.50 a week later.

To make matters more complex I only pasted the "regular season" section (competition type). If the game type is SF, PF or GF then it's a final and different rates apply. If the game type is GR or RS then it's grading or regular season, which is the section I posted.

OMG - And yet another criteria - if the competition is After School, then the day of the week also comes into play. Currently they are all the same but the association has stated they could change in the future.

So, for completeness, this is the whole rows with the date in one column as per your advice.

The criteria used for evaluation is in a sheet called 'Payments'. This sheet is called 'Rates 2'


Regular SeasonFinalsScore
Effective DateBoysGirlsAfter School/MiniballSeniorsBoysGirlsAfter SchoolSeniorsTable
GrU9U10U11U12U13U14U15U16U18U21U9U10U11U12U13U14U15U16U18U21MonTuesThursFriMenWomenU9U10U11U12U13U14U15U16U18U21U9U10U11U12U13U14U15U16U18U21MonTuesThursFriMenWomen
2012/06/01C $ 9.70 $ 9.70 $ 9.70 $ 9.70 $ 9.70 $ 9.70 $ 9.70 $ 9.70 $ 10.30 $ 10.30 $ 9.70 $ 9.70 $ - $ 9.70 $ - $ 9.70 $ - $ 9.70 $ 10.30 $ 10.30 $ 8.40 $ 8.40 $ 8.40 $ 8.40 $ 10.30 $ 10.30 $ 15.30 $ 15.30 $ 15.30 $ 15.30 $ 15.30 $ 15.30 $ 15.30 $ 15.30 $ 17.80 $ 17.80 $ 15.30 $ 15.30 $ - $ 15.30 $ - $ 15.30 $ - $ 15.30 $ 17.80 $ 17.80 $ 8.40 $ 8.40 $ 8.40 $ 8.40 $ 17.80 $ 17.80 $ 5.00
2013/01/01B $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 12.90 $ 12.90 $ 11.00 $ 11.00 $ - $ 11.00 $ - $ 11.00 $ - $ 11.00 $ 12.90 $ 12.90 $ 8.40 $ 8.40 $ 8.40 $ 8.40 $ 12.90 $ 12.90 $ 15.30 $ 15.30 $ 15.30 $ 15.30 $ 15.30 $ 15.30 $ 15.30 $ 15.30 $ 17.80 $ 17.80 $ 15.30 $ 15.30 $ - $ 15.30 $ - $ 15.30 $ - $ 15.30 $ 17.80 $ 17.80 $ 8.40 $ 8.40 $ 8.40 $ 8.40 $ 17.80 $ 17.80 $ 5.00
2013/03/01B $ 11.30 $ 11.30 $ 11.30 $ 11.30 $ 11.30 $ 11.30 $ 11.30 $ 11.30 $ 13.20 $ 13.20 $ 11.30 $ 11.30 $ - $ 11.30 $ - $ 11.30 $ - $ 11.30 $ 12.90 $ 12.90 $ 8.70 $ 8.70 $ 8.70 $ 8.70 $ 12.90 $ 13.20 $ 15.90 $ 15.90 $ 15.90 $ 15.90 $ 15.90 $ 15.90 $ 15.90 $ 15.90 $ 18.20 $ 18.20 $ 15.90 $ 15.90 $ - $ 15.90 $ - $ 15.90 $ - $ 18.20 $ 18.20 $ 15.90 $ 8.40 $ 8.40 $ 8.40 $ 8.40 $ 18.20 $ 15.90 $ 5.00
2014/03/01B $ 11.60 $ 11.60 $ 11.60 $ 11.60 $ 11.60 $ 11.60 $ 11.60 $ 11.60 $ 13.50 $ 13.50 $ 11.60 $ 11.60 $ - $ 11.60 $ - $ 11.60 $ - $ 11.60 $ 13.50 $ 13.50 $ 9.00 $ 9.00 $ 9.00 $ 9.00 $ 13.50 $ 13.50 $ 16.10 $ 16.10 $ 16.10 $ 16.10 $ 16.10 $ 16.10 $ 16.10 $ 16.10 $ 18.50 $ 18.50 $ 16.10 $ 16.10 $ - $ 16.10 $ - $ 16.10 $ - $ 16.10 $ 18.50 $ 18.50 $ 9.00 $ 9.00 $ 9.00 $ 9.00 $ 18.50 $ 18.50 $ 10.00
2015/03/01B $ 11.90 $ 11.90 $ 11.90 $ 11.90 $ 11.90 $ 11.90 $ 11.90 $ 11.90 $ 13.90 $ 13.90 $ 11.90 $ 11.90 $ - $ 11.90 $ - $ 11.90 $ - $ 11.90 $ 13.90 $ 13.90 $ 9.00 $ 9.00 $ 9.00 $ 9.00 $ 13.90 $ 13.90 $ 16.60 $ 16.60 $ 16.60 $ 16.60 $ 16.60 $ 16.60 $ 16.60 $ 16.60 $ 18.80 $ 18.80 $ 16.60 $ 16.60 $ - $ 16.60 $ - $ 16.60 $ - $ 16.60 $ 18.80 $ 18.80 $ 9.00 $ 9.00 $ 9.00 $ 9.00 $ 18.80 $ 18.80 $ 10.00
2016/03/01B $ 12.20 $ 12.20 $ 12.20 $ 12.20 $ 12.20 $ 12.20 $ 12.20 $ 12.20 $ 14.20 $ 14.20 $ 12.20 $ 12.20 $ - $ 12.20 $ - $ 12.20 $ - $ 12.20 $ 14.20 $ 14.20 $ 9.30 $ 9.30 $ 9.30 $ 9.30 $ 14.20 $ 14.20 $ 17.00 $ 17.00 $ 17.00 $ 17.00 $ 17.00 $ 17.00 $ 17.00 $ 17.00 $ 19.30 $ 19.30 $ 17.00 $ 17.00 $ - $ 17.00 $ - $ 17.00 $ - $ 17.00 $ 19.30 $ 19.30 $ 9.30 $ 9.30 $ 9.30 $ 9.30 $ 19.30 $ 19.30 $ 10.00
2016/05/01AR $ 13.60 $ 13.60 $ 13.60 $ 13.60 $ 13.60 $ 13.60 $ 13.60 $ 13.60 $ 14.70 $ 14.70 $ 13.60 $ 13.60 $ - $ 13.60 $ - $ 13.60 $ - $ 13.60 $ 14.70 $ 14.70 $ 9.30 $ 9.30 $ 9.30 $ 9.30 $ 14.70 $ 14.70 $ 17.40 $ 17.40 $ 17.40 $ 17.40 $ 17.40 $ 17.40 $ 17.40 $ 17.40 $ 19.80 $ 19.80 $ 17.40 $ 17.40 $ - $ 17.40 $ - $ 17.40 $ - $ 17.40 $ 19.80 $ 19.80 $ 9.30 $ 9.30 $ 9.30 $ 9.30 $ 19.80 $ 19.80 $ 10.00
2017/03/01AR $ 13.80 $ 13.80 $ 13.80 $ 13.80 $ 13.80 $ 13.80 $ 13.80 $ 13.80 $ 14.90 $ 14.90 $ 13.80 $ 13.80 $ - $ 13.80 $ - $ 13.80 $ - $ 13.80 $ 14.90 $ 14.90 $ 9.30 $ 9.30 $ 9.30 $ 9.30 $ 14.90 $ 14.90 $ 17.40 $ 17.40 $ 17.40 $ 17.40 $ 17.40 $ 17.40 $ 17.40 $ 17.40 $ 20.00 $ 20.00 $ 17.40 $ 17.40 $ - $ 17.40 $ - $ 17.40 $ - $ 17.40 $ 20.00 $ 20.00 $ 9.30 $ 9.30 $ 9.30 $ 9.30 $ 20.00 $ 20.00 $ 10.00
2018/03/01AR $ 14.60 $ 14.60 $ 14.60 $ 14.60 $ 14.60 $ 14.60 $ 14.60 $ 14.60 $ 15.70 $ 15.70 $ 14.60 $ 14.60 $ - $ 14.60 $ - $ 14.60 $ - $ 14.60 $ 15.70 $ 15.70 $ 10.00 $ 10.00 $ 10.00 $ 10.00 $ 15.70 $ 15.80 $ 18.50 $ 18.50 $ 18.50 $ 18.50 $ 18.50 $ 18.50 $ 18.50 $ 18.50 $ 20.50 $ 20.50 $ 18.50 $ 18.50 $ - $ 19.50 $ - $ 18.50 $ - $ 18.50 $ 20.50 $ 20.50 $ 10.20 $ 10.20 $ 10.20 $ 10.20 $ 20.50 $ 20.50 $ 10.00
2019/03/01AR $ 15.60 $ 15.60 $ 15.60 $ 15.60 $ 15.60 $ 15.60 $ 15.60 $ 15.60 $ 16.70 $ 16.70 $ 15.60 $ 15.60 $ - $ 15.60 $ - $ 15.60 $ - $ 15.60 $ 16.70 $ 16.70 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 16.70 $ 16.70 $ 19.50 $ 19.50 $ 19.50 $ 19.50 $ 19.50 $ 19.50 $ 19.50 $ 19.50 $ 20.50 $ 20.50 $ 19.50 $ 19.50 $ 19.50 $ - $ 19.50 $ - $ 19.50 $ 20.50 $ 20.50 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 21.50 $ 21.50 $ 10.00
2019/10/05
AR $ 15.60 $ 15.60 $ 15.60 $ 15.60 $ 15.60 $ 15.60 $ 15.60 $ 15.60 $ 16.70 $ 16.70 $ 15.60 $ - $ 11.00 $ 15.60 $ - $ 15.60 $ - $ 15.60 $ 16.70 $ 16.70 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 16.70 $ 16.70 $ 19.50 $ 19.50 $ 19.50 $ 19.50 $ 19.50 $ 19.50 $ 19.50 $ 19.50 $ 20.50 $ 20.50 $ 19.50 $ - $ 14.50 $ 19.50 $ - $ 19.50 $ - $ 19.50 $ 20.50 $ 20.50 $ 12.60 $ 12.60 $ 12.60 $ 12.60 $ 21.50 $ 21.50 $ 10.00
2019/10/12
AR $ 15.60 $ 15.60 $ 15.60 $ 15.60 $ 15.60 $ 15.60 $ 15.60 $ 15.60 $ 16.70 $ 16.70 $ 15.60 $ - $ 12.50 $ 15.60 $ - $ 15.60 $ - $ 15.60 $ 16.70 $ 16.70 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 16.70 $ 16.70 $ 19.50 $ 19.50 $ 19.50 $ 19.50 $ 19.50 $ 19.50 $ 19.50 $ 19.50 $ 20.50 $ 20.50 $ 19.50 $ - $ 14.50 $ 19.50 $ - $ 19.50 $ - $ 19.50 $ 20.50 $ 20.50 $ 12.60 $ 12.60 $ 12.60 $ 12.60 $ 21.50 $ 21.50 $ 10.00
2020/03/01AR $ 15.90 $ 15.90 $ 15.90 $ 15.90 $ 15.90 $ 15.90 $ 15.90 $ 15.90 $ 17.00 $ 17.00 $ 15.90 $ - $ 12.50 $ 15.90 $ - $ 15.90 $ - $ 15.90 $ 17.00 $ 17.00 $ 11.00 $ 11.00 $ 11.00 $ 11.00 $ 17.00 $ 17.00 $ 19.80 $ 19.80 $ 19.80 $ 19.80 $ 19.80 $ 19.80 $ 19.80 $ 19.80 $ 20.80 $ 20.80 $ 19.80 $ - $ 14.50 $ 19.80 $ - $ 19.80 $ - $ 19.80 $ 20.50 $ 20.50 $ 12.60 $ 12.60 $ 12.60 $ 12.60 $ 21.80 $ 21.80 $ 10.00


I felt that the entire table was oo large, but that has only confused the issue.
 
Upvote 0
The criteria used for evaluation is in a sheet called 'Payments'.
Perhaps we had better see a section of that as well, including some expected results, manually entered.
Included in those samples as well as a few straight-forward ones should be, for example, the following with explanation of how you get the result manually, provided what I have given are possible combinations.

25 April 2017, AR, Girls, U11, regular season
30 May 2015, AR, Boys, U15, regular season
12 August 2018, C, Boys, U9, regular season

Also examples from Finals, After School & Seniors
 
Upvote 0
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 1Slot 2Slot 3Slot 4Slot 5Slot 6Slot 7Slot 8 (Wed only)GameOutcomesMyChngdMyTrain
IDWDGame DateSessVenGndrPatternCrtRefExpGrAgeCrtRefExpGrAgeCrtRefExpGrAgeCrtRefExpGrAgeCrtRefExpGrAgeCrtRefExpGrAgeCrtRefExpGrAgeCrtRefExpGrAgeTypeSuper(T,U,D,R,N)TFUFRPTInjEvalRostGradeGameExpGames
133243/10/20192LWGGG42963ER9932963C9923574B99RS93NNN00000000011AR5
133328/10/20191KGGGGG13934A1113934AR1113934A1113934AR11GR155NNNN000000000000226AR5
133439/10/20192LGNGGGG13154AR1622894B1613894BR1643834B16GR93NNNNN000000000000000226AR5
1335410/10/20192LWGWG33544D9943804ER9923014BR99RS12NNN00000000011AR5
1336612/10/20191OBGGGBGG41714BR1323424C1343284B1333284B1253943BR12GR41NNNNNN000000000000000000226AR5
1337215/10/20191KGGGGG13934AR1113934AR1113934A1113934A11GR155NNNN000000000000226AR5
1338316/10/20192LGGGBGG33754CR1411475AR1623154B1642994B21GR93NNNNN000000000000000226AR5
1339417/10/20192LWGG33513ER9933723ER99SF12NN01010011AR5
1340619/10/20191OBGGGBGG32963BR1333883C1343954B1333903B1253954BR12GR41NNNNNN000000000000000000226AR5
1341222/10/20191KGGGGG63934AR1163934A1163934A1163934AR11GR155NNNN000000000000226AR5
1342323/10/20192LGGGGGG13154AR1442264C1623144B1623364B1611714B21GR93NNNNN000000000000000226AR5
1343424/10/20192KWGG33414D9933514ER99GF2NN00000011AR5
1344626/10/20191OBGGGBGG33333BR1333823C1343962B1333962B1253913BR12GR41NNNNNN000000000000000000226AR5
1345229/10/20191KGGGGG63934AR1163934A1163934A1163934AR11GR155NNNN000000000000226AR5
1346330/10/20192LGGGBGG21494B1413143A1621714B1633753B16GR93NNNNN000000100000000226AR5
1347431/10/20192LWGGG33513C9923513D9933143B99GR12NNN00000000011AR5


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!


DOWVenGenderDatePatternTotalA/GrpG/TypYearMonthGamesBench12345678SexSessRate12345678PayOutcomes
3KG11/09/2019GGNN39.00JF2019921212FARB2331212 $ 39.00NNNN
4LW12/09/2019GGG50.10SR201993999999FD C B2555999999 $ 50.10NNN
6KB14/09/2019GGG58.50JF20199391110MA B AR133391110 $ 58.50NNN
2OG17/09/2019GG39.00JF201992911FB AR133911 $ 39.00NN
3KG18/09/2019NNNNJF20199F1 $ -NNNN
4LW19/09/2019GGG50.10SR201993999999FD BRBR2555999999 $ 50.10NNN
6KB21/09/2019GGGNN58.50JF20199391012MA ARB133391012 $ 58.50NNNNN
4LW26/09/2019GGG50.10SR201993999999FERBRB2555999999 $ 50.10NNN
4LW3/10/2019GGG50.10SR2019103999999FERC B2555999999 $ 50.10NNN
2KG8/10/2019GGGG44.00JR201910411111111FA ARA AR1333311111111 $ 44.00NNNN
3LG9/10/2019NGGGG62.40JR201910416161616F ARB BRB2333316161616 $ 62.40NNNNN
4LW10/10/2019GWG50.10SR2019103999999FD ERBR2555999999 $ 50.10NNN
6OB12/10/2019GGGBGG78.00JR20191051313131212MBRC B B BR1333331313131212 $ 78.00NNNNNN
2KG15/10/2019GGGG50.00JR201910411111111FARARA A1333311111111 $ 50.00NNNN
3LG16/10/2019GGBGG63.50JR201910414161621FCRAR B B2333414161621 $ 63.50NNNNN
4LW17/10/2019GG43.00SF20191029999FERER2559999 $ 43.00NN
6OB19/10/2019GGGBGG78.00JR20191051313131212MBRC B B BR1333331313131212 $ 78.00NNNNNN
2KG22/10/2019GGGG50.00JR201910411111111FARA A AR1333311111111 $ 50.00NNNN
3LG23/10/2019GGGGG79.10JR20191051416161621FARC B B B2333341416161621 $ 79.10NNNNN
4KW24/10/2019GG43.00SF20191029999FD ER2559999 $ 43.00NN
6OB26/10/2019GGGBGGJR20191051313131212MBRC 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...


CalculatedActualMentoring
Game DateGame 1Game 2Game 3Game 4Game 5Game 6Game 7Game 8PayPayTraineesVaraiance
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 SeasonFinals
MthYearGrMinballA/SchoolGU9-GU11U9 - U16U18 - U21SeniorsHoop TimeMinballA/SchoolU9 - U16U18 - U21SeniorsHoop TimeScoring
22018AR $ 9.70 $ 9.30 $ 13.80 $ 14.90 $ 14.90 $ 10.30 $ 9.30 $ 17.40 $ 20.00 $ 20.00 $ 10.00
32018AR $ 10.00 $ 10.20 $ 14.60 $ 15.70 $ 15.70 $ 10.20 $ 10.20 $ 18.50 $ 20.80 $ 20.50 $ 10.00
42018AR $ 10.00 $ 10.20 $ 14.60 $ 15.70 $ 15.70 $ 10.20 $ 10.20 $ 18.50 $ 20.80 $ 20.50 $ 10.00
52018AR $ 10.00 $ 10.20 $ 14.60 $ 15.70 $ 15.70 $ 10.20 $ 10.20 $ 18.50 $ 20.80 $ 20.50 $ 10.00
62018AR $ 10.00 $ 10.20 $ 14.60 $ 15.70 $ 15.70 $ 10.20 $ 10.20 $ 18.50 $ 20.80 $ 20.50 $ 10.00
72018AR $ 10.00 $ 10.20 $ 14.60 $ 15.70 $ 15.70 $ 10.20 $ 10.20 $ 18.50 $ 20.80 $ 20.50 $ 10.00
82018AR $ 10.00 $ 10.20 $ 14.60 $ 15.70 $ 15.70 $ 10.20 $ 10.20 $ 18.50 $ 20.80 $ 20.50 $ 10.00
92018AR $ 10.00 $ 10.20 $ 14.60 $ 15.70 $ 15.70 $ 10.20 $ 10.20 $ 18.50 $ 20.80 $ 20.50 $ 10.00
102018AR $ 10.00 $ 10.20 $ 14.60 $ 15.70 $ 15.70 $ 10.20 $ 10.20 $ 18.50 $ 20.80 $ 20.50 $ 10.00
112018AR $ 10.00 $ 10.20 $ 14.60 $ 15.70 $ 15.70 $ 10.20 $ 10.20 $ 18.50 $ 20.80 $ 20.50 $ 10.00
122018AR $ 10.00 $ 10.20 $ 14.60 $ 15.70 $ 15.70 $ 10.20 $ 10.20 $ 18.50 $ 20.80 $ 20.50 $ 10.00
12019AR $ 9.70 $ 10.20 $ 14.60 $ 15.70 $ 15.70 $ 10.20 $ 10.20 $ 17.40 $ 20.00 $ 20.00 $ 10.00
22019AR $ 9.70 $ 10.20 $ 14.60 $ 15.70 $ 15.70 $ 10.20 $ 10.20 $ 17.40 $ 20.00 $ 20.00 $ 10.00
32019AR $ 10.00 $ 11.00 $ 15.60 $ 16.70 $ 16.70 $ 11.00 $ 12.60 $ 19.50 $ 20.50 $ 21.50 $ 10.00
42019AR $ 10.00 $ 11.00 $ 15.60 $ 16.70 $ 16.70 $ 11.00 $ 12.60 $ 19.50 $ 20.50 $ 21.50 $ 10.00
52019AR $ 10.00 $ 11.00 $ 15.60 $ 16.70 $ 16.70 $ 11.00 $ 12.60 $ 19.50 $ 20.50 $ 21.50 $ 10.00
62019AR $ 10.00 $ 11.00 $ 15.60 $ 16.70 $ 16.70 $ 11.00 $ 12.60 $ 19.50 $ 20.50 $ 21.50 $ 10.00
72019AR $ 10.00 $ 11.00 $ 15.60 $ 16.70 $ 16.70 $ 11.00 $ 12.60 $ 19.50 $ 20.50 $ 21.50 $ 10.00
82019AR $ 10.00 $ 11.00 $ 15.60 $ 16.70 $ 16.70 $ 11.00 $ 12.60 $ 19.50 $ 20.50 $ 21.50 $ 10.00
92019AR $ 10.00 $ 11.00 $ 15.60 $ 16.70 $ 16.70 $ 11.00 $ 12.60 $ 19.50 $ 20.50 $ 21.50 $ 10.00
102019AR $ 10.00 $ 11.00 $ 11.00 $ 15.60 $ 16.70 $ 16.70 $ 11.00 $ 12.60 $ 19.50 $ 20.50 $ 21.50 $ 10.00
112019AR $ 10.00 $ 11.00 $ 12.50 $ 15.60 $ 16.70 $ 16.70 $ 11.00 $ 12.60 $ 19.50 $ 20.50 $ 21.50 $ 10.00
122019AR $ 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.
 
Upvote 0
I'm afraid it looks too complex for a free public forum like this but perhaps somebody might take up the challenge.
 
Upvote 0
I expected as much. Thanks anyway.

I doubt anyone will take up the challenge. I have other posts that still have no replies on similar topics.

I wrote some date handling code in Cold Fusion and SQL over a decade ago. I doubt I have it lying around, but I might get lucky. Sadly, I think I'm going to have to migrate this to a proper DB app. <sigh>
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,044
Members
448,543
Latest member
MartinLarkin

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
Back
Top