• Welcome to the Cricket Web forums, one of the biggest forums in the world dedicated to cricket.

    You are currently viewing our boards as a guest which gives you limited access to view most discussions and access our other features. By joining our free community you will have access to post topics, respond to polls, upload content and access many other special features. Registration is fast, simple and absolutely free so please, join the Cricket Web community today!

    If you have any problems with the registration process or your account login, please contact us.

2022-2023 New Zealand First-Class Draft

HeathDavisSpeed

Hall of Fame Member
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.
 

Chewie

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

HeathDavisSpeed

Hall of Fame Member
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.
 

Kippax

Cricketer Of The Year

 

HeathDavisSpeed

Hall of Fame Member
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

Hall of Fame Member
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?
 

HeathDavisSpeed

Hall of Fame Member
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.
 
Last edited:

HeathDavisSpeed

Hall of Fame Member
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

Cricketer Of The Year
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:

 

HeathDavisSpeed

Hall of Fame Member
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

Hall of Fame Member
@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

Hall of Fame Member
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.
 

Kippax

Cricketer Of The Year
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.
 

Top