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?

 

[dave2018]

You can count the W's with the countif function.

=COUNTIF(A1:A10,"W")

And this will calculate for the games listed from A1 to A10.
Just put this formula in B1 or something like that...

=ROUND(COUNTIF(A1:A10,"W")*10%*162,0)

Now, for the rest of your question, what does your data look like? Is it across row 1 or down column A? ALso, in which cell(s) do you want the answer to be in (multiple or just 1 cell)?

I hope i am clear enough... if not, then pls correct me

 

[ikoiko]

Thanks - that is cool. I can't attached the Work In Process with my questions and what the format I'd like to have. How can I do that? maybe a PM?
Here is a copy/paste:
2018 BASEBALL TRENDS - 10 AND 25 GAMES
Note for Mr. Excel - thanks! this is what I want. 3 issues:
1. how can I have the calculation in c13 thru c17 automatically go back 10 games, this would be a rolling 10 games. In my example, April 8's game would go back to the game in D5, etc.
2. for the calculated games - - I'll enter another calculation for the prior 25 games - all set!
3. for the standings (Row 22) I would ideally like this to resort each time the days' are updated. For example - new york and baltimore are going to end up with 146 wins, but tonight, the yankees lose ad baltimore wins, the projected standings would be shifted, with baltimore now in 2nd place and yankees slipping to third.
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
BOSTON w w w w w w w w w w
NEW YORK l w w w w w w w w w
BALTIMORE w w w w w w w l w w
TAMPA w w l w w l w w l w
TORONTO w w w w w ng w w w w


Last 10 Last 25
Boston 162 use same calculation as the 10 game one.
New York 146
Baltimore 146
Tampa 113
Toronto 146


Standings if season ends today:
Column1 Column2
Boston 162 having a problem here - I want this section to automagically resort when I enter results above…
New York 146
Baltimore 146
Tampa 113
Toronto 146

And a picture of what it really looks like:
(can't do this either..... I can't show what it will look like.....)

You can count the W's with the countif function.

=COUNTIF(A1:A10,"W")

And this will calculate for the games listed from A1 to A10.
Just put this formula in B1 or something like that...

=ROUND(COUNTIF(A1:A10,"W")*10%*162,0)

Now, for the rest of your question, what does your data look like? Is it across row 1 or down column A? ALso, in which cell(s) do you want the answer to be in (multiple or just 1 cell)?

I hope i am clear enough... if not, then pls correct me

 

[dave2018]

Maybe consider uploading a sample sheet to google or dropbox (both are free) and share a link to your spreadsheet.

You should place the answers you want in their proper cells, which i can then convert to formulas

 

[ikoiko]

See if you can get to this.... i created a folder in my dropbox and here is a link (the excel is in there)
BTW - i did not receive a note yesterday you posted this question, my bad - apology.

https://www.dropbox.com/sh/fm2vb5kymb0bizt/AAAZp6Ec2_Zhz95HRBJkMSrLa?dl=0

 

[dave2018]

Hi, I added a small feature with your question.

If you look at this image:
https://drive.google.com/open?id=1v_yTY1kOcS79whHfM0d4cxoEq1kQITbn

I basically added a date in A14, that you can change it and it will look up the last 10 or 25 dates from that specific date!

Then in C15, add this array formula:

=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))

NOTE: you need to press CTRL+SHIFT+ENTER for this formula to work!

Then you can drag it across and down using the fill handle.

Let me know if that works.

 

[dave2018]

I just realized that you wanted to sort them on the bottom.

Put the following in A24 (you can delete the table):

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

Drag it across and down (you dont need ctrl+shift+enter for this one)

 

[ikoiko]

thanks. I am jumping on a train (with a ticket...) and will look at what you did and try and figure out what it does.

the excel i put in my dropbox has not been changed, has it ? doesn't look like it has.

stay tuned for my update.

thanks again

 

[ikoiko]

(train delayed....)
i did what (i think) you defined, but am getting a date error. I updated the Trends spreadsheet on my drop box.

https://www.dropbox.com/s/2cwhlb153r5cyzx/Trend.xlsx?dl=0

 

[dave2018]

in which cell is the error? and what type of error is showing? I can't see it in your uplaoded spreadsheet...