Calculating Innings Pitched

**tnoble17** · 05-23-2010, 11:32 AM

I am having a problem calculating innings pictched. I am using the following formula:

=DOLLARFR(SUMPRODUCT(INT(D69:D73)+DOLLARDE(MOD(D69:D73,1),3)),3)

When I calculate the following with the above formula:

D69 = 7
D70 = 0
D71 = 0.1
D72 = 0.1
D73 = 0.1

I get a total of 7.3, but what I should be getting is 8. There are 3 outs in an inning so in my thoughts I should never see 7.3 it should drive it to the next number as in 8.

**protonLeah** · 05-23-2010, 09:42 PM

Can you explain in words what that formula was intended to do?

1. Your use of the MOD() is in error (twice):
a. the remainder of any number divided by one is always going to be zero
b. it looks like you intended to calc the sum of the range D69:D73 but, without the SUM() all you really have is the first number in the range, vis., 7. Therefore, the remainder of 7/1 is 0.

2. Your use of the INT() is also in error for the same reason as #1.b. above: INT(7)=7
3. What are you trying to calc with the DOLLARx()'s?
4. What do the numbers in the range signify? (counts of what?)

**tnoble17** · 05-29-2010, 10:42 AM

The intent is to total the range from D69:D73. if the toal 7.3 it would roll to 8. If the total was 7.2 then it would show 7.2. Currently the formula (which I got from another person on this forum) provides a total of 7.3 instead of 8. If you deduct 0.1 from one of the numbers it gives me 7.2 as I was hoping for it to. Does this help?

**daddylonglegs** · 05-29-2010, 11:03 AM

You can get some "rounding errors" with those type of formulas. One solution would be to put the SUMPRODUCT part in a ROUND function, rounding to a large number of decimal places, i.e. like this

=DOLLARFR(ROUND(SUMPRODUCT(INT(D69:D73)+DOLLARDE(MOD(D69:D73,1),3)),8),3)

although you can simplify a little and use this for the same result

=DOLLARFR(ROUND(SUMPRODUCT(D69:D73+MOD(D69:D73,1)*7/3),8),3)

....or even

=DOLLARFR(ROUND(SUMPRODUCT(DOLLARDE(D69:D73+0,3)),8),3)

Note that all 3 of these work in Excel 2007 but only the 2nd will work in earlier versions (and you need Analysis ToolPak installed)