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

#### MrBurn5

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

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

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

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
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
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
It's basically the same as your formula, I just separated it out depending on whether D7 or D8 was ""

Replies
0
Views
166
Replies
0
Views
151
Replies
10
Views
279
Replies
5
Views
203
Replies
7
Views
92

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.

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