I am trying to sum a group of numbers that have been formatted using the custom format #,###,,;(#,###,,);0

Example: If have a number 9,623,523,456 it will show 9,624 (which is rounded). However, I have several of these numbers that I am summing but when I do so the sum amount (which is also rounded) doesn't add up to the displayed numbers.

Example:

Actual Amounts Formatted If Add up displayed numbers

9,228,356,830 9,228 9228

1,310,231,236 1,310 1310

715,744,602 716 716

203,139,979 203 203

22,209,357 22 22

- 0 0

11,479,682,004 11,480 11479

As you see if I add up the displayed numbers is adds to 11479 but is being rounded to 11480. How can I fix this? I prefer to have the subtotal correct (11480) meaning I would like the sum to stay at 11480 and one of the others numbers to adjust accordingly. Thanks

Example: If have a number 9,623,523,456 it will show 9,624 (which is rounded). However, I have several of these numbers that I am summing but when I do so the sum amount (which is also rounded) doesn't add up to the displayed numbers.

Example:

Actual Amounts Formatted If Add up displayed numbers

9,228,356,830 9,228 9228

1,310,231,236 1,310 1310

715,744,602 716 716

203,139,979 203 203

22,209,357 22 22

- 0 0

11,479,682,004 11,480 11479

As you see if I add up the displayed numbers is adds to 11479 but is being rounded to 11480. How can I fix this? I prefer to have the subtotal correct (11480) meaning I would like the sum to stay at 11480 and one of the others numbers to adjust accordingly. Thanks

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

In some cases, the rounded actual sum might be greater than the sum of the rounded amounts, as in the following example:

10.1 10

10.1 10

10.1 10

10.1 10

10.1 10

10.1 10

10.1 10

10.1 10

10.1 10

10.1 10

_______________

101 100

While in other cases, the sum of the rounded amounts might be greater than the rounded actual sum, as in the following example:

10.6 11

10.6 11

10.6 11

10.6 11

10.6 11

10.6 11

10.6 11

10.6 11

10.6 11

10.6 11

_______________

106 110

You can solve this problem by doing either of the following:

1. Calculate the formatted sum by adding the formatted amounts rather than rounding the actual total, or

2. Follow these steps:

a. First, you need to decide whether adjustment is needed or not. This can be done by comparing (subtracting) the total of the rounded (formatted) amounts to the actual total (rounded). So in the above example, you will be comparing 11479 to the sum of the rounded amounts (11480).

b. If both values are *not* equal, then you need to decide whether you will increment or decrement the rounded amounts. If the sum of the rounded amounts is greater than the rounded actual sum, then you need to decrement a number of rounded amounts equal to the difference between both sums. Similarly, if the rounded sum of the actual amounts is greater than the sum of the formatted amounts, then you need to increment a number of rounded amounts equal to the difference between both sums.

_______________

Nayer Naguib

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Programming

From novice to tech pro — start learning today.

Experts Exchange Solution brought to you by

Enjoy your complimentary solution view.

Get every solution instantly with Premium.
Start your 7-day free trial.

* Calculate the actual total (11,479,682,004 in your example).

* Calculate the formatted total (11,480).

* If formatted_total = (int)(actual_total / 1000000) then everything's fine. Note that int (or an equivalent function) should be used to remove the fraction part rather than round the number to the nearest integer.

* If formatted_total > (int)(actual_total / 1000000) then do the following:

* Loop through each of the added amounts, and quit the loop at the first amount found where formatted_amount > (int)(actual_amount / 1000000).

* Decrement the formatted_amount of the found item by 1.

_______________

Nayer Naguib