Probably a very simple Excel question

Saints Rest

Well-Known Member
Lifetime Member
SoSH Member
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.
Screen Shot 2020-10-20 at 10.42.26 AM.png
 

LogansDad

Member
SoSH Member
Nov 15, 2006
29,053
Alamogordo
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

funky and cold
SoSH Member
Jul 22, 2005
21,613
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.
 
Last edited:

Humphrey

Member
SoSH Member
Aug 3, 2010
3,163
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

Well-Known Member
Lifetime Member
SoSH Member
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.
 

begranter

Couldn't get into a real school
Silver Supporter
SoSH Member
Jul 9, 2007
2,344
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)
 

DJnVa

Dorito Dawg
SoSH Member
Dec 16, 2010
53,840
I'd say the biggest issue isn't EXCEL it's the pitching staff.
 

Saints Rest

Well-Known Member
Lifetime Member
SoSH Member
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?
 

Over Guapo Grande

panty merchant
SoSH Member
Nov 29, 2005
4,459
Worcester
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
 

BroodsSexton

Member
SoSH Member
Feb 4, 2006
12,630
guam
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

Well-Known Member
Lifetime Member
SoSH Member
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

Member
SoSH Member
Sep 29, 2006
780
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

Member
SoSH Member
Feb 4, 2006
12,630
guam
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

funky and cold
SoSH Member
Jul 22, 2005
21,613
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
 

tbrown_01923

Member
SoSH Member
Sep 29, 2006
780
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

Well-Known Member
Lifetime Member
SoSH Member
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.
Screen Shot 2020-10-21 at 8.18.25 AM.png
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

Member
SoSH Member
Feb 4, 2006
12,630
guam
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

Well-Known Member
Lifetime Member
SoSH Member
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!
 

santadevil

wears depends
Silver Supporter
SoSH Member
Aug 1, 2006
6,472
Saskatchestan
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
 
Last edited:

Saints Rest

Well-Known Member
Lifetime Member
SoSH Member
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.

Final Stats.png
 

Jim Ed Rice in HOF

Red-headed Skrub child
SoSH Member
Jul 21, 2005
8,252
Seacoast NH
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.
 

santadevil

wears depends
Silver Supporter
SoSH Member
Aug 1, 2006
6,472
Saskatchestan
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
 

Saints Rest

Well-Known Member
Lifetime Member
SoSH Member
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.
 

Saints Rest

Well-Known Member
Lifetime Member
SoSH Member
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??
 

Saints Rest

Well-Known Member
Lifetime Member
SoSH Member
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

Member
SoSH Member
Feb 4, 2006
12,630
guam
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

Well-Known Member
Lifetime Member
SoSH Member
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

Member
SoSH Member
Jul 11, 2009
521
UK
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.
 
Last edited:

jcaz

Member
SoSH Member
Jun 8, 2009
383
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.



56434

I'll spoiler the data set.
56431