Probably a very simple Excel question

[Saints Rest]

With little work happening, I am devoting a ridiculous amount of attention to tracking stats on my son’s baseball team.

As such, I have a table showing the stats of each pitcher in each game listed int he top half of the workbook, broken down by game. In the bottom I want to sum up the totals across all games per player in each category. Names are in rows, stats are in columns.

So my question is what syntax do I need to use in cell range C23:K27 to have them show totals of the data in the table, aggregated appropriately? Ideally, the totals in the bottom will adjust as I add more data to the table, but I’m guessing that will be obvious.

Here’s a screen grab.

 

[Over Guapo Grande]

My lazy Excel answer... Subtotals? Break on Name, and then sum/avg columns ?

 

[LogansDad]

Try something like "=SUMIF(B4:B20,"Riley",C4:C20)

That might get you on the right track.

Edit: One other thing I might consider is putting the totaled stats to the side or on another sheet, that way you can use "B:B" instead of "B4:B20" and it will automatically sum up the stuff in that whole column as you add new players in, and you won't have to be inserting new rows at the bottom of the sheet each time you want to add an appearance.

 

[Bergs]

Step 1: move your lower table to a new sheet, keeping the player names and column titles
Step 2: Formula for what is now C23, but will be moved to a new sheet

=sumif(sheet1!$B:$B,$B23,sheet1!C:C) --- Where "$B23" is actually where you moved the lower table to

That should be looking of all instances of "Riley" in Column B of sheet1, and adding up all values in column C if sheet1.

Step 3: Copy that formula and paste it in the equivilant of C23-K27 in your new table. Then do the math in columns L and M.

That will work until there have been 1,048,573 game/pitcher entries.

 

[Humphrey]

Another way is to have each worksheet represent a given ballgame. List all the players for each game whether they pitch that game or not (there will be some 0 0 0 s if you know what I mean).

So, each worksheeet has "Riley", "Neko", etc.

Then have a worksheet that totals up each of the "game" worksheets. You shouldn't have to worry about someone's name that way, as long as your roster is accurate and in the same order on each sheet....AND, if you add a player, you add the player to each sheet.

 

[Saints Rest]

Try something like "=SUMIF(B4:B20,"Riley",C4:C20)

That might get you on the right track.

Edit: One other thing I might consider is putting the totaled stats to the side or on another sheet, that way you can use "B:B" instead of "B4:B20" and it will automatically sum up the stuff in that whole column as you add new players in, and you won't have to be inserting new rows at the bottom of the sheet each time you want to add an appearance.

With a little judicious use of $, I made your first version work.

 

[LogansDad]

With a little judicious use of $, I made your first version work.

I will say that Bergs' solution is better because it will be easier to cut and paste, and you won't have to update the names in each of the formulas.

 

[tbrown_01923]

Seems like a pivot table solution to me

 

[begranter]

Seems like a pivot table solution to me

This is the correct answer, especially since you have the data in tabular format already.

Either way, you can make the arrays dynamic by using the offset formula. The easiest way to do this for the pivot solution is use a named range:

