Rounding off Formula.

  • Thread starter Thread starter koji
  • Start date Start date
K

koji

I have a sample here in CELL-A1 with the number 16

and in CELLs (see below)that all add up to 16.
B1-5.05
B2-6.75
B3-4.20

Now the thing is I would actually like to have a formula
that can do something like this.

B1-5.00 (it would loose .05)
B2-6.75 (left alone since its already .75)
B3-4.25 (it would gain .5 to add into .25 since .20 is
greater than .05)

moving the numbers accordingly so that it still adds up to
16 and keeps the numbers ... in .25 .50 .75 etc.only.

Any help would be greatly appreciated. Thank you very
much. Koji
 
Hi Koji
You can use the following formula to convert the numbers to multiples of 0.25

=ROUND(A1*4,0)/4 or =MROUND(A1,0.25) (requires analysis toolpak

Or you can use this to round the numbers and add them up in one step

=SUMPRODUCT(ROUND(A1:A3*4,0)/4

I not certain that they will always add up to the same value as before rounding. What if they didn't add up to a multiple of 0.25 in the first place

You cannot get the numbers to round off within the cells they are already in. That would require a VBHA program

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- koji wrote: ----

I have a sample here in CELL-A1 with the number 1

and in CELLs (see below)that all add up to 16
B1-5.0
B2-6.7
B3-4.2

Now the thing is I would actually like to have a formula
that can do something like this

B1-5.00 (it would loose .05
B2-6.75 (left alone since its already .75
B3-4.25 (it would gain .5 to add into .25 since .20 is
greater than .05

moving the numbers accordingly so that it still adds up to
16 and keeps the numbers ... in .25 .50 .75 etc.only

Any help would be greatly appreciated. Thank you very
much. Koj
 
Hi!

There is no way you can guarantee that the rounded numbers will add u
to the same total as the unrounded numbers.

We've all heard of rounding errors, haven't we...

It can be close, and the larger the group of numbers being rounded th
better the chances their changes will cancel each other. But n
guarantees.

Al
 
Back
Top