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

[MrBurn5]

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

[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][th]

D​

[/th][th]

E​

[/th][th]

F​

[/th][th]

G​

[/th][th]

H​

[/th][th]

I​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td]Lowe[/td][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td]Upper[/td][td]

21​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]Ldog[/td][td]

16​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]Udog[/td][td]

23​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

5​

[/td][td][/td][td=bgcolor:#A9D08E][/td][td=bgcolor:#A9D08E][/td][td=bgcolor:#A9D08E][/td][td=bgcolor:#A9D08E][/td][td=bgcolor:#A9D08E][/td][td=bgcolor:#A9D08E][/td][td=bgcolor:#A9D08E][/td][td=bgcolor:#A9D08E][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

6​

[/td][td][/td][td=bgcolor:#A9D08E]Vegas[/td][td=bgcolor:#A9D08E][/td][td=bgcolor:#A9D08E]Predicted[/td][td=bgcolor:#A9D08E][/td][td=bgcolor:#A9D08E][/td][td=bgcolor:#A9D08E]Actual[/td][td=bgcolor:#A9D08E][/td][td=bgcolor:#A9D08E][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

7​

[/td][td][/td][td=bgcolor:#A9D08E][/td][td=bgcolor:#A9D08E][/td][td=bgcolor:#A9D08E]=IF(E7=MAX(E7,E8), E7-E8,"")[/td][td=bgcolor:#A9D08E]

10.00​

[/td][td=bgcolor:#A9D08E]Charlotte[/td][td=bgcolor:#A9D08E]

14​

[/td][td=bgcolor:#A9D08E]=IF(G7=MAX(G7,G8), G7-G8,"")[/td][td=bgcolor:#A9D08E]

=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,"")​

[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

8​

[/td][td][/td][td=bgcolor:#A9D08E]

-39.5​

[/td][td=bgcolor:#A9D08E]

-38.5​

[/td][td=bgcolor:#A9D08E]

=IF(E8=MAX(E7,E8), E8-E7,"")​

[/td][td=bgcolor:#A9D08E]

60.00​

[/td][td=bgcolor:#A9D08E]Louisville[/td][td=bgcolor:#A9D08E]

70​

[/td][td=bgcolor:#A9D08E]

=IF(G8=MAX(G7,G8), G8-G7,"")​

[/td][td=bgcolor:#A9D08E][/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet1[/td][/tr][/table]

 

[rondeondo]

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]

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]

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]

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]

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]

With the data you posted this

ABS(B7+D7)

will give you the #VALUE error & so the entire formula will result in that error.

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Lowe	7
2	Upper	21
3	Ldog	16
4	Udog	23
5
6		Vegas		Predicted			Actual
7					10	Charlotte	14		#VALUE!	#VALUE!
8		-39.5	-38.5	50	60	Louisville	70	56
9
Main

Cell Formulas
Range	Formula
I7	I7	=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,"")
J7	J7	=ABS(B7+D7)
D7	D7	=IF(E7=MAX(E7,E8), E7-E8,"")
D8	D8	=IF(E8=MAX(E7,E8),E8-E7,"")
H7	H7	=IF(G7=MAX(G7,G8), G7-G8,"")
H8	H8	=IF(G8=MAX(G7,G8), G8-G7,"")

 

[Fluff]

Is this what you want

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

Is this what you want

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

It's basically the same as your formula, I just separated it out depending on whether D7 or D8 was ""