Need help with a formula to correctly subtract decimals with a base of 3 not 10

[Gusnk]

Experts -

I handle baseball stats (using excel) for a local league I'm associated with. My problem is when subtracting (to get the correct innings pitched) sometimes the result is incorrect. The formula I am using is the follwoing one INT((INT(R275)-INT(S275)+(MOD(R275,1)-MOD(S275,1))/0.3))+MOD((MOD(R275,1)-MOD(S275,1))/0.3,1)*3/10. Anytime that the decimals (see example 1) are the same the result should be zero, but instead the decimal is a 3. It does not happen when the deciamls are different (with the exception the decimal of 0) (see examples 2) and the result is correct.

Example 1: incorrect results
71.2 - 51.2 = 20.0 this should be the correct result but instead it provides the result as 20.3.

Example 2: correct results
90.0 - 71.1 = 18.2
58.0 - 4.0 = 54.0

Any and all help would be greatly appreciated.

Merry Christmas and Happy New Year to all

Best regards,
Gus

 

[Tetra201]

Try

=DOLLARFR(ROUND(DOLLARDE(R275,3)-DOLLARDE(S275,3),5),3)

 

[6StringJazzer]

Baseball is very eccentric in its notation for innings pitched and it's a pain to do arithmetic on it. I would do this with two Lambda functions that convert from decimal innings to number of outs, and from number of outs to decimal innings. Then convert to outs, do the arithmetic, and convert back to innings. This makes your cell formulas much easier to write and understand.

Innings:
=LAMBDA(Outs,INT(Outs/3)+MOD(Outs,3)/10)

Outs:
=LAMBDA(Innings,INT(Innings)*3+MOD(Innings,1)*10)

$scratch.xlsm
	A	B	C	D
1			Sum	Diff
2	20.0	11.0	31.0	9.0
3	20.1	11.0	31.1	9.1
4	20.2	11.0	31.2	9.2
5	20.0	11.1	31.1	8.2
6	20.1	11.1	31.2	9.0
7	20.2	11.1	32.0	9.1
8	20.0	11.2	31.2	8.1
9	20.1	11.2	32.0	8.2
10	20.2	11.2	32.1	9.0
Innings

Cell Formulas
Range	Formula
C2:C10	C2	=Innings(Outs(A2)+Outs(B2))
D2:D10	D2	=Innings(Outs(A2)-Outs(B2))
B8:B10	B8	=B5+0.1

 

[Gusnk]

Tetra201 & 6StringJazzer -

I want to thank you both of you with helping me find a solution.

Best regards,

Gus