Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide and round
if any one can help
e.g i have $100 in A1 and like to divide it by 12 and round the figure to 3 decimal place and put the result in B1 to M1 and then total of (B1:M1)in N1 the result is $99.996 how can i make it $100 without manually changing the figures in B1:M1 Regards |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide and round
=ROUND(SUM(B1:M1),0)
"osaka78" wrote: if any one can help e.g i have $100 in A1 and like to divide it by 12 and round the figure to 3 decimal place and put the result in B1 to M1 and then total of (B1:M1)in N1 the result is $99.996 how can i make it $100 without manually changing the figures in B1:M1 Regards |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide and round
What you describe will add up to $100 in N1 unless you are doing something
else other than displaying the division results to 3 decimal places. To do this format the cells to 3 decimal places and all should be OK Mike More info "osaka78" wrote: if any one can help e.g i have $100 in A1 and like to divide it by 12 and round the figure to 3 decimal place and put the result in B1 to M1 and then total of (B1:M1)in N1 the result is $99.996 how can i make it $100 without manually changing the figures in B1:M1 Regards |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide and round
More info. I suspect your using
=ROUND($A$1/12,3) For the division displayed to 3 decimal places? Mike "osaka78" wrote: if any one can help e.g i have $100 in A1 and like to divide it by 12 and round the figure to 3 decimal place and put the result in B1 to M1 and then total of (B1:M1)in N1 the result is $99.996 how can i make it $100 without manually changing the figures in B1:M1 Regards |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide and round
It actually comes out 100 in my Excel97, but perhaps this will help...
=TEXT(SUM(B1:B12),"0")*1 Vaya con Dios, Chuck, CABGx3 "osaka78" wrote: if any one can help e.g i have $100 in A1 and like to divide it by 12 and round the figure to 3 decimal place and put the result in B1 to M1 and then total of (B1:M1)in N1 the result is $99.996 how can i make it $100 without manually changing the figures in B1:M1 Regards |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide and round
yes in B1:M1 is 8.333
"Mike H" wrote: More info. I suspect your using =ROUND($A$1/12,3) For the division displayed to 3 decimal places? Mike "osaka78" wrote: if any one can help e.g i have $100 in A1 and like to divide it by 12 and round the figure to 3 decimal place and put the result in B1 to M1 and then total of (B1:M1)in N1 the result is $99.996 how can i make it $100 without manually changing the figures in B1:M1 Regards |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide and round
I don't know what you are actually doing but using 'Round' actually changes
the number ( as do TRUNC & INT etc) so when you add the numbers up again all the bits after 8.333 are gone. Unles you are using ROUND for a reason change the way you are doing it to:- =A1/12 and display it to 3 decimal places by selecting the cell and then Format|cell|number select 3 decimal places Doing it that way only changes the way the number is displayed, the underlying number doesnt change and will add up correctly. Mike "osaka78" wrote: yes in B1:M1 is 8.333 "Mike H" wrote: More info. I suspect your using =ROUND($A$1/12,3) For the division displayed to 3 decimal places? Mike "osaka78" wrote: if any one can help e.g i have $100 in A1 and like to divide it by 12 and round the figure to 3 decimal place and put the result in B1 to M1 and then total of (B1:M1)in N1 the result is $99.996 how can i make it $100 without manually changing the figures in B1:M1 Regards |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide and round
You won't get arithmetic like that to work out exactly, as $100/12 cannot be
represented exactly in fixed-point binary, any more than it can in fixed-point decimal. You can get it closer if in B1 to M1 you don't put the rounded number =ROUND($A1/12,3), but instead just use =$A1/12 and format the cell to display to 3 decimal places. -- David Biddulph "osaka78" wrote in message ... if any one can help e.g i have $100 in A1 and like to divide it by 12 and round the figure to 3 decimal place and put the result in B1 to M1 and then total of (B1:M1)in N1 the result is $99.996 how can i make it $100 without manually changing the figures in B1:M1 Regards |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide and round
EXACTLY can you help me in how to automaticlly add the difference in the M1
i.e 8.333 in B1:L1 and 8.337 in M1 "Mike H" wrote: I don't know what you are actually doing but using 'Round' actually changes the number ( as do TRUNC & INT etc) so when you add the numbers up again all the bits after 8.333 are gone. Unles you are using ROUND for a reason change the way you are doing it to:- =A1/12 and display it to 3 decimal places by selecting the cell and then Format|cell|number select 3 decimal places Doing it that way only changes the way the number is displayed, the underlying number doesnt change and will add up correctly. Mike "osaka78" wrote: yes in B1:M1 is 8.333 "Mike H" wrote: More info. I suspect your using =ROUND($A$1/12,3) For the division displayed to 3 decimal places? Mike "osaka78" wrote: if any one can help e.g i have $100 in A1 and like to divide it by 12 and round the figure to 3 decimal place and put the result in B1 to M1 and then total of (B1:M1)in N1 the result is $99.996 how can i make it $100 without manually changing the figures in B1:M1 Regards |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide and round
Hi
It's because all cells in range B1:M1 are divided down, i.e. ROUND(0.33333333333333,3)=0.333. To avoid this, some cells must be rounded to 0.334. One possible solution: B1=ROUND($A$1/12,3) C1=ROUND(($A$1-SUM($B$1:B$1))/(12-COLUMN()+2),3) , and copy C1 to range C1:M1 Now the sum in N1 will be exactly 100 -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "osaka78" wrote in message ... if any one can help e.g i have $100 in A1 and like to divide it by 12 and round the figure to 3 decimal place and put the result in B1 to M1 and then total of (B1:M1)in N1 the result is $99.996 how can i make it $100 without manually changing the figures in B1:M1 Regards |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide and round
Osaka,
I still believe it's fundamentally incorrect to change a number and then expect it to behave as if it hadnot been changed. However, try this in M1 =ROUND($A$1/12,3)+(A1-(L1*12)) M1 will now be 8.337 N1 will now add up to 100!! Mike "osaka78" wrote: EXACTLY can you help me in how to automaticlly add the difference in the M1 i.e 8.333 in B1:L1 and 8.337 in M1 "Mike H" wrote: I don't know what you are actually doing but using 'Round' actually changes the number ( as do TRUNC & INT etc) so when you add the numbers up again all the bits after 8.333 are gone. Unles you are using ROUND for a reason change the way you are doing it to:- =A1/12 and display it to 3 decimal places by selecting the cell and then Format|cell|number select 3 decimal places Doing it that way only changes the way the number is displayed, the underlying number doesnt change and will add up correctly. Mike "osaka78" wrote: yes in B1:M1 is 8.333 "Mike H" wrote: More info. I suspect your using =ROUND($A$1/12,3) For the division displayed to 3 decimal places? Mike "osaka78" wrote: if any one can help e.g i have $100 in A1 and like to divide it by 12 and round the figure to 3 decimal place and put the result in B1 to M1 and then total of (B1:M1)in N1 the result is $99.996 how can i make it $100 without manually changing the figures in B1:M1 Regards |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide and round
Hi again
You can do it with a single formula for range B1:M1 too: B1=ROUND((2*$A$1-SUM($A$1:A$1))/(12-COLUMN()+2),3) and copy to B1:M1 -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide and round
Hi,
=100.008 on my machine I think the issue here is that the OP doesn't want to change the 8.333 in cells B1 - M1 and by doing it using ROUND anything after .333 is lost yet still expects it to add up to the original 100. I'm minded of the person asking for directions to the Whitehouse and being told that you shouldn't really start from here. Mike "Arvi Laanemets" wrote: Hi again You can do it with a single formula for range B1:M1 too: B1=ROUND((2*$A$1-SUM($A$1:A$1))/(12-COLUMN()+2),3) and copy to B1:M1 -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide and round
Hi
"Mike H" wrote in message ... Hi, =100.008 on my machine Then there went something wrong in your calculations - I get exactly 100 in N1 [N1=SUM(B1:M1)]. The formula is self-adjusting. I think the issue here is that the OP doesn't want to change the 8.333 in cells B1 - M1 and by doing it using ROUND anything after .333 is lost yet still expects it to add up to the original 100. It was somewhere said, that happiness is when what you want mathces with what you get :-))) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide and round
The OP shouldn't, of course, have 8.333 in B1 to M1. He should have =100/12
(which he can, if he wishes, *display* to 3 decimal places). The problem might, of course, arise if one were to display to 3 decimal places and select the "Precision as displayed" option, but the latter option is not usually advisable. -- David Biddulph "Mike H" wrote in message ... Hi, =100.008 on my machine I think the issue here is that the OP doesn't want to change the 8.333 in cells B1 - M1 and by doing it using ROUND anything after .333 is lost yet still expects it to add up to the original 100. I'm minded of the person asking for directions to the Whitehouse and being told that you shouldn't really start from here. Mike "Arvi Laanemets" wrote: Hi again You can do it with a single formula for range B1:M1 too: B1=ROUND((2*$A$1-SUM($A$1:A$1))/(12-COLUMN()+2),3) and copy to B1:M1 |
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
Divide and round
thanx alot this is exactly what i am looking for
"Arvi Laanemets" wrote: Hi again You can do it with a single formula for range B1:M1 too: B1=ROUND((2*$A$1-SUM($A$1:A$1))/(12-COLUMN()+2),3) and copy to B1:M1 -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Format divide by 10 / single digit view: Custom? Text(Round( | Excel Discussion (Misc queries) | |||
divide by zero | Excel Worksheet Functions | |||
Divide by Zero | Excel Worksheet Functions | |||
Divide one row over other row I dont wont to divide one number | Excel Discussion (Misc queries) | |||
How do I ROUND() round off decimals of a column dataset? | Excel Worksheet Functions |