This formula erroes in the 3rd spot

=IF(AND(B7="m",I7="m"),+J7-C7,(IF(AND(B7="m",I7="o"),(50-C7)+(50-J7),(IF(AND(B7="o",I7="m",(J7-50)+(C7-50),IF(AND(B7="o",I7="o"),+C7-J7," ")))

I'm not sure what you mean by it errors in "the 3rd spot". Where is "the 3rd spot"? In any case, here's how I went about fixing your formula, starting with things that I saw that didn't look right:

First, you used +J7-C7 and +C7-J7

The plus signs are unnecessary and just confusing, so I took them out.

Second, you used (50-C7)+(50-J7) and (J7-50)+(C7-50)

The parentheses are unnecessary and just confusing, so I took them out and reduced both of those sections to 100-C7-J7 and J7+C7-100.

Third, you have an open parenthesis in front of your 2nd and 3rd IF's, which I don't believe belong there. Normally a Nested IF does not have an open parenthesis before an IF used as the

value_if_falseargument, so I took them out.Fourth, you are missing a parenthesis after AND(B7="o",I7="m", so I added one.

Once I cleaned all that up, I counted the open parentheses and the closed parentheses. I found 1 more open ones than closed ones, so I added one more at the end.

When I pressed Enter, Excel didn't argue with me, so I assume I got it right. The following is I ended up with. Since I don't know what values you have in C7 and J7, I don't know what your results should be, but at least the formula no longer errors.

=IF(AND(B7="m",I7="m"),J7-C7,IF(AND(B7="m",I7="o"),100-C7-J7,

IF(AND(B7="o",I7="m"),J7+C7-100,IF(AND(B7="o",I7="o"),C7-J7," "))))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Thank you!! Mostly silly errors but I guess I was too close to the forest. Works great and will be used to calculate yards gained in a football spreadsheet. Agiain, thanks.

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History