How do I get a formula total to quit rounding up in Excel?

S

skmarshall

I have a formula in a cell =PRODUCT(H17*A31, 4) and the answer should be
$67.96 but it's coming up $68.00. If I change the number of decimal places,
it only changes the answer is $68.000; I've changed the size of the column
and that doesn't help either. Thanks for your time.
 
D

David Biddulph

What do H17 and A31 contain? Are they formulae or constants? Are you sure
that those cells don't contain figures with more precision than you are
seeing?
 
S

skmarshall

Up to that point I was getting the correct figures. These are the
formulas/constants:
cell E17 (constant) $29,068
cell H17 (formula) =E17/12 (answer $2422.33)
cell A31 (constant) 3
cell D31 =Product(H17*A31,4) (incorrect answer $29068.00)
I just changed the calculator to 4 decimal places and see that H17 could be
$2422.3333. Is that the problem? Is there any way to get the final product
(cell D31) to have the correct answer of $29067.96? Thanks
 
D

David Biddulph

29068 is the *correct* answer for =Product(H17*A31,4), because that is
=(E17/12)*3*4 and that is =E17.
29067.96 would be an *incorrect* answer.

If you want Excel to round your calculations, you have to tell it to do so.
If you want H17 to contain 2422.33, then instead of =E17/12 you may want
=ROUND(E17/12,2)
Another option is to format H17 to 2 decimal places and ask Excel to use
"Precision as Displayed", but I wouldn't recommend that as it will give a
variety of unexpected results if you don't think carefully about what you
haveasked it to do.
 
S

skmarshall

Thank you so much. That worked!!

David Biddulph said:
29068 is the *correct* answer for =Product(H17*A31,4), because that is
=(E17/12)*3*4 and that is =E17.
29067.96 would be an *incorrect* answer.

If you want Excel to round your calculations, you have to tell it to do so.
If you want H17 to contain 2422.33, then instead of =E17/12 you may want
=ROUND(E17/12,2)
Another option is to format H17 to 2 decimal places and ask Excel to use
"Precision as Displayed", but I wouldn't recommend that as it will give a
variety of unexpected results if you don't think carefully about what you
haveasked it to do.
Click to expand...
 
Top