1) in the "Formulas" menu, select "Name Manager", then click the "New" button"
2) In the name field, write "RawData", make sure "Scope" is Workbook.
3) The refers to field, click the up arrow to open the selection field and write the following (note using arrows to move within the text of this box will mess up the formula and I'm assuming the sheet name is Sheet1): =offset(Sheet1!$A$3,0,0,counta($A:$A)-1,counta($3:$3))
4) You can now type "RawData" into the field for the new pivot and when you hit refresh it will automatically capture any new lines that are entered, as long as they have a date in column A. (Note: the "-1" is to account for you having cell A1 populated. If you delete that, you can take out the -1. I would have suggested use column B instead, but since you have the table below, that will mess it up.

If you insist on having a summary table, as suggested above, put it on another tab. Then, you can use the offset formula in the table itself and just drag the formula across the table.
In your first cell, (in this case Riley's IP, C23) the formula would be: =SUMIF(offset($B$3,1,0,counta($B:$B)-1,1),$B23,offset(C$3,1,0,counta(C:C)-1,1)) where "$B23" is the cell where you write "Riley" in the summary table (This is all assuming the table below is removed)

 

[Bergs]

Seems like a pivot table solution to me

I hate pivot tables. Not sure why, really.

 

[DJnVa]

I'd say the biggest issue isn't EXCEL it's the pitching staff.

 

[tims4wins]

I question the ER vs R column. They're kids, there have been no errors / unearned runs? Fake news.

 

[Saints Rest]

I'd say the biggest issue isn't EXCEL it's the pitching staff.

I question the ER vs R column. They're kids, there have been no errors / unearned runs? Fake news.

Hah!
Our team uses an app called GameChanger which uses traditional box score layouts (likely because it is used by all ages). My son's team is in a 10 and under Cal Ripken league. The coach who inputs the game as it happens, treats all balls in play as Hits, regardless of the fielding involved. In reality, almost all the runs are unearned, but I just wanted to be able to copy data from the app to my phone without having to sift thru play by play to figure out which runs were actually earned.

Now that the Excel question has been solved -- and thank you for all the suggestions -- bonus question: Which pitcher is my son?

 

[tims4wins]

I'll say Ryan

 

[Over Guapo Grande]

I'd say Neko. You always dreamed of being a knuckleballer, and wanted your kid named Neikro. Mrs. Rest would have none of that. You wanted your child to be safe, so you got rid of the IR, leaving you with Neko. #Columbo

 

[Saints Rest]

I'll say Ryan

Strike one.

I'd say Neko. You always dreamed of being a knuckleballer, and wanted your kid named Neikro. Mrs. Rest would have none of that. You wanted your child to be safe, so you got rid of the IR, leaving you with Neko. #Columbo

Strike two.

 

[BroodsSexton]

Willing to bet the OP's son is Riley.

 

[Saints Rest]

Willing to bet the OP's son is Riley.

Please show your work.

 

[BroodsSexton]

Please show your work.

Isn't it obvious? Appears first on the table for no good reason (not organized alphabetically, by ERA, or by IP). Plus, you wouldn't be spending so much time tracking the statistics if your son was a scrub. Gotta have the stats in front of you when you complain to the coach that your son isn't pitching enough (even though he's getting the most innings on the team)

 

[Saints Rest]

Isn't it obvious? Appears first on the table for no good reason (not organized alphabetically, by ERA, or by IP). Plus, you wouldn't be spending so much time tracking the statistics if your son was a scrub. Gotta have the stats in front of you when you complain to the coach that your son isn't pitching enough (even though he's getting the most innings on the team)

Impressive, Mr Holmes.

 

[tbrown_01923]

I hate pivot tables. Not sure why, really.

why? they make this type of rollup easier... store the data as raw as you possibly can, set your dimensions (rows) to be any level you want to roll up to be and add the measures to the column to be yoyr aggregates (by default they ar eeither sums or counts, but you can right cliecn and change them easily).

 

[BroodsSexton]

Impressive, Mr Holmes.

I’ve seen this episode before. My son was in 10U several years ago...Honestly I’m sort of surprised there isn’t a significantly higher number of unearned runs for Riley. I was always amazed by the guy who kept the stats for my son’s team, and literally sent long emails with advanced metrics explaining why his son should be the lead off hitter...as a 10 year old. Funny thing was, the other kids always seemed to be getting on base as a result of errors, while his son had all the hits. And his son always had the unearned runs, as a pitcher...!

But then again, maybe Riley is just DOMINATING THE LEAGUE!

Poor Neko. DFA. Maybe he can play soccer.

 

[Bergs]

I hate pivot tables. Not sure why, really.

why? they make this type of rollup easier... store the data as raw as you possibly can, set your dimensions (rows) etc

Do they help with reading comprehension?

On a more serious note, I am generally an "under the hood" guy, and I like having explicit control of what I generate in xls, especially when I sprinkle in VBA, etc. Not saying pivot tables CAN'T do that, just that in my experience, you need to fight them a bit more. YMMV

 

[Tangled Up In Red]

Seems like a VLOOKUP solution to me.
But what up with OJ's earned runs on 9/27?!

 

[Bergs]

Seems like a VLOOKUP solution to me.
But what up with OJ's earned runs on 9/27?!

it wasn't. [/narrator]

 

[santadevil]

Did you blow up Ryan's arm on Day 1 and (s)he can't pitch anymore?

 

[tbrown_01923]

On a more serious note, I am generally an "under the hood" guy, and I like having explicit control of what I generate in xls, especially when I sprinkle in VBA, etc. Not saying pivot tables CAN'T do that, just that in my experience, you need to fight them a bit more. YMMV

Ahh - when I need more horsepower than excel pivot table can provide, i jump into something like R or Python. I legitimately fear VBA for any Office based tasks (try looking at the objects behind outlook).

 

