Help please... Simple calculation - with unexpected results

[Guest]

I am getting unexpected results from a simple multiplication formula. Column
A contains rates, Column B contains billing discounts and Col C calculates
the billing $.

For example: Col A = 100, Col B = .44 and Column C calculation is: a2*(
50*37.5*0.85)*b2
Both Column B and C display only 2 decimals.

The answer that comes out is $70,492.79 but I'm expecting $70,125. It
appears to be calculating at 7 decimal places but should only be calculating
at 2. When I enter the actuals numbers into a function (100*1593.75*.44)
the answers comes out right (70,125).

Am I missing something basic? I have done a ton of formulas but have not
come across this before.

 

[Pete_UK]

I get the answer that you expect (Excel 2000). Check what is in cells
A2 and B2 - if these contain formulae then they may result in values
like 100.2 or .442, but your formatting does not allow you to see the
extra digits. To ensure that your formula returns the correct result,
you could amend it thus:

=ROUND(A2,0)*50*37.5*0.85*ROUND(B2,2)

I've removed the brackets around the numbers as they are not needed.

Hope this helps.

Pete

 

[Bernard Liengme]

There is a difference between what is STORED (for example 0.4396) and what
is DISPLAYED (example 0.44)
You can EITHER
1) use Tools | Options , open Calculations tab and check Precision as
Displayed
(but be aware it will affect other workbook that you open)
OR
2) tailor the formula to 2 decimals as in =ROUND(A2,2)*
50*37.5*0.85*ROUND(B2,2)
best wishes

 

[Ron Rosenfeld]

I am getting unexpected results from a simple multiplication formula. Column
A contains rates, Column B contains billing discounts and Col C calculates
the billing $.

For example: Col A = 100, Col B = .44 and Column C calculation is: a2*(
50*37.5*0.85)*b2
Both Column B and C display only 2 decimals.

The answer that comes out is $70,492.79 but I'm expecting $70,125. It
appears to be calculating at 7 decimal places but should only be calculating
at 2. When I enter the actuals numbers into a function (100*1593.75*.44)
the answers comes out right (70,125).

Am I missing something basic? I have done a ton of formulas but have not
come across this before.

Excel calculates to 15 decimal places. Unless you check Precision as Displayed
under Tools/Options, the formatting will not change that.

Given:

A2: 100.004987921697
B2: 0.44228564109933

you would see 100.00 in A2, 0.44 in B2, and get the results you report in C2.

If you only want to calculate using two decimal places, you must either use
Precision as Displayed (which may have unexpected results elsewhere in your
workbook), or round the factors in A2:B2 to two decimal places.

You can either use the ROUND function within A2 and B2, or use it in C2:

=ROUND(A2,2)*(50*37.5*0.85)*ROUND(B2,2)

depending on what you want.

If you will only want two decimal precision in A2 and B2, then I would do the
rounding there.
--ron

 

[Guest]

Sorry, I forgot to add that .44 is the result of a calculation in Col B. Col
B in the example below is 23/52. This is how I figure the 7 decimals is
coming into play, but not sure how when I'm only displaying 2.

 

[Guest]

Yes! The rounding works perfectly! Thanks so much.

 

[Guest]

Thanks for all the info, I hadn't realized the underlying decimal situation.
Never came across it before. Anyways, using Round is the solution.

Everyone has been very helpful. Thanks again.

 

[Pete_UK]

Glad to be of help - thanks for feeding back.

Pete