I have created a scoring spreadsheet for a fantasy basketball league. I'm
trying to automatically calculate the wins and losses rather than calculate
them manually. How do I do this??

send responses to:

I'd have a line for each game, with team names in columns A and D, and the
scores in column B and C. Then I'd have a results in columns E and F (which
can eventually be hidden), with the following formulas.

Column E, for the result of the team in column A:
=IF(OR(ISBLANK(B2),ISBLANK(C2)),"",IF(B2C2,"W",IF (C2B2,"L","T")))

Column F, for the result of the team in column B:
=IF(OR(ISBLANK(B2),ISBLANK(C2)),"",IF(C2B2,"W",IF (B2C2,"L","T")))
or more simply:
=IF(E2="W","L",if(E2="L","W",E2))

Then a table (in a new worksheet) to tally the results:
Cell A2 has team name
Cell B2 has wins:
=Sum(if(Sheet1!A1:A200=A2,if(Sheet1!E1:E200="W",1, 0)))+Sum(if(Sheet1!D1:D200=A2,if(Sheet1!F1:F200="W ",1,0)))
Cell C2 has losses:
=Sum(if(Sheet1!A1:A200=A2,if(Sheet1!E1:E200="L",1, 0)))+Sum(if(Sheet1!D1:D200=A2,if(Sheet1!F1:F200="L ",1,0)))

Note that these last two are array formulas, so press Control-Shift-Enter.
And adjust the 200 if there are more games to count. Or instead of doing
array formulas, you could have the formula in the results columns of Sheet1
return something like "BullsWin" or "BullsLose" in place of "W" and "L", then
count wins and losses this way:
=COUNTIF(Sheet1!E1:F200,A2&"Win")
=COUNTIF(Sheet1!E1:F200,A2&"Lose")

"rayammo461" wrote:

I have created a scoring spreadsheet for a fantasy basketball league. I'm
trying to automatically calculate the wins and losses rather than calculate
them manually. How do I do this??

send responses to:

On Nov 18, 10:02 pm, J. Sperry
wrote:
I'd have a line for each game, with team names in columns A and D, and the
scores in column B and C. Then I'd have a results in columns E and F (which
can eventually be hidden), with the following formulas.

Column E, for the result of the team in column A:
=IF(OR(ISBLANK(B2),ISBLANK(C2)),"",IF(B2C2,"W",IF (C2B2,"L","T")))

Column F, for the result of the team in column B:
=IF(OR(ISBLANK(B2),ISBLANK(C2)),"",IF(C2B2,"W",IF (B2C2,"L","T")))
or more simply:
=IF(E2="W","L",if(E2="L","W",E2))

Then a table (in a new worksheet) to tally the results:
Cell A2 has team name
Cell B2 has wins:
=Sum(if(Sheet1!A1:A200=A2,if(Sheet1!E1:E200="W",1, 0)))+Sum(if(Sheet1!D1:D20-0=A2,if(Sheet1!F1:F200="W",1,0)))
Cell C2 has losses:
=Sum(if(Sheet1!A1:A200=A2,if(Sheet1!E1:E200="L",1, 0)))+Sum(if(Sheet1!D1:D20-0=A2,if(Sheet1!F1:F200="L",1,0)))

Note that these last two are array formulas, so press Control-Shift-Enter.
And adjust the 200 if there are more games to count. Or instead of doing
array formulas, you could have the formula in the results columns of Sheet1
return something like "BullsWin" or "BullsLose" in place of "W" and "L", then
count wins and losses this way:
=COUNTIF(Sheet1!E1:F200,A2&"Win")
=COUNTIF(Sheet1!E1:F200,A2&"Lose")



"rayammo461" wrote:
I have created a scoring spreadsheet for a fantasy basketball league. I'm
trying to automatically calculate the wins and losses rather than calculate
them manually. How do I do this??

send responses to: - Hide quoted text -

- Show quoted text -

The first part worked great...Thanks!

This part I'm having a bit of a problem with....

