2022-2023 New Zealand First-Class Draft

[Kippax]

One other wrinkle could be a duck copping a 10-point penalty.

 

[HeathDavisSpeed]

Oh thanks very much for this. Yes, Popli did earn 25 blunter points in the first round.

Hmmm. Yes - that's much harder to deal with straight from the scorecard, given the nature of how fall of wickets is recorded on there. Probably has to realistically be a manual adjustment for that element, and a few others.

 

[HeathDavisSpeed]

One other wrinkle could be a duck copping a 10-point penalty.

Nah. That's easy. Already in there. Just need to adjust a little to ensure a not out 0 isn't penalised.

 

[Chewie]

Did sneddon get taken? Will pick him for my backup starting from next round if no one has taken. Otherwise davey

 

[HeathDavisSpeed]

Oh thanks very much for this. Yes, Popli did earn 25 blunter points in the first round.

Nearly done with the base line stuff. Just need to fix a divide by zero issue and the run outs and the thing will automate the baseline scoring and you'll just need to manually do the blunter bonus and the Kiwi spinners condescension bonus (though the condescension bonus could probably be easily automated with a table of players and bowling type - just would need to be updated when new players start as this data is not directly on the scorecard).

There's one absolutely horrific stacked 'if' formula in there. Always entertaining.

 

[vandem]

I will be off line for a few weeks. Motorbike accident.

 

[honestbharani]

I will be off line for a few weeks. Motorbike accident.

Wow. Take care man. Speedy recovery ?

 

[Kippax]

2022/23 New Zealand First-Class Draft

docs.google.com

 

[HeathDavisSpeed]

Nearly done with the base line stuff. Just need to fix a divide by zero issue and the run outs and the thing will automate the baseline scoring and you'll just need to manually do the blunter bonus and the Kiwi spinners condescension bonus (though the condescension bonus could probably be easily automated with a table of players and bowling type - just would need to be updated when new players start as this data is not directly on the scorecard).

There's one absolutely horrific stacked 'if' formula in there. Always entertaining.

Bowling now works. I've limited the number of bowlers to 7 in any given innings. I can add more if necessary, but most games aren't going to use 8 or more bowlers.... most....

 

[HeathDavisSpeed]

With run outs such as

JM Gibson run out (Harrison/Horne)

Who do you want to score the run out points? Is this the case where you want them to score 5 points each?

 

[Kippax]

Yes five points each.

 

[HeathDavisSpeed]

Ha. Bugger. Didn't think of c&b or the pain the arse of having multiple O'Donnells and K Clarkes.

 

[HeathDavisSpeed]

I think it's done. There's bound to be something I've missed, but as it is, you can copy and paste the batting card into columns A-D from row 1 and the bowling card into columns A-D from row 80 onwards and the section in M2-AA24 will calculate the batting, fielding and bowling points excluding the blunter's bonus and the spin bowlers' maiden overs.

EDIT: Make a copy of the spreadsheet first and have a play, see if it works for you.

EDIT 2: Reckon there might be the Clarke/O'Donnell problem with some other players too - namely Carters, Walkers, Hays, Bracewells, Phillips. Ugh. I'll have to think of a different way to work round this. My current solution to Clarke/O'Donnell won't really be fit for purpose for every potential combo. Though it only seems to be a problem when they have two with the same surname in the same match. It'll happen at some point and ruin everyone's day.

 

[HeathDavisSpeed]

EDIT 2: Reckon there might be the Clarke/O'Donnell problem with some other players too - namely Carters, Walkers, Hays, Bracewells, Phillips. Ugh. I'll have to think of a different way to work round this. My current solution to Clarke/O'Donnell won't really be fit for purpose for every potential combo. Though it only seems to be a problem when they have two with the same surname in the same match. It'll happen at some point and ruin everyone's day.

Think I've solved this now. Changed the logic a tad, should now work.

 

[Kippax]

Okay, so when I paste in the most recent Wellington game, can we get it adjusting for McPeake batting as a nightwatchman at No. 4 in the second innings? At the moment it's given his 25 runs to the first innings No. 4, Kelly.

Copied muckaround sheet:

Copy of 2022/23 New Zealand First-Class Draft

docs.google.com

 

[HeathDavisSpeed]

Haha. Bloody cricket with its idiosyncratic ways. Yep, I can fix that up. That'll teach me for taking a shortcut! It'll also be getting everyone below McPeake wrong in that innings. Bhula definitely punching above his weight with 23 points!

EDIT: That's a bit of a bugger actually. Might be a fundamental change to the logic in the spreadsheet, but definitely fixable. I'll probably be able to fix that up on Wednesday.

 

[HeathDavisSpeed]

@Kippax

Actually, I had a brainwave and have fixed it already in the worksheet I was working in.

The formula in the batting columns should now be of this ilk as opposed the simple one that was in there before if you have any doubt over version control.

=if(index($B$1:$B$78,match(M15,$B$1:$B$78,0)+2,1)=0,if((index($B$1:$B$78,match(M15,$B$1:$B$78,0)+1,1))="not out",0,-10),(index($B$1:$B$78,match(M15,$B$1:$B$78,0)+2,1)))

You're bound to find other teething issues. Just let me know when you do.

 

[HeathDavisSpeed]

There's probably a similar issue in that Davey is being over-rewarded for taking McPeake's wicket in the 2nd innings. The irony being McPeake was Wellington's top scorer, but still.

 

[Flem274*]

Not sorry.

likewise.

 

[Kippax]

Well the guy who was nice enough to try to keep this thing going for you two for another season has had a moderately serious road accident. But yes, parasites just tend to suck harder, in adverse circumstances.