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

MrBurn5

New Member
Joined
Jul 4, 2016
Messages
48
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.



2lo2hk3.png
[/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>
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

MrBurn5

New Member
Joined
Jul 4, 2016
Messages
48
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,596
Messages
5,597,081
Members
414,121
Latest member
DamianX88

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
Top