# IF, AND, OR - or do you have a better suggestion?

#### MrBurn5

##### New Member
I asked this one a few months ago but I think it was to obscure of a question without visual reference.

In a nut shell I run a handicapping site that focuses on Pro and College football and basketball. I've been about 66% accurate against the spread the past two seasons, but I'm always trying new formulas to improve the accuracy - which is also the most time consuming part of it.

All the stats are imported through macros, manipulated in another worksheet and then spit out the scores and margin in to Columns D and C. The Vegas lines (A and B) as well as the final scores (F) are manually inputted until I find a site I can properly scrape data from.

All of that is the easy part. Where I desperately need your help, is automating the 1's that are in Column H, and here is the logic behind what those are for.

In order to hone in on games with a higher likelihood of a winning bet, I look for games like this where the value in A7 or A8 is more than A2 points different than the value in C7 or C8. In this example, I'm looking for games where there is more than a 7 point difference. I currently have to manually look through ~200 games per week and do the math in my head, then put the 1 in H7. If I am correct, then I put the 1 in H8. That is then all summed somewhere else on the sheet to spit out an accuracy percentage for that week's games.

I've tried multiple formulas to get me those 1's in H7 and H8 to no avail, and I'm not sure if it has to do with the negative values in A/B, or the null values (in this example; A8, B8, and C7) confusing the math and giving a false positive.

[/IMG]

Here is the current iteration that still is giving a "0".

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #0057d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}</style>=IF(OR(AND(-A7+A2<c7,c8=0),and(-a7-a2>C7,C8=0),AND(A7+A2<c8,c7=0),and(-a8+a2<c8,c7=0),and(-a8-a2>C8,C7=0),AND(A8+A2<c7,c8=0)),1,0)

I'll break that down so it's easier to follow: (Away team is on top, Home team is on bottom)

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #0057d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}</style>
=IF(OR(
AND(-A7+A2<c7,c8=0), <--="" if="" away="" team="" is="" favored="" (by="" -6.5="" in="" image),="" and="" my="" prediction="" greater="" than="" 6.5="" +="" 7
AND(-A7-A2>C7,C8=0), <-- If away team is favored by a lot and I have them narrowly winning, (say -10 - 7 > C7)
AND(A7+A2<c8,c7=0), <--="" if="" away="" team="" is="" favored,="" but="" i="" have="" the="" home="" winning="" by="" more="" than="" 7="" (in="" photo)
AND(-A8+A2<c8,c7=0), <--="" same="" as="" 1st,="" but="" for="" home="" team
AND(-A8-A2>C8,C7=0), <-- Same as 2nd, but for home team
AND(A8+A2<c7,c8=0)) <--="" same="" as="" 3rd,="" but="" for="" home="" team
,1,0)

It seems like I might be making this way more complicated than it needs to be, but I've been fighting with this off and on for several months. The reason doing it manually is such a time suck, is because every time I test a new formula I do it against ten years worth of games. So for College Football, thats 50 games a week for 12 weeks for 10 years, or 6000 games... SO you can see how this will make my life a lot easier.

I'm open to any suggestions outside of IF/AND/OR as well.
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #006107}span.s2 {color: #ab30d6}span.s3 {color: #0057d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}</style></c7,c8=0))></c8,c7=0),></c8,c7=0),></c7,c8=0),></c7,c8=0)),1,0)
</c8,c7=0),and(-a8+a2<c8,c7=0),and(-a8-a2></c7,c8=0),and(-a7-a2>

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### MrBurn5

##### New Member
What the heck? Hmm, it for some reason removed half of my formulas and changed most of my formatting. Sorry to make that look so messy. Let me know if you'd like me to try reposting the formula.

Replies
4
Views
574
Replies
8
Views
461
Replies
18
Views
1K
Replies
4
Views
2K
Replies
10
Views
362

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,861
Messages
5,834,032
Members
430,256
Latest member
Todor T

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

### Which adblocker are you using?

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

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