making numbers divide exactly without rounding

  • Thread starter Thread starter Tina
  • Start date Start date
T

Tina

Okay, I'll try to explain what my problem is.

I have four columns to add. Also, each row is added.
Kind of like cross checking?

Well, one of the numbers in the total column is divided
by three to make up one of the numbers in each of the
other three columns. However, when it divides, it
doesn't do anything with the extra penny that is left
over. For example the number being divided is $8287.96.
It puts $2762.65 in each of the columns, but actually one
of the columns needs to be $2762.66.


$141,009.00 $1,436.01 $1,158.56
$6,286.80
$147,295.80 $1,436.01 $1,158.56 =$149,890.37
$2,762.65 $2,762.65 $2,762.65 = $8,287.96
$150,058.45 $4,198.66 $3,921.21 =$158,178.33
$2,273.89 $2,273.89 $2,273.89 = $6,821.67
$152,332.34 $6,472.55 $6,195.10 =$165,000.00

(I tried to show how my page is set up. I hope it comes
out right.)

The right hand column totals $165,000.00. Since the
$8287.96 didn't divide quite right, the penny that should
have been in the 1st column didn't get added. The total
for column 1 is one penny short. It should be
$152,332.35. If the total for column 1, 2, and 3 are
added together they actually equal $164,999.99. However
when I add them together in Excel, it prints $165,000.00.

The row total and the column total must equal and I'm not
even sure how to set it up to check that, either.

I don't have a clue what to do about this, and I'm not
even sure that I have explained myself well enough for
anyone to help me.

Thank you for your help
Tina
 
Tina,

I suggest using ROUNDUP & ROUNDDOWN functions as follows:-

In the first column put-

=ROUNDUP(8287.96/3,2)

In the other two columns put-

=ROUNDDOWN(8287.96/3,2)

This will give in that row

$2,762.66 $ 2,762.65 $2,762.65

and your totals should now be correct.

regards,

JohnI
 
This presumes the particular values that the OP mentioned. A more
general solution would rouond the first two numbers and calculate the
third by subtraction.

Jerry
 
Jerry,

Thanks for the info.
Yeah, as sometimes happens, I thought of that idea after I sent my solution.

Glad to hear your thoughts on this.

regards,

JohnI
 
Back
Top