[Saints Rest]

Did you blow up Ryan's arm on Day 1 and (s)he can't pitch anymore?

Updated data, with totals now accurate thanks to Bergs' help.

Some background:

• Riley is indeed my son.
• Jack is the Head coach's son.
• Ryan is the Asst coach's son, but he plays on an academy team up in Hartford on weekends and thus isn't available to pitch as much.
• All the players except for OJ and Riley play on the town's travel team (well, Ryan did until he switched to the academy team). That team won the state 8U championship in 2019, finished 2nd in the New England regionals, and 6th in the nationals.
• Jayden had been consistently the best pitcher until he threw 7 walks in 2.2 innings on Sunday in the Fall Ball semis.

 

[BroodsSexton]

Updated data, with totals now accurate thanks to Bergs' help.
View attachment 35151
Some background:

• Riley is indeed my son.
• Jack is the Head coach's son.
• Ryan is the Asst coach's son, but he plays on an academy team up in Hartford on weekends and thus isn't available to pitch as much.
• All the players except for OJ and Riley play on the town's travel team (well, Ryan did until he switched to the academy team). That team won the state 8U championship in 2019, finished 2nd in the New England regionals, and 6th in the nationals.
• Jayden had been consistently the best pitcher until he threw 7 walks in 2.2 innings on Sunday in the Fall Ball semis.

BTW, I didn't mean to give you shit (well, maybe a little bit!) I hope you and your son are having fun. Keeping statistics for young kids can be frustrating, because they change so much. But it can be fun, too (particularly when things are going good!)

 

[Saints Rest]

BTW, I didn't mean to give you shit (well, maybe a little bit!) I hope you and your son are having fun. Keeping statistics for young kids can be frustrating, because they change so much. But it can be fun, too (particularly when things are going good!)

No offense taken! This thread, when it's not confusing me discussing the topic at hand, has been thoroughly amusing!

 

[wiffleballhero]

Jayden gets the yips! Bummer.

 

[Saints Rest]

Jayden gets the yips! Bummer.

Combined with an ump who seemed to be unaware that these pitchers were 10U. The zone was tight.

 

[santadevil]

Updated data, with totals now accurate thanks to Bergs' help.
View attachment 35151
Some background:

• Riley is indeed my son.
• Jack is the Head coach's son.
• Ryan is the Asst coach's son, but he plays on an academy team up in Hartford on weekends and thus isn't available to pitch as much.
• All the players except for OJ and Riley play on the town's travel team (well, Ryan did until he switched to the academy team). That team won the state 8U championship in 2019, finished 2nd in the New England regionals, and 6th in the nationals.
• Jayden had been consistently the best pitcher until he threw 7 walks in 2.2 innings on Sunday in the Fall Ball semis.

One thing I change when I do my teams stats, is the calculation of the H/9, K/9, BB/9 to the normalized inning per game they usual play
In U15 it was 7 innings, so I do those stats based on a 7 inning game, instead of 9

I also do a pitches per inning stat and a pitches per appearance stat, just to see who is being more efficient (or getting better defense behind them in some cases)
And for the innings, I set up a custom number format as #,0 0/0, which shows innings pitched as 9 1/3 or 5 2/3, which makes my brain feel better when looking at stats

 

[Saints Rest]

Final calculations after they won their championship game on Sunday.

My son only went one and a third because on the second batter of the second inning, the batter hit a liner right back thru the box, catching my son right in the mouth. Caused 3 fractured teeth, multiple cuts in his mouth, and a very shook set of parents. I still have a hard time getting the image out of my head, made worse by the fact that I was filling from right behind home plate so I captured the whole thing on video.

He's doing OK now. He has a splint on his upper teeth for at least four weeks (crossing fingers that none of the teeth die). Soft food and no contact sports for 4 weeks.

 

[Jim Ed Rice in HOF]

If it makes you feel any better I got hit with a fly ball in 2nd grade (first day of little league tryouts in the town's first season, coaches hitting fly balls to 2nd graders, didn't end well). One of my front teeth was hanging by a root. They splinted my top teeth with some sort of mouth guard looking thing for probably that length of time. I'm now 52 and while the tooth is a shade or two different from my other teeth (not overly noticeable) it's still there. Every time the dentist's do x-rays they're kind of amazed it's still holding on.

 

[Old Fart Tree]

Sumif is your friend.

 

[santadevil]

