Need Help Creating a Difficult Formula

[cms90740]

Hi there, I think this is a pretty tall challenge, not sure it can be done but I have asked for help on this forum in the past and was successful. What I'm trying to do seems pretty complex to me (I have a little more than basic knowledge of Excel and the possible formulas). I was first going to try to create an IF function that would add and subtract everything accordingly, but it appears it might be a bit more complicated.

Here's what I'm trying to do:
Determine if a team out scored the handicap by adding/subtracting multiple columns.

​

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD]home team[/TD]
[TD]handicap[/TD]
[TD]away team[/TD]
[TD]handicap[/TD]
[TD]home score[/TD]
[TD]away score[/TD]
[TD]total[/TD]
[TD]winner[/TD]
[TD]winner handicap[/TD]
[TD]handicap right/wrong[/TD]
[/TR]
[TR]
[TD]vipers[/TD]
[TD]-4[/TD]
[TD]cobras[/TD]
[TD]0[/TD]
[TD]17[/TD]
[TD]24[/TD]
[TD]41[/TD]
[TD]cobras[/TD]
[TD]0[/TD]
[TD]wrong[/TD]
[/TR]
[TR]
[TD]bulldogs[/TD]
[TD]-10[/TD]
[TD]tigers[/TD]
[TD]0[/TD]
[TD]42[/TD]
[TD]14[/TD]
[TD]56[/TD]
[TD]bulldogs[/TD]
[TD]-10[/TD]
[TD]right[/TD]
[/TR]
[TR]
[TD]kings[/TD]
[TD]0[/TD]
[TD]panthers[/TD]
[TD]-5[/TD]
[TD]13[/TD]
[TD]14[/TD]
[TD]27[/TD]
[TD]panthers[/TD]
[TD]-5[/TD]
[TD]wrong[/TD]
[/TR]
[TR]
[TD]stars[/TD]
[TD]0[/TD]
[TD]greyhounds[/TD]
[TD]-3[/TD]
[TD]21[/TD]
[TD]15[/TD]
[TD]36[/TD]
[TD]stars[/TD]
[TD]0[/TD]
[TD]wrong[/TD]
[/TR]
</tbody>[/TABLE]


What I'm trying to accomplish is in column J. The team with the negative handicap (i.e. -4) is the favorite to win the game. In order for the prediction (column J) to be correct, the winner (column H) must have a negative handicap (be the favorite) and have won the game by more than that respective handicap. If the winner (column H) has a handicap of 0, then the prediction is automatically wrong (the favorite did not win the game). If the winner in column H has a handicap of -5 but only won the game by 1 then the prediction would also be wrong. If the winner in column H has a handicap of -3 and won the game by 3 then the prediction is also wrong (margin of victory does not exceed the handicap).

In this instance the results of column J, the prediction would be as follows row 2 - wrong - cobras were not the favorite, row 3 - right bulldogs won by more than 10 points, row 4 - wrong - panthers only won by 1 handicap was 5, row 5 - wrong - stars were not the favorite.

My first instinct was to make a rule stating that a 0 in column I would equal wrong or "false", I could not figure out how to make sure every other instance gets subtracted and compared to the handicap correctly. If there was a way to subtract the handicap from the final scores, subtract those two scores, and then compare that to the initial handicap prediction..? Still seems flawed to me in that how would it attribute it to the correct team? Maybe someone here is much smarter than I am and knows of a more efficient way to compare these numbers. Any guidance you can lend would be much appreciated!

 

[steve the fish]

Think this works:

=IF(I2<0,IF(ABS(E2-F2)>ABS(I2),"right","wrong"),"wrong")

 

[steve the fish]

It doesnt apologies. Ill try again

 

[steve the fish]

This seems better:

=IF(I2<0,IF(ABS(I2) < CHOOSE(MATCH(H2,A2:C2,0),E2-F2,,F2-E2),"right","wrong"),"wrong")<choose(match(h2,a2:c2,0),e2-f2,,f2-e2),"right","wrong"),"wrong")[ code]<choose(match(h2,a2:c2,0),e2-f2,,f2-e2),"right","wrong"),"wrong")<="" html=""></choose(match(h2,a2:c2,0),e2-f2,,f2-e2),"right","wrong"),"wrong")[>

 

[cms90740]

Thanks steve! I appreciate the quick response, I'll test this out.

 

[cms90740]

Well this kinda works! I feel like it's pretty close, in this instance on row 4 (kings vs panthers) I'm getting a #VALUE! message but it seems to work correctly on the other rows. Any ideas what would be triggering that?

 

[steve the fish]

Make sure all your numbers are numbers and do not have leading or trailing spaces

 

[cms90740]

Double checked everything and tested it a few times with different numbers, it looks like it returns #VALUE! when the negative handicap (favorite) occurs in column D and column I the winner handicap and the winning team won by a score of 1 or greater. Not sure why it isn't recognizing this and subtracting the scores like it does with the others but I'm gonna keep poking around at it!