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]

I guess public folders in dropbox are no longer possible. How can i send this file to you? I can't attached it here ad a PM to you does not seem to offer attachments as an option....

the error i am getting is date error, like in your example. i have formatted the fields to all be date and they are in the same cells as your example...
i can give you my email: chrisjemail@me.com if that helps.

 

[dave2018]

can you upload it to google drive and turn on link sharing and paste the link here? I don't like answering public forum questions by PM, since the public may use this in the future.

 

[ikoiko]

ok, i don't use google drive - but i think this will get yout there and allow changes....

https://drive.google.com/file/d/143M3CyyE0dPWnCfGJf-dzpDaYk88TVFi/view?usp=sharing

 

[dave2018]

OH! I'm sorry! That was a little part of my formula. If the date entered in the top left doesn't have 10 dates before it in your sheet, then it says "invalid date"!

Do you want any changes to the way that works?

 

[ikoiko]

(I am tweaking the one on my PC, not the one in google)
- ok, i changed the Date: (B14) to be 4/9/2018)
- there are entries prior to 4/9/2018
- control shift Enter
----- the date error remains.

the expectation was that it would display the standings - based on the last 10 games and the standings based on the last 25 games. I would assume there would be date errors until such time the teams play 10 games and 25 games.

 

[dave2018]

I Believe that there is no data under the date you entered... (the formula only works if there is data in the 10 cells that we are talking about.) Do you want me to remove that "feature"?

("feature", because it warns u that this in not a correct input...)

 

[ikoiko]

humm... i thought there was data - so - maybe quicker for you to adjust things and i can reverse engineer. CAN YOU GET to the google drive version of the file?

i very much appreciate this... and i am doing this strictly for personal use....

 

[dave2018]

In the google drive, the date 4/9/2018 and the one before DONT have data... (and therefore, if u would calculate those empty cells, you wouldn't get accurate results, would you? or maybe u would)

 

[ikoiko]

true.
but there have been no games prior to april 9.
No matter what - when i do the control shift enter - am getting that same error.
i cleaned up the example.

 

[dave2018]

true.
but there have been no games prior to april 9.
No matter what - when i do the control shift enter - am getting that same error.
i cleaned up the example.

So what are the values w and l in columns C to L?
Sorry for my not-understanding...