Reverse Calculation

I created a following formula to convert the number of balls bowled in a match to a number of overs in that match.

Logic: 1 over = 6 balls

Whereas “bb” is the cell reference wherein we insert number of balls bowled in a match.

Formula =INT(bb/6)+(bb/6-INT(bb/6))*6/10

The formula works fine to calculate number of overs accurately i.e. 36 balls equivalent to 6 overs similarly if we put 39 balls it converts to 6.3 Overs

Now I want a formula that perform a reverse calculation to calculate number of balls for any number of overs (based on the same logic that 1 over =6 balls)

This maybe:

=10*A1-4*INT(A1)

Also, first formula is same as: =2/5*INT(B1/6)+B1/10

What if someone enter 1,7 for example?
You should get some indicator that number is wrong... Change Error in something you find usefull:

=IF(FLOOR(10*(A1-INT(A1));6)>0;"Error";10*A1-4*INT(A1))

hmmm.. I can't figure out why formula works for numbers up to 4,5 and not further :-S

For 4,6; 5,6; 6,6; 7,6; 8,6 etc FLOOR should be 0 (therefore result Error)

maybe just
=IF(A2-INT(A2)>0.5,"error",10*A2-4*INT(A2))

Yes.. I was thinking about it at the first.. But then I realize that 0,42 would give FALSE while ti rather need to be TRUE...

It will also my FLOOR formula works but now I still can't figure out why ti's not working for upper numbers

why would anyone knowing cricket put 0.42 come to that >0.5 ?

I don't even know that this is cricket and even less it's rules XD

well it is so
=IF(A2-INT(A2)>0.5,"error",10*A2-4*INT(A2)) should do since
2.5 is 2 overs 5 balls =17

or if you really need to make sure no one puts 1.42
=IF(OR(A2-INT(A2)>0.5,ROUND(A2-INT(A2),1)<>ROUND(A2-INT(A2),3)),"error",10*A2-4*INT(A2))
now im just following on from whats there coz im sure donkyote has some better cricket functions

If you're using lots of calcs for base 6 then IMO the likes of DOLLARDE / DOLLARFR are very useful
(worth activating the Analysis ToolPak Add-In for those running versions pre XL2007)

For balls to overs:

=DOLLARFR(balls/6,6)

For overs to balls

=DOLLARDE(overs,6)*6

In terms of approaches that avoid ATP dependency

For balls to overs:

=INT(balls/6)+MOD(balls,6)*10

For overs to balls:

=INT(overs)*6+MOD(overs,1)*10

(you might need to Round)