calculation decimal limit

  • Thread starter Thread starter txfirepro
  • Start date Start date
T

txfirepro

Hi all,

I have a question thats driving me bonkers. Maybe its just late and I'
loosing it, or maybe it cant be done.

I have a workbook with two spreadsheets.

Sheet one has list prices, each.

Sheet two has formulas calculating costs that go as follows...

References list price on sheet one, multiplies the discount, adds
tax, and multiplies by the number of items in a box.

Example, lets say I sell a soap thats priced by the gallon. I want t
calculate a 40% discount, multiply that times the 5 gallon or 55 gallo
drum it comes in, and add 7.5% tax.

When I set up the cells, I have them in curency format, two decima
places.

When it does the calculation, the total is allways a few cents highe
that usual.

I suspect that Excel is carrying too many decimals into the calc
rounding the number up.

It displays the final calc in 2 decimal places.

My question, How to get it to calc only two decimal places and displa
the right number?

Thanks in advance,

J
 
YOu probably have something like:

=ROUND(A1*(1-40%)*5*(1+7.5%),2)

which will multiply each term successively using the entire precision
available (15 decimal digits)

So

A1: 17.97
B1: =ROUND(A1*0.6*5*1.075,2) ==> 57.95

Where by hand:

17.97 * 0.6 = 10.782 rounds to 10.78
10.78 * 5 = 53.90 (vs. 53.91 for 10.782*5)
53.90 * 1.075 = 57.9425, which rounds down to 57.94

To simulate the hand calculation:

B1: =ROUND(ROUND(A1*0.6,2)*5*1.075,2) ==> 57.94

Note that you don't have to round each step - multiplying X by 5 will
not create a value in the third decimal place if X is already rounded to
2 decimal places.
 
Back
Top