Adding cricket overs - base 6

I've got an array formula to add a column of numbers, some with one decimal place. The complex part is that the decimal isn't 1/10th, it's 1/6th. Cricket overs count 0.1, 0.2, 0.3, 0.4, 0.5, 1.0... and so on. So 0.3 + 0.3 = 1.0

=SUM(INT(J5:J24))+INT(SUM(MOD(J5:J24,1))*10/6)+(MOD(SUM(MOD(J5:J24,1)*10/6),1))*6/10

I have just noticed that sometimes, if the first two numbers both have a point three (eg. 5.3 and 6.3) it shows 11.6 instead of 12. It works every other time.

Anyone have any idea why this happens or how to fix?
Thanks

Hi

Try this. It has to be array entered with the ctrl, shift and enter keys.

=SUM(INT(J5:J24))+INT(SUM(J5:J24-INT(J5:J24))/0.6)+MOD(SUM(J5:J24-INT(J5:J24)),0.6)


rylo

Thanks for the help rylo.

That works but the problem remains. For some reason, 5.3 + 5.3 = 10.6

If you put 5.4 and 5.2, it's says 11, which is correct. But whenever the only decimals are 3s it goes for the old .6

Two other possibilities which don't need to be "array entered"

=INT(SUMPRODUCT(J5:J24*10-INT(J5:J24)*4)/6)+MOD(SUMPRODUCT(J5:J24*10-INT(J5:J24)*4),6)/10

or, if you don't mind using the Analysis ToolPak add-in function DOLLARFR

=DOLLARFR(SUMPRODUCT(J5:J24*10-INT(J5:J24)*4)/6,6)

Hey there,

Have you got a formula to add just 2 different cells? Like F30 says 1.4 and F91 says 1.4 I want it to say 3.2 in another cell.

Obviously I'm talking about overs. Cause on my template I'm trying to add a bowlers 1st innings and also his 2nd innings to have the total overs in the match.

I have this formula for the team total overs and i've fiddled around with the formula but I can't get it to work for just 2 cells. Like the SUMPRODUCT(G4:G9 I just want something easy like B3 + B40 with the rest of what I need in the formula. I don't want to use this symbol :

=INT(SUMPRODUCT(F117:F122*10-INT(F117:F122)*4)/6)+MOD(SUMPRODUCT(F117:F122*10-INT(F117:F122)*4),6)/10

Also to note that these cells are on a different sheet. So i'll be putting in ='1st Test'!F30+'1st Test'!F91 and so on. I just need help with the MOD and INT formula because I'm very new to them and I have no idea on how to type it out on my own.

Thank you Paul.