I'm trying to convert baseball stats into a 30-99 rating for a game I play. 30-99 because the in game rating system bottoms out there instead of using the entire 1-99 range.
- Sample2.jpg (89 KiB) Viewed 1009 times
So for example column X has the hits allowed per 9 innings stats (H/9) for this player for this player over the last 4 years. I want to turn those stats into a 30-99 number and have that number automatically calculated into column Y. With this column a 99 rating would equal 5.0 H/9 and a 30 rating would equal 12.0 H/9. Lower is better in this case. Obviously my statistical range would be different for column Z, column AB, and column AD. The ratings will usually all be 30-99 but if a few instances it will top out at 95 instead of 99.
One final hiccup is I want to regress the rating based on the level of play (MLB, AAA, AA, A, RK). I have this info entered into a column not shown in the image (column C). Basically MLB would be 100% where 5.0 H/9 would equal 99 for example. AAA would be 75% (5.0=75), AA would be 70%, A would be 65%, and RK would be 60%.
Fun with spreadsheets!