Looking to create a nifty Baseball Trends. And then I tried doing it...

[ikoiko]

Hey there. Long time since I have posted something and became stuck on something.

I want to track Wins and Losses for a set of baseball teams. Every game will have a W or an L

Go back 10 games and tell me the % of those games that team has won
Also, Go back 25 games and tell me the % of those games they have won
(EDITED) Also, sometimes they don't play. So, each cell for each game is a W or L or NG (no game)

Let's focus on the % won in the last 10 games (but the same calculation is needed for 25 games):
based on a 162 game schedule, this team will win _____ games.

For example Boston has won 3 games over the last 10 and therefore will win 113 games this year.
that row would look like this:
4/1 4/2 4/3 4/4 4/5 4/6 4/7 4/8 4/9 4/10 <<< these are the dates of the games
L W W W L W W L W W <<<<< Updated each day (manually)
And over the last 25 games, they've won 16 games, so they'll win 104 games.

Each day the projected standings would be redone, based on the prior 10 games and another based on 25 games.

So, it is the counting the W's in the row that I don't know how to handle.

Sound easy?

 

[ikoiko]

In the example:
"today" is April 12. i want to see their % of games won going back 10 days (back to April 2). The standings table (at the bottom) would basically mean "assuming the team's winning %, the final standings after 162 will be... (the standings would show first through fifth place)

The boston red sox have Won (w) games on March 29,30,31, etc. They have lost (l) games on 4/9
I added ng for a team to indicate they had No Game that day
I would add PPD or R to indicate a game was postponed or rained out.

 

[dave2018]

I repeat, if boston lost on 4/9, then why is cell N7 empty? (it should have a L !)

or do u want to treat blank cells as losses???

 

[ikoiko]

All cells prior to today would be either an L or W.
Just want to know the W's
If it rained, I'll put an R
if they are not scheduled to play, I'll put in NG (no game).
Updated version has been updated to google drive.

 

[dave2018]

Please share a link to the updated sheet. the link you posted previously hasn't updated.

 

[ikoiko]

I did not realize that a new link needs to be be made each time... i just created a new one and this should work.

https://drive.google.com/file/d/1bzKdqciFlhkg8J2LTYuiLExzCYkpAL9g/view?usp=sharing

 

[dave2018]

Hi, sorry for the delay. Here is your worksheet with the formula updated.

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
6	Team\Date		3/29/2018	3/30/2018	3/31/2018	4/1/2018	4/2/2018	4/3/2018	4/4/2018	4/5/2018	4/6/2018	4/7/2018	4/8/2018	4/9/2018	4/10/2018	4/11/2018	4/12/2018
7	BOSTON		w	w	w	w	w	w	w	w	w	w	w	l	w	w	w
8	NEW YORK		l	w	w	w	w	w	w	w	w	w	w	l	r	w	w
9	BALTIMORE		w	w	w	w	w	w	w	l	w	w	w	l	w	w	w
10	TAMPA		w	w	l	w	w	l	w	w	l	w	w	ng	w	w	w
11	TORONTO		w	w	w	w	w	ng	w	w	w	w	w	l	w	w	w
12
13
14	Date:	4/11/2018	Last 10	Last 25
15	Boston		146	Invalid Date
16	New York		130	Invalid Date
17	Baltimore		130	Invalid Date
18	Tampa		113	Invalid Date
19	Toronto		130	Invalid Date
Sheet1

Cell Formulas
Range	Formula
C15	{=IF(IFERROR(SUM(IF(INDEX($C$7:$ALN$11,MATCH($A15,$A$7:$A$11,0),N(IF(1,ROW(INDIRECT(MATCH($B$14,$C$6:$ALN$6,1)-(RIGHT(C$14,2)-1)&":"&MATCH($B$14,$C$6:$ALN$6,1),1)))))=0,1,0)),1)>0,"Invalid Date",ROUND(SUM(IF(INDEX($C$7:$ALN$11,MATCH($A15,$A$7:$A$11,0),N(IF(1,ROW(INDIRECT(MATCH($B$14,$C$6:$ALN$6,1)-(RIGHT(C$14,2)-1)&":"&MATCH($B$14,$C$6:$ALN$6,1),1)))))="W",1,0))*10%*162,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

The Issue was that the formula was looking at A14 and not the actual date in B14.

Note: The "last 25" won't work, because in this example, there aren't 25 dates filled in before the selected date.

Hope this clears it all up

 

[ikoiko]

Well, this is great! Thanks - it works great. for the dates prior to 4/11 - it is calculating the projected total wins, based on 162 games.
The last thing - at the bottom, i pasted in your early calculation for the standings, ranked large to small - and it is not doing something. The calculation that is in A24 thru B29 is this:

=INDEX($A$15:$C$19,RANK($C15,$C$15:$C$19,0)+COUNTIF($C$15:$C15,$C15)-1,IF(COLUMN(A1)=1,1,3))

But as you can see in the example - it is not sorting. So, I resorted A24 thru B29 from Large to Small in column B and that does not change.

I updated the Spreadsheet with the latest version.

Again - thanks so much for this - very interesting.

New Link:

https://www.dropbox.com/s/8t0ly4kv6talw7p/Trend.xlsx?dl=0