Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How to count innings pitched?
Hi all,
I'm new to the forum. Could anybody help me to figure out how to count innings pitched in Excel? Let's say I'm counting the innings pitched in the A1 column and individual games are listed in B1:B10. I've found a couple of tips in the internet, but they don't seem to work. To those who are not familiar with how innings pitched in baseball are calculated, they go by retired batters so two complete innings and one retired batter in the third inning amount to 2.1 innings pitched. So basically I need Excel to roundup to the next rounded number after .2 e.g. 1.2 + 1.1 = 3.0 |
#2
|
|||
|
|||
Quote:
Could you post an example workbook of wht you have so far? Some manually entered expected results will help too. |
#3
|
|||
|
|||
Quote:
In baseball, innings pitched are calculated by the amount of batters the pitcher has retired in one inning. He needs to retire three (3) batters to end the inning. So one retired batter amounts 0.1 innings pitched and 3 retired batters to 1.0. So when a pitcher pitches 6.1 innings in Game 1 and 5.2 innings in Game 2, he has pitched a total of 12.0 innings in 2 games. Does that help? Normally 6.1+5.2 = 11.3, but in this case it should be 12.0 and so on… The result should look like: ---A-------------------- B 1 12.0 (B1+B2)----- 6.1 2-----------------------5.2 |
#4
|
|||
|
|||
Quote:
Does =ROUNDUP(SUM(B1:B2),0) in A1 (with 6.1 in B1 and 5.2 in B2) do the trick? |
#5
|
|||
|
|||
Quote:
Game 1: 6.1 Game 2: 5.2 Game 3: 4.2 Game 4: 5.2 Should amount to 22.1 innings pitched |
#6
|
|||
|
|||
Quote:
So is there a rule that states which should be rounded up and which should not? i.e. if the total ends in .1 it should stay as that but if it ends in .2 then it should be rounded up? |
#7
|
|||
|
|||
Quote:
Basically .3 should already be the next .0. You know what I mean? 12.3 - 13.0 15.3 - 16.0 etc. e.g. 15.2 + 0.1 = 16.0 15.2 + 0.2 = 16.1 Last edited by tomcat82 : September 12th 12 at 10:46 AM |
#8
|
|||
|
|||
Quote:
I think maybe a running score is needed. It could be hidden so you only get the latest score, but to save writing a very long nested formula to give the result you need I think that's the best approach. So, taking your example earlier, what would be the desired results after each game? Game 1: 6.1 = ? Game 2: 5.2 = ? Game 3: 4.2 = ? Game 4: 5.2 = ? |
#9
|
|||
|
|||
Quote:
Found the solution already: =DOLLARFR(ROUND(SUMPRODUCT(B2:B40+MOD(B2:B40,1)*7/3),8),3) |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to count innings pitched?
On Wed, 12 Sep 2012 08:27:47 +0000, Spencer101 wrote:
tomcat82;1605414 Wrote: Can't upload files at work, but let me try to explain it... In baseball, innings pitched are calculated by the amount of batters the pitcher has retired in one inning. He needs to retire three (3) batters to end the inning. So one retired batter amounts 0.1 innings pitched and 3 retired batters to 1.0. So when a pitcher pitches 6.1 innings in Game 1 and 5.2 innings in Game 2, he has pitched a total of 12.0 innings in 2 games. Does that help? Normally 6.1+5.2 = 11.3, but in this case it should be 12.0 and so onÂ… The result should look like: ---A-------------------- B 1 12.0 (B1+B2)----- 6.1 2-----------------------5.2 Does *=ROUNDUP(SUM(B1:B2),0)* in A1 (with 6.1 in B1 and 5.2 in B2) do the trick? It might as long as you only add 2 games together... If you add 3 that are 1.2, 1.2 and 1.2, you approach yields 4, but the answer should be 5. I'd do something a little more complicated, along the lines of splitting the screwy numbering scheme that baseball uses into 2 more columns. B1 = int(a1) C1 = (a1-b1)*10 B{n} = sum(b1:b{n-1}) C{n} = int(sum(C1:C{n-1})/3) D{n} = mod(sum(c1:c{n-1}),3)/10 innings pitched = B{n}+C{n}+D{n} Can probably be simplified a bit, but it's a start. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count duplicats, display incremental count, restart count at changein value | Excel Programming | |||
Converting Baseball Innings when Summed | Excel Discussion (Misc queries) | |||
Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
formatting for innings pitched | Excel Discussion (Misc queries) | |||
Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions |