I have a sum formula that adds 3 different cells from 3 different columns to reach my total profit. The sum total will occasionally be rounded up one cent. I have check formatting and it is set to accounting with 2 decimal places. The formula is the same in each row as well as the format of each cell. Why would the formula occasionally round up? Help please!
Hi and welcome to the forum
Up to .004, it will round down, after that, it will round up. It is not actually rounding the number, it is just displaying it to that format, the underlying value remains unchanged
The values on the left are formatted to accounting, the values on the right show the actual cell contents...
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Excel maintains the maximum possible precision for real numbers, even if you are only displaying two decimal places. The amount displayed will be rounded to the nearest cent, but rounding errors can cause a SUM to round to a result that is different than if you add up what is displayed. Is that what you are seeing?
The way to manage that is to explicitly round each individual amount from the 3 different cells.
See attached for a simple example to illustrate the problem and solution.
Thanks for the quick replies!
Yes, The amount displayed that is rounded to the nearest cent, is resulting in errors - a result that is different than if you add up what is displayed.
I will try to round each individual amount from the 3 different cells.
Thank you all!
you can also use the trunc funtion
=trunc(yourformula,2) -> this will drop the other decimal retaining 2 decimal points only. no rounding down/up
.122 -> .12
.125 -> .12
.129 -> .12
I think people forget the word "THANK YOU!!!!" Do you still know it???
There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "
Regards,
Vladimir
Originally Posted by jacalynl
Simply formatting to display 2 decimal places only affects the appearance of values. It does not affect the precision of the actual value. (Unless the "Precision As Displayed" option is set, which I do not recommend.)
Moreover, most non-integers are not represented exactly, due to the way that Excel stores numbers and performs arithmetic. Consequently, small differences can creep into the results of arithmetic in surprising ways. For example, IF(10.1 - 10 = 0.1, TRUE) returns FALSE(!).
Consequently, you should explicitly round non-integer arithmetic when you expect the result to be accurate to a specific number of decimal places. Usually, that means using the ROUND function. For example, IF(ROUND(10.1 - 10, 1) = 0.1, TRUE) returns TRUE.
Thank you! I will try this!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks