Novice seeking a more efficient way to assign +/- values based on MLB stats spectrum.

DireFantasy

New Member
Joined
Mar 23, 2018
Messages
2
Hello, thank you very much in advance for your time and charity.

In an effort to quantify the best pitching/hitting options for daily fantasy baseball, I'm building a formula/model with 30 factors.

For example, the first factor is ground ball percentage (GB%). Based on a low-point of 28.7, average of 43.7, and high-point of 58.1, I wrote this lengthy IF statement:

=if(I6=0,0, if(I6<34, -10, if(I6<34.42,-9.5, if(I6<34.84,-9, if(I6<35.26,-8.5, if(I6<35.68,-8, if(I6<36.2,-7.5, if(I6<36.62,-7, if(I6<37.04,-6.5, if(I6<37.46,-6, if(I6<37.88,-5.5, if(I6<38.3,-5, if(I6<38.72,-4.5, if(I6<39.14,-4, if(I6<39.56,-3.5, if(I6<39.98,-3, if(I6<40.4,-2.5, if(I6<40.82,-2, if(I6<41.24,-1.5, if(I6<41.66,-1, if(I6<42.08,-0.5, if(I6<42.4,-0.25,if(I6>58,10,if(I6>57.22,9.5,if(I6>56.44,9,if(I6>55.66,8.5,if(I6>54.88,8,if(I6>54.1,7.5,if(I6>53.32,7,if(I6>52.54,6.5,if(I6>51.76,6,if(I6>50.98,5.5,if(I6>50.2,5,if(I6>49.52,4.5,if(I6>48.74,4,if(I6>47.96,3.5,if(I6>47.18,3,if(I6>46.4,2.5,if(I6>45.62,2,if(I6>44.84,1.5,if(I6>44.06,1,if(I6>43.28,0.5,if(I6>42.4,0.25,0)))))))))))))))))))))))))))))))))))))))))))


This is GB% for 5 out of a potential 30 pitchers/offensive teams for the day. Pitcher/team 1 GB% is in cell I2. My idea is to increase/decrease the output by 5% while the input moves 5% further/closer to the average.
Many days will have few than 30 pitching/hitting options,hence =if(I6=0,0. Im planning on copying, pasting, and sorting the final results.

Like I said, I have 29 others factors (strikeout percentage, precipitation chances, etc.), with different averages, low points, and high points. I'm planning on applying each factor to an overall offensive quantification cell (negative number being beneficial in above IF statement) and pitching quantification (positive number being beneficial in above IF statement). I also plan on using the MAX function in both final quantifications to avoid reducing points in an unfavorable situation, only awarding points for favorable situations. I'm also going to weight all factors differently, based on perceived importance.

So, with great hope this makes sense, my question is: is there a more efficient way to do this? I've read a bit about data buckets and some others things, but truly don't know what I'm doing. Any advice/guidance at all would be greatly appreciated.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,620
Latest member
dsubash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top