Help With "If" statements

[trendon]

I am a beginner/intermediate Excel user, so bear with me as I struggle to explain what I want. Thanks in advance for your patience as this may get long-winded.

I am in a NCAA Football Online league that awards points to coaches for their successes and failures. Being that there are 119 teams, this can grow quite tedious and I am looking to automate the process as much as I can. I am going to outline what my worksheet looks like as of now. I am just going to reference row 3 throughout.

A3 - Week (Insignificant)
B3 - Ranked? (If statement set up for "Yes")
C3 - Opponent (Insignificant)
D3 - Outcome (If statement set up for "Win")
E3 - Conference Game (If statement set up for "Yes")
F3 - Opponent Ranked? (If statement set up for "Yes")
G3 - Blank for formatting

OUTPUT CELLS

H3 - Takes D3 and E3 into consideration
I3 - Same as H3 plus the bonus for beating a ranked team (F3)
J3 - Penalties

Key Numbers

Win (D3) - 5 points
Conference Win (E3) - 5 points
Big Win (F3) - 5 points awarded if F3=Yes

Task #1

You get 5 points for a win (D3) and an additional 2 points for a conference win (E3). I can set it up where the points are added up, but I want to know how I can do it so if D3=No (thus, no points), E3 is automatically taken out of the picture. As it is, I have... =IF(D3="Win",5,"0")+IF(E3="Yes",2,"0") which will award the 2 extra points regardless of what D3 is. This all goes to H3.

More info: Just in case you are not a sports fan, a conference game consists of teams within your division. So, all the cell is asking is if this is a game vs a conference team. I want a Yes or No answer, so it is independent of D3 as even though the team might have lost, it is still considered a conference game and I need that to be indicated.

Task #2

Do the same as #1, but also include the bonus for beating a ranked team, if applicable. As it is, I have this in I3... =IF(D3="Win",5,"0")+IF(E3="Yes",2,"0")+IF(F3="Yes",5,"0")

That is fine and well, but it still awards the 2 points for E3 even in a loss. I am going to assume if you answer Task #1 for me, I can just tack on ")+IF(F3="Yes",5,"0") but I want to be sure.

Task #3

In my league, if you are ranked (B3) and you lose to a non-ranked team (F3), you get none of the aforementioned points, plus you get hit with a -5 penalty. I have no idea how to indicate this. In the end, this will be output to J3.

Thanks in advance for any help, as I would be 100% appreciative.

 

[Smitty]

Welcome to the Board!

Lots of sports fans here willing to help (as long as you play Lacrosse! )

Take a look at the bottom of the page for the link to Colo's HTML Maker, which will allow you to post a representative shot of your sheet(s).

From there it will be much easier to get you an answer. (But I'm a visual kinda guy...)

Smitty

 

[trendon]

Getting Error 91 when I try to implement the add-in, searching for help right now.

 

[Fergus]

Hi Trendon, try these formula, without the outer quotes:

In H3 "=IF(D3="win",5,0)+IF(AND(D3="win",E3="yes"),2,0)"
In I3 "=H3+IF(AND(D3="win",F3="yes"),5,0)" and
In J3 "=IF(AND(B3="yes",F3="yes",D3<>"win"),-5,"")"

Worked for me.
Fergus

 

[trendon]

I love you

... in like that manly way!

Seriously, though, thanks a lot. Now that I see how it is done using the proper syntax, I can now move on to other exploits. I appreciate both of your help an I will now bookmark this site in hopes of getting help again in the future and answering any questions I might know!

Trendon

 

[Fergus]

Trendon, Having just read your task list again I noticed that the formula I gave you for J3 contained an error it should have read:

"=IF(AND(B3="yes",F3<>"yes",D3<>"win"),-5,"")"

as it was it would have given the penalty point if the opponent was ranked, whereas you said the penalty is only earned if the opponent was not ranked, however it's right now I think.

Fergus