Does anyone have a spreadsheet or formula for estimating OPS?

[captainbuttercream]

I think I have an OK idea just looking at players ratings what their OPS could be but I was wo dering if anyone was willing to share a spreadsheet they've made for estimating?

I'd like to be able to get a general idea before dumping a bunch of points on a player, and it would be a good way to test my own judgment.


EDIT: This somehow got posted twice.

[DonkeyKongSr]

Here's the formulas straight from my spreadsheet. You'll have to convert to columns for your spreadsheet. And the rows of course...if you drop this on your spreadsheet on row 3 and initially place the ratings columns in AK to AN, you don't have to worry about changing the formulas, then can add and subtract columns while Excel converts the references for you.

OBP:
=((IF(AK3>50,1.089*(AK3*2-100)/2+146,11.08*(AK3/5)+35))+(IF(AN3>50,(AN3-1)*2-50,AN3))+1)/(550+(IF(AN3>50,(AN3-1)*2-50,AN3))+1)

SLG:
=((IF(AK3>50,1.089*(AK3*2-100)/2+146,11.08*(AK3/5)+35))+(0.95*(((AL3)*2-125)/25*7+34.5))+(0.05*(((AL3)*2-125)/25*7+34.5))*2+(IF(AM3>51,(AM3*2-125)/25*8.5+24.5,2*AM3*0.15818182))*3)/550


AK = CON
AL = GAP
AM = POW
AN = EYE

Credit goes to whoever I stole it from on Reddit who stole it from someone on here.

[chazzycat]

OPS is a pretty bad stat though. How about an wRC+ formula

[zrog2000]

Originally Posted by DonkeyKongSr

Here's the formulas straight from my spreadsheet. You'll have to convert to columns for your spreadsheet. And the rows of course...if you drop this on your spreadsheet on row 3 and initially place the ratings columns in AK to AN, you don't have to worry about changing the formulas, then can add and subtract columns while Excel converts the references for you.

OBP:
=((IF(AK3>50,1.089*(AK3*2-100)/2+146,11.08*(AK3/5)+35))+(IF(AN3>50,(AN3-1)*2-50,AN3))+1)/(550+(IF(AN3>50,(AN3-1)*2-50,AN3))+1)

SLG:
=((IF(AK3>50,1.089*(AK3*2-100)/2+146,11.08*(AK3/5)+35))+(0.95*(((AL3)*2-125)/25*7+34.5))+(0.05*(((AL3)*2-125)/25*7+34.5))*2+(IF(AM3>51,(AM3*2-125)/25*8.5+24.5,2*AM3*0.15818182))*3)/550


AK = CON
AL = GAP
AM = POW
AN = EYE

Credit goes to whoever I stole it from on Reddit who stole it from someone on here.

Interesting that Avoid K isn't present in the OBP formula.

[chazzycat]

avoid k has no effect on offensive output as far as most stats are concerned, which don't count things like moving runners over, or getting on base from a fielding error.

[RchW]

LOL what is the correlation of estimated OPS to actual performance?

[zrog2000]

Originally Posted by RchW

LOL what is the correlation of estimated OPS to actual performance?

I'm taking it as relative to each other, not actual estimated anything. Or maybe you could subtract 250-300 points for perfect leagues.

[Dogberry99]

Originally Posted by chazzycat

avoid k has no effect on offensive output as far as most stats are concerned, which don't count things like moving runners over, or getting on base from a fielding error.

As I understand it, avoid k is one of several ratings used to construct the contact rating. As such, it does have an effect, but it is baked into the contact rating, so including avoid k's would potentially overrepresent its effect.

[joehart]

While the actual OPS prediction is way too high and not accurate, it gives a general sense of who will be a good player (confirming the simple eye test).

However, once you apply actual results, ballpark adjustment, platoons... you can further adjust the top list for all the positions.

I assume this is what the initial poster was looking for....matching numbers with his eye test. In this regard, it works well enough.

[chazzycat]

Originally Posted by Dogberry99

As I understand it, avoid k is one of several ratings used to construct the contact rating. As such, it does have an effect, but it is baked into the contact rating, so including avoid k's would potentially overrepresent its effect.

That may be true, because contact rating certainly does matter. But I can tell you that I looked at the data from half a million ABs sample size and Avoid K had zero correlation with OPS, wOBA, wRC+ or anything else I could think of.

[DonkeyKongSr]

Originally Posted by RchW

LOL what is the correlation of estimated OPS to actual performance?

Not totally uncorrelated. Below are all the batters in my 3 leagues (Perfect + 2 Diamonds). Like cards have been combined with stats recalculated for the new combined player.

The real question is whether OBP/OPS correlate with a winning team.

[DonkeyKongSr]

And while we are at it, here's some very strong correlations...

[chazzycat]

yeah strikeouts vs Avoid K I think has the highest correlation of anything, IIRC.

