FORMULA HELP

WTCAMB

Board Regular
Joined
Apr 17, 2003
Messages
51
Boise St.
Fresno St. 20.3

Fresno St. favored -9

Final Score
Boise St. 7
Fresno St. 27


I have a spreadsheet that picks football games. In this case, it has picked Fresno St. to win by 20.3. They were favored by 9.5. So, I am saying they will cover the spread. The final score shows they beat the spread because they won the game by 20. I need a formula to show me when I pick correctly or when I don't. I pick all of the games, so I need to know my winning percentage. In other words, I just need a win column and a loss column with a 1 in it if I won and a 1 in it if I don't cover the spread.
It would look like this in the above game. I would want it to show each games results down a column. I hope I explained it o.k.

W L
1 0
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Could do something like
Book4
ABCDEFG
1TeamsSpreadPickScoreCover SpreadCorrect Pick
2Team1Boise St9.570 
3Team2Fresno St-9.5-20.32711
4Team1USC-14.5-6.52800
5Team2UCLA14.5271 
6
Sheet1
 
Upvote 0
Wouldn't the USC pick be a correct pick becauseit was not supposed to beat the spread thus saying take UCLA + 14.5?

How would you correct the formula to make it a correct pick?

Thanks

I'm from Anniston.
 
Upvote 0
WTCAMB said:
Wouldn't the USC pick be a correct pick becauseit was not supposed to beat the spread thus saying take UCLA + 14.5?

How would you correct the formula to make it a correct pick?

Thanks

I'm from Anniston.

No, the way I set it out:

column F: USC favored by 14-1/4, won by 1 so they did not cover==0, nd the reverse for UCLA

column G: USC was chosen as a 6-1/2 point winner, but won by only 1; so while the correct winner was picked, at that spread the bet would have been lost.

Do you just want to know if the winner picked, regardless of the chosen spread?

[ a bit north of you. ]
 
Upvote 0
By the way, thanks for your help.

What I want to know is how many of my picks beat the spread.

In your example USC won the game by one point. The pick said they would win by 6.5 points, but since the spread had them a 14.5 point favorite, they didn't cover winning by one. Therefore, the 6.5 pick said they would not cover, making it a correct pick by taking UCLA + 14.5.

In your spreadsheet, you are showing it an incorrect pick with a 0.

I want a formula to cover all circumstances of each game, whether the pick covers or not.

I usually pick all the 1-a games.

I hope this makes sense to you.

Thanks again for your help.
 
Upvote 0
Book1.xls
ABCDEFG
1TeamsSpreadPickScoreCoverCorrect
2Team1Boise9.570 
3Team2Fresno-9.5-20.32711
4Team1USC-14.5-6.52801
5Team2UCLA14.5271 
6
Sheet1


UNTESTED above.
 
Upvote 0

Forum statistics

Threads
1,224,427
Messages
6,178,587
Members
452,860
Latest member
jroberts02

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