I've been trying to solve this problem for years, and may have even asked here a couple of years ago. But for most of this year, the world has been on pause so I've not needed to worry about it. But, basketball will hopefully be starting up again next year and it's time to solve it because how they pay officials has gone digital.
The pay rates are increased first of march each year. They can also be changed at any time, and if an official is upgraded the rates also increase. The rates depend on whether the gme being officiated is a regular season game or a finals game. There are also several competition types: Junior Domestic, Senior Domestic, After School Domestic, Miniball Domestic and then finals rates for each of those competitions.
Rates for U9 boys are different for U9 girls because the U9 girls is classed as a different competition type.
As if this wasn't bad enough, the rate is different for the last 3 games on a Saturday, and all games on a Sunday. (+$4 and +$1 respectively).
The only solution I've come up with so far is a massive table for every day of the year for the last 10 years and for the next 10 years (Hopefully I will still be officiating in 10 years). That's huge!
My preference is to use an effective date and match on age, day, gender, competition type and whether regular season or finals.
I've looked around for commercial and open source software but haven't come across anything. I also have 10 years of game data already recorded in Excel that could potentially be a huge task getting into some other program.
I'm assuming this is going to be a [H|V]Lookup but I'm damned if I can figure out the correct formula.
If the game date is > last pay rate row, use the last row.
If it's less than the date of the last row, I need to find the row between two dates that this game date sits between.
If the game date is < the first pay rate row - throw an error
Can any one help me figure this out?
thanks
The pay rates are increased first of march each year. They can also be changed at any time, and if an official is upgraded the rates also increase. The rates depend on whether the gme being officiated is a regular season game or a finals game. There are also several competition types: Junior Domestic, Senior Domestic, After School Domestic, Miniball Domestic and then finals rates for each of those competitions.
Rates for U9 boys are different for U9 girls because the U9 girls is classed as a different competition type.
As if this wasn't bad enough, the rate is different for the last 3 games on a Saturday, and all games on a Sunday. (+$4 and +$1 respectively).
The only solution I've come up with so far is a massive table for every day of the year for the last 10 years and for the next 10 years (Hopefully I will still be officiating in 10 years). That's huge!
My preference is to use an effective date and match on age, day, gender, competition type and whether regular season or finals.
I've looked around for commercial and open source software but haven't come across anything. I also have 10 years of game data already recorded in Excel that could potentially be a huge task getting into some other program.
I'm assuming this is going to be a [H|V]Lookup but I'm damned if I can figure out the correct formula.
If the game date is > last pay rate row, use the last row.
If it's less than the date of the last row, I need to find the row between two dates that this game date sits between.
If the game date is < the first pay rate row - throw an error
Can any one help me figure this out?
thanks