| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Make table query will work in datasheet view but will not make tab | Excel Discussion (Misc queries) | |||
| How do I make make comment format default? | Excel Discussion (Misc queries) | |||
| make macros usable! make instructions in plain english !!!! | Excel Worksheet Functions | |||
| Excel templates. Wanna make excel look like regular blue lined pap | Excel Discussion (Misc queries) | |||
| how do I make make my hyperlinks show the email address they are . | Excel Discussion (Misc queries) | |||
Linear Mode
