Integer and Modulus Math

I am doing a spreadsheet for my baseball teams statistics. Each individual game stat is rolled up to a total area for the season. Most of this is done by just summing each individual games stats. However, there is a problem dealing with pitching. If a pitcher goes 6.2 innings in one game, and then 6.2 innings in another game. Excel will roll this up and show 12.4 innings. What I really need is for it to show 13.1 innings, since an inning is constituted by .3 outs. I know how to calculate it in my head, but not sure how to 'code' the right formula in that cell. The process would probably be something like.

1. Sum them all
2. then I need to break apart the number into the integer and fraction part.
3. Then do integer division on the fraction part, so in this case 4 integer division with 3 is 1.
4. Add that result (1) to the integer part (12), to give me 13
5. Then Modulo 3 the fractional part (4), to get 1
6. Then bring the 13 and 1 back together to get 13.1

Anyone know how I could accomplish this?
Thanks

There are probably shorter ways to accomplish this, but have a look at the attached. The first total would be more useful in calculating things like ERA, while the second returns innings as you would expect to see them.

Hello caseyj,

Are the cells you need to sum in a contiguous range as per darkyam's example?

Hello darkyam,

I don't think the formula in B8 will give the correct result in all scenarios, e.g. say B2 = 5 and B3:B6 are blank that formula gives a sum of 5.2 when, clearly, it should be 5.

One method, using DOLLARFR function from Analysis ToolPak add-in

=DOLLARFR(SUMPRODUCT(B2:B6+MOD(B2:B6,1)*7/3),3)

You're right. Please write a book on formulas for those among us who are mortals. Seriously, how do you come up with these?

Not that it matters anymore, but I think I got mine to work with a minor tweak:

Please Login or Register  to view this content.

, confirmed with Ctrl+Shift+Enter.

Thanks! Awesome!

Here is what the spreadsheet looks like:

http://bulldog-baseball.com/2008/BulldogStats2008.htm

Originally Posted by darkyam

Not that it matters anymore, but I think I got mine to work with a minor tweak:

Please Login or Register  to view this content.

, confirmed with Ctrl+Shift+Enter.

The above doesn't quite work.... at least i'm under the assumption that if you were to sum 6.2 & 6.1 the result should be 13.0 not 12.3

Try the below, there is perhaps a better way to do it but this works as far as I can tell. "RANGE" is the range of cells you want to sum and you need to Ctrl+Shift+Enter for the formula to work properly as it's an array formula.

=INT(SUM("RANGE"))+(INT(MOD(SUM("RANGE"),1)/0.3))+(SUM(MOD("RANGE",1))-(INT(MOD(SUM("RANGE"),1)/0.3))*0.3)

how do you add them from multiple sheets? i want make space a1 on sheet1 add up IP from a2 on sheet1:sheet3 and show it as the proper IP format.