# FORMULA HELP

#### WTCAMB

##### Board Regular
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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### just_jon

##### Legend
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

#### WTCAMB

##### Board Regular
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.

#### just_jon

##### Legend
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. ]

#### WTCAMB

##### Board Regular
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.

#### just_jon

##### Legend
Book1.xls
ABCDEFG
1TeamsSpreadPickScoreCoverCorrect
2Team1Boise9.570
3Team2Fresno-9.5-20.32711
4Team1USC-14.5-6.52801
5Team2UCLA14.5271
6
Sheet1

UNTESTED above.

Replies
3
Views
145
Replies
41
Views
664
Replies
1
Views
392
Replies
1
Views
325
Replies
5
Views
155

Threads
1,195,934
Messages
6,012,386
Members
441,694
Latest member
Elvin A

### 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

### 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