IF/OR/AND should work but getting #value error

MrBurn5

New Member
Joined
Jul 4, 2016
Messages
48
I'm so frustrated I'm the verge of tears. Anyone want to help take a stab at this?

Somehow the original files were lost (or stolen and deleted) so I've had to start from scratch. I do handicapping for various sports and the math to figure out the scores isn't that hard, it's just long. The most complicated formula in the whole process is actually the one in cell I7 that produces either a "1" or a blank space. A "1" tells me that this game has a favorable line and that people should consider betting on it. There are six possible results of a game:

1: Home team favored and covering the spread
2: Home team favored and not covering the spread
3: Home team favored and the underdog outright winning
4: Away team favored and covering
5: Away team favored and not covering
6: Away team favored and the underdog outright winning

The old formulas worked fine, but maybe I didn't check compatibility with this version of excel? While trying to rewrite the formula I used the ABS function to eliminate two of the OR statements. Each statement by itself works but when I add another AND statement for the OR Logical2 or Logical3 statement, THAT's when it fails.

The values in B1:B4 are upper and lower limits I use. B1 and B2 are for when a team is favored and I predict they will win, but either cover or not cover. The Ldog and Udog numbers are for when the underdog is predicted to win.

Unknown 64 bit

A
B
C
D
E
F
G
H
I
1
Lowe
7​
2
Upper
21​
3
Ldog
16​
4
Udog
23​
5
6
VegasPredictedActual
7
=IF(E7=MAX(E7,E8), E7-E8,"")
10.00​
Charlotte
14​
=IF(G7=MAX(G7,G8), G7-G8,"")
=IF(OR(AND(ABS(B8+D8)>$B$1,ABS(B8+D8)<$B$2),AND(ABS(B7+D7)>$B$1,ABS(B7+D7)<$B$2),AND(ABS(B8-D7)>$B$3,ABS(B8-D7)<$B$4),AND(ABS(B7-D8)>$B$3,ABS(B7-D8)<$B$4)),1,"")​
8
-39.5​
-38.5​
=IF(E8=MAX(E7,E8), E8-E7,"")​
60.00​
Louisville
70​
=IF(G8=MAX(G7,G8), G8-G7,"")​
Sheet: Sheet1
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

rondeondo

Board Regular
Joined
Aug 15, 2012
Messages
156
I see a problem to. I split out the four and() portions and wrapped their results with an OR, no problem there. I have come across some Excel functions having a maximum number of characters, but usually this if 255 if there's going to be a problem.
You could try arranging the ANDs in nested IFs. May be worth a shot. If that doesn't work, the workaround of breaking out the ANDs into four separate cells then doing the OR over those does work.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
Either D7 or D8 will be "" and then the formula in I7 is then trying to add/subtract that value, which it can't do as "" is a string.
 

MrBurn5

New Member
Joined
Jul 4, 2016
Messages
48
Either D7 or D8 will be "" and then the formula in I7 is then trying to add/subtract that value, which it can't do as "" is a string.

Oh shoot... I did make a few manual changes. E7:E8 are formulas in the main sheet since they are pulling the values from another sheet where all the calculations happen. Under normal circumstances the only thing I enter manually are the opening and gametime lines (that's why there are two values under "vegas". The team names and the final score. I figured it shouldn't be a problem though since I7 is referencing the other cells in their natural state.

I hang to god don't know why it's not working now when it worked fine (under a similar structure) last year. Those formulas are all ancient history now. I can do Rondeondo's idea but this sheet is the master results sheet for the entire season. so ~64 games and ~14 weeks with bowl games =900 games. dragging this formula over in 5 other cells, and the one for the game time odds (x6), and the one for calculating if the prediction was right (x12) would be 324,000 cells worth of formulas I'm trying to avoid.

I like to go back and make tweaks to the past 15 years every now and then to see if I can improve the accuracy so this is one of the more important formulas.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

It's not working because you are trying to add/subtract a null string "", I suspect that you handled that differently in the original workbook.
 

MrBurn5

New Member
Joined
Jul 4, 2016
Messages
48
It's not working because you are trying to add/subtract a null string "", I suspect that you handled that differently in the original workbook.

Possibly? Most of this is muscle memory so once a formula works, I tend to forget about what it took to make it work. Took me forever to realize what the difference was between " " and "" when I was trying to see IF a cell equalled 0, was blank or was blank but had a formula in the cell making it blank.

When I take each individual AND statement and test it against an example where it should produce the "1" for the correct value, it works. It's only producing the error when I put two of the AND's inside an OR statement. I just got home so I'll go back and try a few different things.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

With the data you posted this
Excel Formula:
ABS(B7+D7)
will give you the #VALUE error & so the entire formula will result in that error.
+Fluff v2.xlsm
ABCDEFGHIJ
1Lowe7
2Upper21
3Ldog16
4Udog23
5
6VegasPredictedActual
7 10Charlotte14 #VALUE!#VALUE!
8-39.5-38.55060Louisville7056
9
Main
Cell Formulas
RangeFormula
I7I7=IF(OR(AND(ABS(B8+D8)>$B$1,ABS(B8+D8)<$B$2),AND(ABS(B7+D7)>$B$1,ABS(B7+D7)<$B$2),AND(ABS(B8-D7)>$B$3,ABS(B8-D7)<$B$4),AND(ABS(B7-D8)>$B$3,ABS(B7-D8)<$B$4)),1,"")
J7J7=ABS(B7+D7)
D7D7=IF(E7=MAX(E7,E8), E7-E8,"")
D8D8=IF(E8=MAX(E7,E8),E8-E7,"")
H7H7=IF(G7=MAX(G7,G8), G7-G8,"")
H8H8=IF(G8=MAX(G7,G8), G8-G7,"")
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
Is this what you want
Excel Formula:
=IF(D7="",IF(OR(AND(ABS(B8+D8)>$B$1,ABS(B8+D8)<$B$2),AND(ABS(B7-D8)>$B$3,ABS(B7-D8)<$B$4)),1,""),IF(OR(AND(ABS(B7+D7)>$B$1,ABS(B7+D7)<$B$2),AND(ABS(B8-D7)>$B$3,ABS(B8-D7)<$B$4)),1,""))
 

MrBurn5

New Member
Joined
Jul 4, 2016
Messages
48
Is this what you want
Excel Formula:
=IF(D7="",IF(OR(AND(ABS(B8+D8)>$B$1,ABS(B8+D8)<$B$2),AND(ABS(B7-D8)>$B$3,ABS(B7-D8)<$B$4)),1,""),IF(OR(AND(ABS(B7+D7)>$B$1,ABS(B7+D7)<$B$2),AND(ABS(B8-D7)>$B$3,ABS(B8-D7)<$B$4)),1,""))


!!! YES!

And I must say you did it completely differently than I had it last time, but your's is also far far cleaner. I pasted it in to the sheet and it's working correctly for the six test games. I want to pick apart yours to make sure I fully understand the differences and then stress test it in the master worksheet. I owe you a coke. :)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,301
Office Version
  1. 365
Platform
  1. Windows
It's basically the same as your formula, I just separated it out depending on whether D7 or D8 was ""
 

Watch MrExcel Video

Forum statistics

Threads
1,127,098
Messages
5,622,682
Members
415,920
Latest member
ExcelNoob28

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