Working out averages for a baseball spreadsheet

[Stepsinpairs]

Hello,

(using excel 2007, windowsXP)

I have created an in-depth baseball spreadsheet detailing at-bats with things such as left or right-handed pitchers, runners on base, pitch type, day or night game and lots more.
The main problem I'm having is working out a batting average in specific situations.

For example if I want to know my batting average against left-handed pitchers;

Pitcher type is in column N (Right or Left)
End result is in column AD (no hit, single, double, triple, home-run, walk, IBB, SacB, SacF)
Can anybody help with what formula I need to use to work out my average against left-handers?

I have a calculation for counting the number of times I have faced left-handers; =COUNTIF(Batting!N:N,"L")
But I don't know how to calculate the hits against only lefties and not righties too.

Sorry if my explanation isn't great! It would help if you know the calculation for baseball batting averages so I don't have to complicate it further with an explanation

Thanks!

 

[par60056]

Welcome to the forum.

Isn't it a bit more complicated than that? You don't want just how many times the player faced a lefty or righty, you need to know if the at bat counts. so you need to interpret the end result to eliminate walks, IBB, hit by pitch, sacb and sacf.

I would start by adding a columns CountAtBat and OnBase (these should be either 0 or 1) facing left hander is =countifs(Batting!N:N,"L",CountAtBat,1)
lefty batting average=countifs(Batting!N:N,"L",CountAtBat,1,OnBase,1)/countifs(Batting!N:N,"L",CountAtBat,1)

 

[Stepsinpairs]

Hi Par thanks for your reply! I've just been working on it and with a few tweaks to what you suggested I've managed to work it out and got the averages to all the scenarios I wanted.
Really appreciate your help!

 

[par60056]

Glad that worked. I didn't expect it to be perfect but hoped it sent you in the right direction.

Good job and thanks for the feedback