LinkBack URL
About LinkBacks
chemmiah 
Hello all,
I am trying to calculate using the attached table values for both current W/L streak for a team AND winning percentage in last 20 games.
Need more info?
Hello all,
I am trying to calculate using the attached table values for both current W/L streak for a team AND winning percentage in last 20 games.
Need more info?
try this, it this works for you, I'll work on the code to limit the stat to 20 games max.
you are close but i don't want a list like that. I already have too many unnecessary lists in my real worksheet and i am trying to elminate them. Seee the updated spreadsheet that is what i need. In my old spreadsheet, i have it so the dates and results for a teams last 20 or so games are listed. But the teams and their games were separated into separate columns and now i want one column for all games.
I limited the % calcultation to a max of the last 20 games.
I droplisted (alphabethical list in sheet2) the teams to make the input easier
Unfortunatey the win streak is just tell how many wins listed not what the current Win or Loss in a row are. For instance Duquesne's win/loss streak should be W1 because the last game was a win but the game before was a loss. Let's say the next game is a win for Duquesne, then the win/loss streak will be W2, if the next game for them entered is a loss then the win/loss streak is L1.
The Last 20 games is counting all games. I need it to find the most recent 20 occurances of a team and calculate the w/L%
This would be relatively easy IF the W/L was in a 1 dimensional array (single column or row).
One of my nieces went to Duquesne.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I can modify that row if need be. You have a solution? The new Streak (3) still doesn't work rcm, but great try.
I have code for the Last 20 and W streak but it is in a different format. Maybe instead is there a way for me to have a dynamic list of the last twenty games of a team posted on the side? If then i can change the team it changes the list?
Last edited by chemmiah; 05-17-2013 at 08:18 AM.
I think I got it. sorry, I was in meetings all day
I am trying this compromise. In the updated, excel spreadsheet attach, i a set of equation which return the dates for the twenty most recent games for one team (that team is dynamically changing) and in the next column whether that is a win or a loss
Here's the best I could come up with using the current data structure.
Insert a new column H. This column will remain empty so you can hide it if you want. We want to end up with a cell between the L/W cells.
Enter this formula in J1 and copy down:
=IFERROR(INDEX(G1:I1,MATCH(L$1,B1:D1,0)),"")
This will return the W or L for the team enterd in cell L1.
List the dates of the 20 most recent games...
Enter this array formula** in M5 and copy down to M24:
=LARGE(IF((B$1:B$66=L$1)+(D$1:D$66=L$1),A$1:A$66),ROWS(M$5:M5))
Format as Date
Enter this array formula** in N5 and copy down to N24. This will pull in the corresponding W/L for the date for the team entered in cell L1.
=INDEX(J$1:J$66,MATCH(M5,IF((B$1:B$66=L$1)+(D$1:D$66=L$1),A$1:A$66),0))
For the current streak...
Enter this array formula** in O5:
=FREQUENCY(IF(N5:N24=N5,ROW(N5:N24)),IF(N5:N24<>N5,ROW(N5:N24)))&" "&N5
For the winning %...
Enter this formula in P5:
=COUNTIF(N5:N24,"W")/20
Format as Percentage
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Thanks it worked beautifully.
You're welcome. Thanks for the feedback!
Sorry to resurrect such an old thread, but this was very helpful for an NFL spreadsheet I'm doing so thanks Tony.
I'm wondering if we can take it a step further and return the last 16 at the spread (ATS) results, streaks and win %s?
I've got a very similar spreadsheet up until column J.
Among a whole bunch of other columns, I've got the Closing Home Line in column Y and and the Closing Road (Away) Line at AC.
I'm going to try to figure this out myself but I'm quite stupid, so if anybody gets there first please help!
Hi.
It's a forum rule that you start your own thread, rather than try to "hijack" someone else's thread. This helps prevent massive confusion arising -which will happen if it is not clear which question is being answered. So please start your own thread and explain your own problem (make sure that, right from the start, you use a meaningful title (NOT things like "help needed" or "urgent problem"... think of the Google search terms that you would use to find the solution). Also, preferably attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)? Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).
1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.
2. Make sure that your desired solution is also shown (mock up the results manually).
3. Make sure that all confidential information is removed first!!
4. Try to avoid using merged cells. They cause lots of problems!
Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks