|
how do i calculate wins and losses for basketball scoring?
|
how do i calculate wins and losses for basketball scoring?
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))) |
how do i calculate wins and losses for basketball scoring?
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))) |
how do i calculate wins and losses for basketball scoring?
"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. |
| All times are GMT +1. The time now is 06:34 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com