If/Then Question

  • Thread starter Trendon \(bPstyles\)
  • Start date
T

Trendon \(bPstyles\)

Because I have no idea what to call this since I am a newbie with Excel, my
searches in google did not work. Hopefully, I can explain what I need
without coming off sounding like an idiot... hell, I didn't even know what
to put in the subject line. Anyway...

I run an online NCAA Football league and we award points to coaches for
their successes, but with 119 teams, keeping track can be a real pain.
Thus, I am trying to make it as easy as possible through Excel. Wthin the
sheet, their are a number of variables, but this is what I am focusing on.

We award 5 points for a victory. As it is, the spreadsheet requires you to
type "WIN" in the outcome column and then add the 5 points yourself. I am
hoping to get it to be automated.

Perhaps there is a way to add a checkbox to one column or to have the POINTS
column automatically add the 5 points if (for example) "Win" is inputted
into the outcome column.

I would greatly appreciate any help.
 
T

Trendon \(bPstyles\)

We award 5 points for a victory. As it is, the spreadsheet requires you to
type "WIN" in the outcome column and then add the 5 points yourself. I am
hoping to get it to be automated.

Perhaps there is a way to add a checkbox to one column or to have the POINTS
column automatically add the 5 points if (for example) "Win" is inputted
into the outcome column.
Click to expand...

OK, I read the help file again and I figured how to do it with numbers, but
what if I wanted it to read:

=IF(A2=Yes,SUM(B5:B15),"")

Furthermore, in place of the ,SUM(B5:B15),"" part.. how could I get it to
just output 5?
 
S

Sandy Mann

With the Win's in column K,

=SUMPRODUCT((K2:K120="Win")*5)

or if there is a chance that the odd stray space may be tagged on to the end
of the "Win" word:


=SUMPRODUCT((LEFT(K2:K120,3)="Win")*5)

HTH

Sandy
 
T

Trendon \(bPstyles\)

Sandy Mann said:
With the Win's in column K,

=SUMPRODUCT((K2:K120="Win")*5)

or if there is a chance that the odd stray space may be tagged on to the end
of the "Win" word:


=SUMPRODUCT((LEFT(K2:K120,3)="Win")*5)

HTH
Click to expand...

Ah, that is easier than what the help file gave me.

One more question expanding this even further, if it is possible.

Let's say I want a column to take into account two separate variables. We
award bonus points (on top of the base number for wins) for defeating a
rival (2 additional points). How could I make it so that the cell will look
to see if there are "yes" datas in two separate columns and output
accordingly. All of the scenarios that are possible are:

1) Win
2) Win and Rival bonus
3) Loss (Thus, no rival bonus)

For example sake, let's say:

K = Wins
L = Rivals

Am I just treading into waters where I'd be better off using a DB?
 
T

Trendon \(bPstyles\)

=IF(D3="Win",5,"0")

I am thinking I would have to sandwich (e3="Yes",2,0) somewhere in the
middle, right?

By the way Sandy, while I won't use the SUM feature you showed me because I
need to show weekly outputs (thus, I'll just sum the columns with the final
output, you just solved about 5 other worksheets of stats I was doing the
long-way!
 
T

Trendon \(bPstyles\)

OK, with Sandy's help and Excel's help, I am moving along beautifully, but I
am now completely stuck.

Here is what I have:

=IF(D3="Win",5,"0")+IF(E3="Yes",2,"0")+IF(F3="Yes",5,"0")

D3 = 5 point bonus for a win
E3 = 2 point additional bonus for beating a rival
F3 = 5 point additional bonus for beating a Top 25 team

Now it gets fun...

In our league, if you are ranked (indicated by a Yes/No query in B3) and
lose to someone who is NOT ranked (indicated by a Yes/No query in F3), you
actually get penalized on top of getting 0's from D3 and E3. The penalty is
a -5.

So....

I need something that will do the following

Look at D3 (to see if a NO is input for a loss. Then...
Look at F3 (to see if they were ranked)

If D3 is "NO" and F3 is "No" then I want a penalty assessed. If either are
yes, then this last part is moot.

I can't seem to find a way to do this.
 
S

Sandy Mann

Aside from the fact that I don't know how you can Win without defeating a
rival <g>

and if I have followed all your contitions properly then try

=(D3="Win")*5+(E3="Yes")*2+((D3="Win")*(F3="Yes"))*5+((B3="Yes")*(D3="No")*(
F3="No"))*-5

for the condition ="No" you may want to use <>"Yes" which will accept any
entry that is not yes

HTH

Sandy
 
Top