Sorry to hear about the liner. That's always been my biggest fear when my some pitches. Back in 11U, when he was actually only 8, we were in Provincials and he took a pitch right to the face, bridge of the nose. He turned his face towards the ball, instead of away. I was the only coach qualified for the team, so I had to stay, while my wife and another parent drove him to the hospital. We were down 7 at the time and close to being eliminated, but the boys rallied back and I had my brother-in-law sending text updates every time we scored and right until the end of the game when they won

Our boy ended up being okay, didn't break his nose, but it sure was huge. He snored like a MOFO that night and his portion of the tournament was over, but he became a good cheerleader for our remaining games. Once we got home, he asked me to throw him BP, so off we went and he stood in like a champ

 

[BroodsSexton]

Sumif is your friend.

More just a fill issue, now.

 

[Saints Rest]

Time for a new Excel question, not directly related to the last, but still sports related.

I made up one tab for my son's soccer schedule and another for his baseball schedule, to use as his "get your shit together checklist" so that the games and practices are headers fo columns (and all the items he needs to bring are the rows). But then I wanted to make a combined tab that would show all games and practices for both sports, sorted by date.

Is there a simple way to rotate (for lack of a better term) an Excel layout so that rows becomes columns and vice versa? Or is there a way to format a table so that the sortable headers are rows not columns?

I suppose I could go back to the two checklist tabs and redo them, but I'm trying to avoid a bunch more typing/cut-and-paste.

 

[luckiestman]

Sounds like all you need to do is ‘transpose’ the table. Watch a short YouTube vid.

View: https://youtu.be/yYVokk0NdiI

 

[Saints Rest]

I couldn't decide if I should start a new thread or reopen an old one, but since there seem to be a lot of Excel threads, and this one is related to my son's baseball team, I figured I would put it here. And this may be more of a math question than an Exel one anyway.

So we are running a Super Bowl squares as a fundraiser for his summer team as discussed above. The plan is that half the proceeds will go the team, and half will pay out to the four winning boxes. But here's the thing: any unsold boxes will be "held" by the team, so the if any one of those empty boxes hits, then the team just adds those winnings to the kitty. So I'm trying to figure out how to calculate expected winnings based on number of boxes sold.

Without the empty boxes issue it's pretty simple: (100 * [$ per box]) - ([# of boxes sold] * [$ per box]). So the more boxes sold, the more money the team gets. Or put another way, once we sell half the boxes, every box sold goes straight to the bottom line.

But an unsold box has a 1% change to hit and add money to the bottom line. The more unsold boxes, the higher the cumulative percentage that the team holds, right? IOW, if there are 25 unsold boxes, we have a 25% chance to hit, correct?

Do I simply multiply the cumulative percentage against the possible winning for each quarter to determine "expected winnings." Is that even the right way to look at it?

If it matters, our current plan is $20/box; $125 for 1st and 3rd quarters; $250 for halftime; and $500 for final score (not end of 4th Q).

So ultimately is the formula [net of sold boxes] + ["expected winnings" of 1st Q] + ["expected winnings" of 2nd Q] + ["expected winnings" of 3rd Q] + ["expected winnings" of final]?? Or is it more of a binomial probability type calculation??

 

[BroodsSexton]

Why don’t you just sell all the boxes.

 

[Saints Rest]

Why don’t you just sell all the boxes.

Well, we are trying to do so, but I wonder if there is any benefit to actually having the team "own" some empty boxes. Since hitting on a box is worth more than selling it to someone who will simply take those profits. IOW, if we sell all boxes, the most we can net is half the sale (thus $1,000). But if we sold only half the boxes and we won all four quarters, we would scoop everything, but again, that's only $1000 gross (50 boxes sold). But if sold say 80 boxes, and managed to hit on a couple boxes, we could possibly take more than $1000.

 

[BroodsSexton]

Well, we are trying to do so, but I wonder if there is any benefit to actually having the team "own" some empty boxes. Since hitting on a box is worth more than selling it to someone who will simply take those profits. IOW, if we sell all boxes, the most we can net is half the sale (thus $1,000). But if we sold only half the boxes and we won all four quarters, we would scoop everything, but again, that's only $1000 gross (50 boxes sold). But if sold say 80 boxes, and managed to hit on a couple boxes, we could possibly take more than $1000.

So if the team wins an unsold box, the proceeds then get "added to the kitty," meaning that it goes back to be divided amongst the winners? So if an unsold box hits, the team still wins 50% of the winnings for that box--just recycled through the other winners.

