Divide and round

[osaka78]

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

 

[Toppers]

=ROUND(SUM(B1:M1),0)

 

[Mike H]

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

 

[Mike H]

More info. I suspect your using

=ROUND($A$1/12,3)

For the division displayed to 3 decimal places?

Mike

 

[CLR]

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]

yes in B1:M1 is 8.333

More info. I suspect your using

=ROUND($A$1/12,3)

For the division displayed to 3 decimal places?

Mike

 

[Mike H]

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

 

[David Biddulph]

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.

 

[osaka78]

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

 

[Arvi Laanemets]

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

 

[Mike H]

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

 

[Arvi Laanemets]

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

 

[Mike H]

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]

Hi

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 ))

 

[David Biddulph]

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.

 

[osaka78]

thanx alot this is exactly what i am looking for