I deleted all the extra spaces, headings, etc.. off the tab
"LeagueSchedule" (where the team scores are, thinking that was causing
th problem. I created a new sheet and added the formulas, just as you
described below...nothing. It gave me a #REF in the blocks. :( Any
thoughts?

Then a table (in a new worksheet) to tally the results
Cell A2 has team name
Cell B2 has wins:
=Sum(if(Sheet1!A1:A200=A2,if(Sheet1!E1:E200="W",1, 0)))+Sum(if(Sheet1!D1:D20-0=A2,if(Sheet1!F1:F200="W",1,0)))
Cell C2 has losses:
=Sum(if(Sheet1!A1:A200=A2,if(Sheet1!E1:E200="L",1, 0)))+Sum(if(Sheet1!D1:D20-0=A2,if(Sheet1!F1:F200="L",1,0)))

Somehow, a rogue dash got inserted in the equations. Where it says "20-0",
change it to "200". By the way, you also need some dollar signs to anchor
the cell references. Heck, I just redo them to avoid confusion.

Cell B2 totals the wins:
=Sum(if(Sheet1!A$1:A$200=A2,if(Sheet1!E$1:E$200="W ",1,0)))+Sum(if(Sheet1!D$1:D$200=A2,if(Sheet1!F$1: F$200="W",1,0)))
Cell C2 totals the losses:
=Sum(if(Sheet1!A$1:A$200=A2,if(Sheet1!E$1:E$200="L ",1,0)))+Sum(if(Sheet1!D$1:D$200=A2,if(Sheet1!F$1: F$200="L",1,0)))


" wrote:

The first part worked great...Thanks!

This part I'm having a bit of a problem with....

I deleted all the extra spaces, headings, etc.. off the tab
"LeagueSchedule" (where the team scores are, thinking that was causing
th problem. I created a new sheet and added the formulas, just as you
described below...nothing. It gave me a #REF in the blocks. :( Any
thoughts?

Then a table (in a new worksheet) to tally the results
Cell A2 has team name
Cell B2 has wins:
=Sum(if(Sheet1!A1:A200=A2,if(Sheet1!E1:E200="W",1, 0)))+Sum(if(Sheet1!D1:D20-0=A2,if(Sheet1!F1:F200="W",1,0)))
Cell C2 has losses:
=Sum(if(Sheet1!A1:A200=A2,if(Sheet1!E1:E200="L",1, 0)))+Sum(if(Sheet1!D1:D20-0=A2,if(Sheet1!F1:F200="L",1,0)))

"J. Sperry" wrote:

Somehow, a rogue dash got inserted in the equations. Where it says "20-0",
change it to "200". By the way, you also need some dollar signs to anchor
the cell references. Heck, I just redo them to avoid confusion.

Cell B2 totals the wins:
=Sum(if(Sheet1!A$1:A$200=A2,if(Sheet1!E$1:E$200="W ",1,0)))+Sum(if(Sheet1!D$1:D$200=A2,if(Sheet1!F$1: F$200="W",1,0)))
Cell C2 totals the losses:
=Sum(if(Sheet1!A$1:A$200=A2,if(Sheet1!E$1:E$200="L ",1,0)))+Sum(if(Sheet1!D$1:D$200=A2,if(Sheet1!F$1: F$200="L",1,0)))


" wrote:

The first part worked great...Thanks!

This part I'm having a bit of a problem with....

I deleted all the extra spaces, headings, etc.. off the tab
"LeagueSchedule" (where the team scores are, thinking that was causing
th problem. I created a new sheet and added the formulas, just as you
described below...nothing. It gave me a #REF in the blocks. :( Any
thoughts?

Then a table (in a new worksheet) to tally the results
Cell A2 has team name
Cell B2 has wins:
=Sum(if(Sheet1!A1:A200=A2,if(Sheet1!E1:E200="W",1, 0)))+Sum(if(Sheet1!D1:D20-0=A2,if(Sheet1!F1:F200="W",1,0)))
Cell C2 has losses:
=Sum(if(Sheet1!A1:A200=A2,if(Sheet1!E1:E200="L",1, 0)))+Sum(if(Sheet1!D1:D20-0=A2,if(Sheet1!F1:F200="L",1,0)))


Ok I figured out what the problem was...LOL I didn't use the ctrl+shft+enter
thing. :) But all is good with the formula working...

How can I use the formulas in multiple columns and still tally the wins and
losses on the scoreboard???

http://www.4shared.com/file/30266118...FBL_07-08.html

There is a copy of the sheet I'm working on.