Use formula in sports analysis to check accuracy of predictions which involves if one negative number is more negative than another negative number

[Doozyman]

I am trying to develop a formula to determine the accuracy of sports analysts' predictions. I can figure it out if all numbers are positive, but some are negative. For example, there are two teams playing, one a home team and the other the road team. On my sheet, the line is a positive number if the home team is the favorite. If the road team is the favorite, the line is negative. So if the road team is favored by 5, the line shows -5. If the predictor picks the road team to win by 7 (-7) and the road team wins by 10 (-10), the predictor is correct and would get a 1 (or TRUE) in my desired formula. It's easy enough to create a formula for the home team winning, and the predictor picking them correctly as something like =IF(AND(J8>=$G8,$G8>=$E8),1,0) will work where the line is in E8, the game result is in G8 and the analysts prediction is J8. So if the prediction is >= the line and >= the result, the prediction was correct and gets 1 (or TRUE). However, if, in the same scenario, the road team won and the predictor was accurate, the formula does not work. If the road team wins by 7 (-7) with the line being 5 (-5) and the analyst predicts a 10-point win (-10), for my purposes, the prediction of -10 is greater than both the line and the result, but in Excel, -10 is less than both -5 and -7 and the formula does not work. Any ideas? thanks

 

[Micron]

Hopefully I get the drift because for me, a table of values and results and the desired outcome would be easier for my old noodle, but I'm thinking you would use the ABS function (Absolute)
-10 is not greater than -7 as you say, but
ABS(-10) is greater than ABS(-7) because the function ignores the sign. I'm not an Excel formula guy so I'm presuming the VBA absolute function I know of is available as a worksheet function.

 

[Micron]

Using my own sheet to test my result is 1 with a formula like
=IF(AND(ABS(J29)>=ABS($G29),ABS($G29)>=ABS($E29)),1,0)

 

[Doozyman]

Micron, thanks for the time and effort. I’m not in front of my PC right but off the top of my head I don’t think the ABS would always work. There are times that you are dealing with both negative and positive numbers and ABS would give you incorrect results If for example the line is negative (road team) but the result and analyst pick is positive (home team). It’s all making my old noodle spin. I had also wondered if I could use ABS but I don’t think it works for all examples.

 

[Micron]

I think that for anyone (including everyone else) to help you'd be better off posting a table of numbers and a column for expected results. Descriptions can be difficult to follow. Copying from a sheet and pasting into a post works well. Pics of sheets not so much.