DoG's Top 100 Test Bowlers Countdown Thread 100-1

[Migara]

My next problem is trying to find and replace all the 4s with 9s in the batting list.

Unfortunately, excel won't let me do this. Whenever I try to find and replace 300*4)>4,4

with 300*9)>9,9

It comes back with 'There is a problem with this formula'

I'm not trying to type a formula, I'm trying to replace a number in a formula. It's very frustrating. Why won't it let me do this?

Change it in one place and then copy and paste.

If you know bit of VBA, then you can name the ranges with formula and can run a VB Script to make the changes.

 

[Days of Grace]

No. It would take too long. I only want to find and replace one number with another across an entire workbook. Should take 5 seconds but Excel is not letting me do it for some reason.

EDIT: I think I solved it.

I'm changing the batsmen average:strike rate ratio from 4:1 to 9:1 in keeping with our discussions on strike-rate being more important for bowlers than batsmen in test cricket.

 

[Bolo]

I'm missing something about how the factors are weighted. Barnes has better stats and a higher PPI for his peak than for non home. The peak bonus is double the away bonus, so i figure he should end up with more than double the points. He gets far less than double though. Why is this?

 

[stephen]

I'm missing something about how the factors are weighted. Barnes has better stats and a higher PPI for his peak than for non home. The peak bonus is double the away bonus, so i figure he should end up with more than double the points. He gets far less than double though. Why is this?

His peak wasn't long enough I imagine.

 

[Victor Ian]

My next problem is trying to find and replace all the 4s with 9s in the batting list.

Unfortunately, excel won't let me do this. Whenever I try to find and replace 300*4)>4,4

with 300*9)>9,9

It comes back with 'There is a problem with this formula'

I'm not trying to type a formula, I'm trying to replace a number in a formula. It's very frustrating. Why won't it let me do this?

I imagine you are working with variables all over the place. It can be worthwhile setting up a list of variables and then pointing to them instead. You can also name variables to make it easier to follow and refer to.

eg Cell A1 = 21 --> From Menu Insert|Name|Define (call it 'something')
Cell A2 formula is = something
Cell A2 value is 21

in your variables table, use a column to record the name you have assigned. Then you can keep that open to the side of your workspace for easy referencing rather than having to look into the cell to see what name you have given it all the time

then you could have 300*9>ratioaveragestrikerate,9 or 300*ratioaveragestrikerate)>ratioaveragestrikerate,ratioaveragestrikerate, etc


This will give you full freedom once its done to go nuts and figure out the numbers you need to get Allan Border to the top!

 

[Migara]

No. It would take too long. I only want to find and replace one number with another across an entire workbook. Should take 5 seconds but Excel is not letting me do it for some reason.

EDIT: I think I solved it.

I'm changing the batsmen average:strike rate ratio from 4:1 to 9:1 in keeping with our discussions on strike-rate being more important for bowlers than batsmen in test cricket.

Easiest way is to type in the constants in the sheet and do a static reference to them. When you want to change the number, merely changin the constant will do.

 

[Days of Grace]

Ok. Do you guys agree that batsmen should have a 9:1 ratio for average:strike-rate?

 

[Starfighter]

It'll have to do.

 

[smash84]

Ok. Do you guys agree that batsmen should have a 9:1 ratio for average:strike-rate?

Sounds reasonable. Although in test matches i wouldn't mind even if it was 19:1

 

[ankitj]

Ok. Do you guys agree that batsmen should have a 9:1 ratio for average:strike-rate?

Sounds OK but when you look at Sehwag or Gilchrist, you'd think they need some bonus points for being attacking batsmen who thrashed opposition out of game in 1-2 sessions.

 

[stephen]

Sounds OK but when you look at Sehwag or Gilchrist, you'd think they need some bonus points for being attacking batsmen who thrashed opposition out of game in 1-2 sessions.

Yeah and there needs to be a way of differentiating Kallis from Ponting.

 

[Days of Grace]

Sounds OK but when you look at Sehwag or Gilchrist, you'd think they need some bonus points for being attacking batsmen who thrashed opposition out of game in 1-2 sessions.

I’m taking strike-rate into account so that really is the bonus.

90/10 for batsmen
80/20 for bowlers

 

[jaideep]

Ok. Do you guys agree that batsmen should have a 9:1 ratio for average:strike-rate?

Are you considering the strike rate for innings of 50+ runs or career strike rate?

 

[Zinzan]

Thread of the year this.

Superb effort Doggie!

 

[akilana]

Great effort DoG. Glad Steyn and Hadlee ended up ahead of Mcgrath.

 

[Days of Grace]

Are you considering the strike rate for innings of 50+ runs or career strike rate?

Career and every innings.

 

[Bolo]

His peak wasn't long enough I imagine.

I don't think it was this. His peak was closer to 50 than his away career. It's not specific to Barnes anyway, he's just a clear example.

 

[Days of Grace]

I don't think it was this. His peak was closer to 50 than his away career. It's not specific to Barnes anyway, he's just a clear example.

Barnes bowled in exactly 50 innings. So his peak was his career. Other bowlers with longer careers had a change to have better peak figures than Barnes.

 

[Bolo]

Barnes bowled in exactly 50 innings. So his peak was his career. Other bowlers with longer careers had a change to have better peak figures than Barnes.

Sure, explains why he doesn't have the best peak. I'm asking about the relationship between peak and away bonuses though. He ecored 184 in peak and 107 in away. With his peak being better than away statistically and in PPI, and peak counting for double, I would expect his away to score somewhere below 92. What causes the adjustment?

 

[Days of Grace]

Sure, explains why he doesn't have the best peak. I'm asking about the relationship between peak and away bonuses though. He ecored 184 in peak and 107 in away. With his peak being better than away statistically and in PPI, and peak counting for double, I would expect his away to score somewhere below 92. What causes the adjustment?

I take the average of all the stats for each criteria across the top 100. So a bowler's stats for each criteria is weighted (compared) against the stats of other bowlers in the top 100.

The averages for the peak criteria are 22.50 52.50 5.50. If you have these peak stats exactly then you will get 150 points (20% of 750).

If a bowler has the exact same stats as the average across the top 100 bowlers for all the criteria then they would get 750 points.