makes 100% perfect sense considering that strikeouts are the first statistic to "stabilize".

https://library.fangraphs.com/principles/sample-size/

these guys know their baseball.

[DonkeyKongSr]

And another one. The game's ratings are pretty much indicative of what they say. Contact is tougher to see any correlations, because as said above, it involves way more factors.

[DonkeyKongSr]

While I'm at it, here's charts I did for pitchers last season in my Perfect League. This is individual players, not combined like cards. As you can see, all 3 ratings have a correlation to what you'd expect:

[pgjocki]

I ran this against my team currently in Gold and everyone of my guys actual performance numbers is under this total by 30 to 75 points. Interestingly enough my Trout card has a lifetime OPS of .416 and this calculation was .456 but Trout was my highest card and has the highest OPS over the last 4 seasons.

Now, I don't know if I would build a team around this yet but I will definitely use it to choose between spending 5,000 PP or 20,000 PP for my next upgrade.

Edit: I'm dense and was looking at OBP and not OPS which the numbers are then WAY off.

[2002halos]

Originally Posted by zrog2000

I'm taking it as relative to each other, not actual estimated anything. Or maybe you could subtract 250-300 points for perfect leagues.

Yeah, these formulas will just get you expected numbers in average leagues, probably not taken from Perfect Team play, so good for relative comparisons but not much more.

To really predict/estimate, you need to include the expected environment in the formula, by which I mean the expected STU, MOV and CON of the average pitcher faced (which obviously increases dramatically at higher levels), as well as the park factors, and probably average defense faced. This would be more useful as it would help set lineups for a full season or a postseason series. And it could be used in reverse (pitcher ratings vs environment average hitter ratings) to make pitching rotation and bullpen decisions.

I'm working on such a formula, but have a long ways to go, as the data prep is slow (determining average environment faced by each team takes a while).

[Echo127]

Originally Posted by chazzycat

avoid k has no effect on offensive output as far as most stats are concerned, which don't count things like moving runners over, or getting on base from a fielding error.

I thought this for a long time, but just this weekend I set up a "test" league to investigate stuff like that, and it turns out that Avoid K *does* have an impact on offensive output beyond raising the value of the Contact rating and and converting strikeouts to ground/fly outs.

I made 2 teams in which every player on each team was identical, then tweaked 1 player to have a higher Avoid K but a lower BABIP (so that his resulting contact rating was identical to the other players). I then ran a handful of 10,000 game seasons via the simulation module and found that the high Avoid K player was consistently the best player in the league. I don't have the #'s in front of me, but IIRC he typically had an OPS 20pts higher than the rest of the league.

My theory is that every time the higher Avoid K stat turns a strike 3 into a foul ball (rather than a ball in play) that player has an extra chance at a hit that he wouldnt have gotten if his contact rating was BABIP-driven rather than Avoid K-driven.

[zrog2000]

Originally Posted by Echo127

I thought this for a long time, but just this weekend I set up a "test" league to investigate stuff like that, and it turns out that Avoid K *does* have an impact on offensive output beyond raising the value of the Contact rating and and converting strikeouts to ground/fly outs.

I made 2 teams in which every player on each team was identical, then tweaked 1 player to have a higher Avoid K but a lower BABIP (so that his resulting contact rating was identical to the other players). I then ran a handful of 10,000 game seasons via the simulation module and found that the high Avoid K player was consistently the best player in the league. I don't have the #'s in front of me, but IIRC he typically had an OPS 20pts higher than the rest of the league.

My theory is that every time the higher Avoid K stat turns a strike 3 into a foul ball (rather than a ball in play) that player has an extra chance at a hit that he wouldnt have gotten if his contact rating was BABIP-driven rather than Avoid K-driven.

I'm also inclined to believe that Avoid K is more than just an almost meaningless rating that is already baked into the Contact rating. Thanks for doing the work on that.

[chazzycat]

Originally Posted by Echo127

I thought this for a long time, but just this weekend I set up a "test" league to investigate stuff like that, and it turns out that Avoid K *does* have an impact on offensive output beyond raising the value of the Contact rating and and converting strikeouts to ground/fly outs.

I made 2 teams in which every player on each team was identical, then tweaked 1 player to have a higher Avoid K but a lower BABIP (so that his resulting contact rating was identical to the other players). I then ran a handful of 10,000 game seasons via the simulation module and found that the high Avoid K player was consistently the best player in the league. I don't have the #'s in front of me, but IIRC he typically had an OPS 20pts higher than the rest of the league.

My theory is that every time the higher Avoid K stat turns a strike 3 into a foul ball (rather than a ball in play) that player has an extra chance at a hit that he wouldnt have gotten if his contact rating was BABIP-driven rather than Avoid K-driven.

Very interesting! I'm not quite sure how to explain the data I saw in that case. Will have to keep looking into it.


BTW how do you access that "simulation module"?