# Sum formula rounds up to the next penny

1. ## Sum formula rounds up to the next penny

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!  Register To Reply

2. ## Re: Sum formula rounds up to the next penny

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...
 J K 4 \$ 1.22 1.223 5 \$ 1.22 1.224 6 \$ 1.23 1.225 7 \$ 1.23 1.226 8 \$ 1.23 1.227 9 \$ 1.23 1.228 10 \$ 1.23 1.229 11 \$ 1.23 1.23 12 \$ 1.23 1.231 13 \$ 1.23 1.232 14 \$ 1.23 1.233 15 \$ 1.23 1.234 16 \$ 1.24 1.235  Register To Reply

3. ## Re: Sum formula rounds up to the next penny

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.  Register To Reply

4. ## Re: Sum formula rounds up to the next penny

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!  Register To Reply

5. ## Re: Sum formula rounds up to the next penny

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  Register To Reply

6. ## Re: Sum formula rounds up to the next penny Originally Posted by jacalynl I have check formatting and it is set to accounting with 2 decimal places. Originally Posted by jacalynl 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
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.  Register To Reply

7. ## Re: Sum formula rounds up to the next penny Originally Posted by vlady 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
Thank you! I will try this!  Register To Reply