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