how can i make 0.6 = 1 in excel
 

I'm trying to produce averages for my local cricket team (UK) using Excel. On
occasions, a bowler does not complete an 'Over' so may end up with an entry
of 7.3. On anther occasion, he has an entry of 6.4. Now, in normal addition
7.3 + 6.4 = 13.7, however in cricket there are only 6 balls in an over, so
the total would be 14.1.

Does anyone have ideas on a formular for this type of equation?

Thanks

Barfly

how can i make 0.6 = 1 in excel
 

=roundup(total overs,0)

"Barfly" wrote:

I'm trying to produce averages for my local cricket team (UK) using Excel. On
occasions, a bowler does not complete an 'Over' so may end up with an entry
of 7.3. On anther occasion, he has an entry of 6.4. Now, in normal addition
7.3 + 6.4 = 13.7, however in cricket there are only 6 balls in an over, so
the total would be 14.1.

Does anyone have ideas on a formular for this type of equation?

Thanks

Barfly

how can i make 0.6 = 1 in excel
 

ooops sorry wrong answer !

"claude jerry" wrote:

=roundup(total overs,0)

"Barfly" wrote:

I'm trying to produce averages for my local cricket team (UK) using Excel. On
occasions, a bowler does not complete an 'Over' so may end up with an entry
of 7.3. On anther occasion, he has an entry of 6.4. Now, in normal addition
7.3 + 6.4 = 13.7, however in cricket there are only 6 balls in an over, so
the total would be 14.1.

Does anyone have ideas on a formular for this type of equation?

Thanks

Barfly

how can i make 0.6 = 1 in excel
 

Overs DecOvers
7.3 7.5
6.4 6.6667

formula in B2 is
=ROUNDDOWN(A2,0)+MOD(A2,1)*10/6

the rounddown gets the number of completed overs
the mod gets the number of balls, which I then multiply by 10/6 to translate
into normal decimals

--
Allllen


"Barfly" wrote:

I'm trying to produce averages for my local cricket team (UK) using Excel. On
occasions, a bowler does not complete an 'Over' so may end up with an entry
of 7.3. On anther occasion, he has an entry of 6.4. Now, in normal addition
7.3 + 6.4 = 13.7, however in cricket there are only 6 balls in an over, so
the total would be 14.1.

Does anyone have ideas on a formular for this type of equation?

Thanks

Barfly

how can i make 0.6 = 1 in excel
 

I should have gone a step further and shown you how to get back to overs and
balls again:

A B
1 Overs DecOvers
2 7.3 7.5
3 6.4 6.667
4
5 14.1 14.167

formula in B2 is =ROUNDDOWN(A2,0)+MOD(A2,1)*10/6
formula in B5 is =SUM(B2:B3)
formula in A5 is = =ROUNDDOWN(B5,0)+MOD(B5,1)*6/10
--
Allllen


"Allllen" wrote:

Overs DecOvers
7.3 7.5
6.4 6.6667

formula in B2 is
=ROUNDDOWN(A2,0)+MOD(A2,1)*10/6

the rounddown gets the number of completed overs
the mod gets the number of balls, which I then multiply by 10/6 to translate
into normal decimals

--
Allllen


"Barfly" wrote:

I'm trying to produce averages for my local cricket team (UK) using Excel. On
occasions, a bowler does not complete an 'Over' so may end up with an entry
of 7.3. On anther occasion, he has an entry of 6.4. Now, in normal addition
7.3 + 6.4 = 13.7, however in cricket there are only 6 balls in an over, so
the total would be 14.1.

Does anyone have ideas on a formular for this type of equation?

Thanks

Barfly

how can i make 0.6 = 1 in excel
 

Assuming your sum total is in G9
=IF(MOD(G9,1)0.5,INT(G9)+1+MOD(G9,1)-0.6,G9)

"claude jerry" wrote:

ooops sorry wrong answer !

"claude jerry" wrote:

=roundup(total overs,0)

"Barfly" wrote:

I'm trying to produce averages for my local cricket team (UK) using Excel. On
occasions, a bowler does not complete an 'Over' so may end up with an entry
of 7.3. On anther occasion, he has an entry of 6.4. Now, in normal addition
7.3 + 6.4 = 13.7, however in cricket there are only 6 balls in an over, so
the total would be 14.1.

Does anyone have ideas on a formular for this type of equation?

Thanks

Barfly

how can i make 0.6 = 1 in excel
 

Excellent, that's just what i was looking for

Thanks

"Allllen" wrote:

I should have gone a step further and shown you how to get back to overs and
balls again:

A B
1 Overs DecOvers
2 7.3 7.5
3 6.4 6.667
4
5 14.1 14.167

formula in B2 is =ROUNDDOWN(A2,0)+MOD(A2,1)*10/6
formula in B5 is =SUM(B2:B3)
formula in A5 is = =ROUNDDOWN(B5,0)+MOD(B5,1)*6/10
--
Allllen


"Allllen" wrote:

Overs DecOvers
7.3 7.5
6.4 6.6667

formula in B2 is
=ROUNDDOWN(A2,0)+MOD(A2,1)*10/6

the rounddown gets the number of completed overs
the mod gets the number of balls, which I then multiply by 10/6 to translate
into normal decimals

--
Allllen


"Barfly" wrote:

I'm trying to produce averages for my local cricket team (UK) using Excel. On
occasions, a bowler does not complete an 'Over' so may end up with an entry
of 7.3. On anther occasion, he has an entry of 6.4. Now, in normal addition
7.3 + 6.4 = 13.7, however in cricket there are only 6 balls in an over, so
the total would be 14.1.

Does anyone have ideas on a formular for this type of equation?

Thanks

Barfly