I'm no Mona Lisa Vito of informal sports betting, but it seems to me that for every box that you don't sell you are reducing the take for the team.

Now, if the team is playing, i.e., it keeps the unsold boxes, that is a different calculation, since there's a likelihood of scooping both halves. But if you play it out--sell 100 boxes at $1 each? Team gets $50. Sell 90 boxes? Team will always get $45 (.5*90) and also expects, that 10% of the time, it will get an extra $45. (i.e., the other half of the pot). That's $4.5, so you're losing there too. ($49.5 EV).

Am I wrong? I'm a lawyer...

 

[Saints Rest]

So if the team wins an unsold box, the proceeds then get "added to the kitty," meaning that it goes back to be divided amongst the winners? So if an unsold box hits, the team still wins 50% of the winnings for that box--just recycled through the other winners.

I'm no Mona Lisa Vito of informal sports betting, but it seems to me that for every box that you don't sell you are reducing the take for the team.

Now, if the team is playing, i.e., it keeps the unsold boxes, that is a different calculation, since there's a likelihood of scooping both halves. But if you play it out--sell 100 boxes at $1 each? Team gets $50. Sell 90 boxes? Team will always get $45 (.5*90) and also expects, that 10% of the time, it will get an extra $45. (i.e., the other half of the pot). That's $4.5, so you're losing there too. ($49.5 EV).

Am I wrong? I'm a lawyer...

What you are wrong about is the kitty is not 50%, it’s 50% of selling all 100 boxes. So in our case boxes are $20, and winnings are $1000 total (125,250,125,500). We’ve sold 58 boxes already so we’ve covered the winnings. Worst case, right now, if we don’t sell any more boxes, and if all winners are people who bought boxes, is we net $160.
But best case, is that the winning boxes all are unsold boxes, in which case we net all the winning plus the $160. But there’s something less than a 42% chance of that happening (due to there being a 42% chance of hitting each quarter).

 

[TenCentBeerNight]

Your problem is somewhat ill-defined as written I think, which makes it hard to work out an answer. If I understand correctly then what you are saying is that :

1) There will be 4 winners (actually 4 payouts, not necessarily 4 distinct winners), with a total payout of $1000. These payouts are fixed *no matter how many boxes are sold*. In event that all boxes are sold, $1000 would represent 50% of the total sales. The 50% figure is not used to determine or scale the actual payouts in the event that not all boxes are sold.

2) If there are unsold boxes then the team keeps any winnings from those boxes. In other words, you don't reallocate the winnings from unsold boxes to the "real" winners.

Assuming that the above are both right, then you can just treat the team as an additional participant that holds a number of boxes that it obtained for free. The payout from selling N boxes is (20*N-1000). The expected win from each box is $1000 / 100 boxes, assuming that it is allowed for a single box to win multiple events. The team will hold (100-N) boxes, which gives the team as a participant an expected additional payout of 10*(100-N). The total expected payout is then (20*N-1000) + 10*(100-N) = 10N, so selling more boxes is always better in expectation.

The worst case is that a single box gets sold and the buyer of that box wins all 4 events, in which case you lose $1000 - $20 = $980. The best case is that there is a single unsold box and that box wins all 4 events, in which case you win 99*$20 = $1980.

Edit for nerds: the above assumes that the 4 events are independent and with each box equally likely to win each. This is not correct, but it seems likely to be true (or close enough) for computing the expectation, particularly if the boxes the team gets are themselves selected randomly. I say "close enough" because I think there is a wrinkle related to the case where the third quarter ends tied, in which case the same box is less likely to win the final score event because ties aren't allowed there. Either way, calculating the variance is probably ugly.

 

[jcaz]

Bump. I need some help. I have what should be a simple problem - I can draw the results I want by hand, but can't make excel do the job.

I have a data set that describes the frequency of interactions between two groups; the data are integers ranging from 0 [no interactions] to 4 [very frequent interactions].
For each of those interactions, I also have a data value that describes the level of coordination; the data again are integers ranging from 0 [no coordination] to 4 [very highly coordinated].
The third data point is the number of observations in each combination of frequency and coordination.

What I would like to do is to create a plot with frequency on the horizontal axis, coordination on the vertical axis and the frequency shown at the appropriate x,y location with circles proportional to the value of the frequency value.

The hand drawing is below. Any help is most appreciated.





I'll spoiler the data set.

Spoiler

 

[saintnick912]

From a quick search I think you want a Bubble Plot in Excel.