# MLB FORUM

• Codith

Hey Guys, I need help creating a formula for a DFS Sheet.

If anyone can help I would greatly appreciate it. I will give you an example of what I need.

lets say the sheet is set up like this

A2|
Players name

B2|
A stat category…Lets use Pitchers Woba in this scenario.

In B2 we have a vertical list of all the players Pitchers Woba numbers.

Top score is .137 | An elite marker I have dubbed to be .227 | there is an average of .284 | and the worst is .377

I need to formula to create a score from 1-3. 3 being the best outcome.

So if the player has a score from .137 to .227, they would score a 3
If the player has a score from .228-.284, they would score a 2
If the player has a score worse than .285, they would score a 1

So if play X has a P.Woba of .169….the score outcome should be a 3.

If you want, you can send me a PM, I just need a quick fix and I can’t seem to get this down.

Cheers guys

• meerkatmreow

An if, else if with < & > should do the trick

• crazypaul

@meerkatmreow said...

An if, else if with < & > should do the trick

You can do this with if, else or you can make another sheet called scales and build the scales there. Then do a vlookup to search the woba and retun a value from your scale. Thats how I do it, but only because I “rate” 20 categories(10 hitting, 10 pitching matchup) and use a custom formula to return an overall rating of 1-100 for a player on that day.

If you are only looking to grade woba, if, else would be much easier.

• Codith

Hey Paul,

I am looking to grade 20 hitting categories and about 10 pitching categories. All with the same idea in mind.

• crazypaul

@Codith said...

Hey Paul,

I am looking to grade 20 hitting categories and about 10 pitching categories. All with the same idea in mind.

Ok, here is what I did:

Create a sheet and call it whatever (scales, ratings ect…) Then in column A List your First stat with the scale you want (example: A1 Pitcher wOBA, a2 .000, a3 .228, a4 .285. Then B1 Scale, b2 3, b3 2, b4 1.)

Then on my main sheet, after all of my stats, i build the calculator and hide the columns afterwards. Example: Column M2 has pitcher woba of .245, so on column AM2 I use vlookup(M2, scales!A:B, 2, false) and it returns a number 1-3 based on the wOBA for that pitcher. Hope that makes sense.

It takes some time, and maybe there is a more simple way to do this, but it works great for me. I hope this helps

• Jvanspro

You’re life will be much easier if you use If and else if. I would highly recommend creating a table instead of a list. If you just use a list you will have a mess on your hands.

• Jvanspro

Or, I you want to make your life really simple and create a visual effect. Just create a conditional formatting rule.

• gje627

If you plan to this in Excel, I think a combination of the “IF” function with the “IF/AND” function is easiest. While VBA is probably best for complicated formulas, I personally would use Excel’s functions capabilities for a problem as small as yours. I actually figured out what you need in just a couple of minutes, but so as not confuse the issue (see floating point comments below) I posted the answer but then deleted it because it appears you found what you needed elsewhere.

For your boundaries, make sure you have a reasonably good grasp of how Excel handles floating point arithmetic. If you’re a programmer, you probably already understand that floating point numbers are the bane of all programmers. If you need a refresher, here’s what Microsoft has to say about floating points in Excel:

https://support.microsoft.com/en-us/help/78113/floating-point-arithmetic-may-give-inaccurate-results-in-excel

Important: Notice the title of Microsoft’s article states that floating point arithmetic may give “inaccurate results in Excel.”

• OUSooners22

Well I was looking for help kinda along the same lines as this but just reading this confused me even more, blows my mind a guy can play baseball all the way threw college and then not understand what the heck all these stats mean in mlb… Never played basketball in my life but the NBA stuff is easy… Not to mention everyone says use fan graph, I have been trying for 2 days to export pitcher stuff and half the time elite players like Kershaw won’t even be in the stats