Almost Complete just need a bit of help

[athres]

I am working on a formula to calculate field position, along with total yards and after catch. I have formula in columns G and H

I have 2 small issues.
1) ROW 2 - is an example of a run play so there should be no yards after catch COL H2. If I remove the "X" then it is a wrong number, if I leave blank then I get #VALUE!

2) ROW 5 and 6 are similar in the that pass was incomplete. In ROW 5 I left E5 and F5 blank and it shows 42 G5 (Incorrect) which should be "0" and "0" in H5 (Correct), SO in ROW 6 I tried placing the original Number F6 and received "0" in G6 which is correct but H6 a negative number which is incorrect

I have attached a sheet which will hopefully clear up my jiberish.

Thank you in advance for any help


Excel Help request - J.xlsxExcel Help request - J.xlsx

[Zodeeak]

=IF(D2="R","",IF(AND(E2<0,F2>0,D2="p"),(E2-F2)+100,IF(AND(E2>0,F2<0),(E2-F2)-100,E2-F2)))

Hope that helps.

-Z

[athres]

Thank you will give it a try

How about the second part, would that be similar? Do I put an I for incomplete?

[Zodeeak]

You can. You can string together quite a few IF functions to keep narrowing the scope of information you want to put into a formula. I would probably form it like this:
=IF(or(D2="R",E2="I"),"",IF(AND(E2<0,F2>0,D2="p"),(E2-F2)+100,IF(AND(E2>0,F2<0),(E2-F2)-100,E2-F2)))
That way you're only dealing with completed passes data in that cell. That keeps the formula as simple as possible and that makes it easy to follow.

-Z

[athres]

Thank You Z. I will give it a try and appreciate your help.

Last question, If I then wanted to add a column, Column D and add Dynamic the I and P that are input, how would something like that read?

Again, thank you so much for your help
Jeff

[Zodeeak]

Not sure what you mean by Dynamic.
However, now that I'm thinking about this particular problem, you might want to modify your input, just a touch, and have Column E have 2 "I" inputs. "INC" and "INT" to account for interceptions. Just change the "I" to "INC" or "INT" in the formula.

-Z

[athres]

Can do!

As far as Dynamic, I tried to sound intelligent. By dynamic I mean, as I add additional inputs, the box would continue to make the tallies. IE R,R, R,R,R, would be 5 then add another would be 6.

Thank you again. How long have you been helping with excel issues?
Jeff

[Zodeeak]

You can, though the exact syntax evades me, right now, as I'm at work.

I've been trying to be helpful in this forum for a few weeks, now, as I get time. I'm an intermediate user, so I can help some, though anything with VBA eludes me and there are some functions I'm just not good with, so I try not to muddy waters with my ignorances, but things I do know, I try to help with to save the more difficult questions for those that know more.

Have a great Labor Day!

-Z

[athres]

Z I have a quick question.

=IF(OR(E10="R",F10="N"),"",IF(AND(F10<0,H10>0,E10="p"),(F10-H10)+110,IF(AND(F10>0,H10<0),(F10-H10)-110,F10-H10)))

My question is if in E10 I wanted to include two other possible inputs F and S. Would it now become

=IF(OR(E10="R",E10="F", E10="S",F10="N"),"",IF(AND(F10<0,H10>0,E10="p"),(F10-H10)+110,IF(AND(F10>0,H10<0),(F10-H10)-110,F10-H10)))

Thank you.

[Zodeeak]

That is one way to do it. However, given that E and F should both contain numeric values, I might just have it read:

=IF(OR(ISTEXT(E10),ISTEXT(F10)),"",IF(AND(F10<0,H10>0,E10="p"),(F10-H10)+110,IF(AND(F10>0,H10<0),(F10-H10)-110,F10-H10)))

This allows you to have any text value in there not interfere with your formulae.

Hope that helps. 8 )

-Z