Solved

# Sum Formatted Numbers

Posted on 2006-04-11
215 Views
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

0
Question by:bmcelroy7

LVL 14

Expert Comment

You can perform the following steps:

* 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
0

Author Comment

The 11,480 is what is displayed but the actual amount in the cell is 11,479,682,004 so the formatted total automatically equals the actual total. Also, I don't want to use Precision as displayed.  I would greatly appreciate a follow up answer. Thanks!
0

LVL 14

Accepted Solution

Sorry! The algorithm I posted above is incorrect.

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

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
0

## Featured Post

### Suggested Solutions

Here we come across an interesting topic of coding guidelines while designing automation test scripts. The scope of this article will not be limited to QTP but to an overall extent of using VB Scripting for automation projects. Introduction Now…
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …