Rounding percentages problem need a solution for

[Guest]

Boss wants total formula showing the percentages of each of our three
offices' transactions compared to the total of all three combined, in whole
numbers, but sometimes the three percentages do not equal exactly 100%.

Example:
Office 1 total trans is 6074
Office 2 total trans is 7052
Office 3 total trans is 6105

Grand total for all three offices trans is 19231

Using formula that divides office 1 cell by grand total = 32%
Office 2 = 37%
Office 3 = 32%
But 32% + 37% + 32% equals 101%!

If I change the three percentage cells format to show two decimal places
then it always equals 100%, but the boss wants whole numbers only, and wants
it to equal 100%!

My formula needs a fix!
Thank-you for your time!!!

 

[Niek Otten]

Just calculate % for Office 1 and 2 and make 3 100% minus the other two

--
Kind regards,

Niek Otten


| Boss wants total formula showing the percentages of each of our three
| offices' transactions compared to the total of all three combined, in whole
| numbers, but sometimes the three percentages do not equal exactly 100%.
|
| Example:
| Office 1 total trans is 6074
| Office 2 total trans is 7052
| Office 3 total trans is 6105
|
| Grand total for all three offices trans is 19231
|
| Using formula that divides office 1 cell by grand total = 32%
| Office 2 = 37%
| Office 3 = 32%
| But 32% + 37% + 32% equals 101%!
|
| If I change the three percentage cells format to show two decimal places
| then it always equals 100%, but the boss wants whole numbers only, and wants
| it to equal 100%!
|
| My formula needs a fix!
| Thank-you for your time!!!
|
|
|

 

[Guest]

Office 1 total trans is 6074
Office 2 total trans is 7052
Office 3 total trans is 6105
Grand total for all three offices trans is 19231
Using formula that divides office 1 cell by grand total = 32%
Office 2 = 37%
Office 3 = 32%
But 32% + 37% + 32% equals 101%!
[...] the boss wants whole numbers only, and wants
it to equal 100%!

This is a common problem with rounding -- and by the way,
it has nothing to do with computer arithmetic or Excel. You
get the same error with these numbers if you do the rounding
by hand.

First, you might try educating your boss that this sort of thing
-- not summing to 100% -- is common-place and well-accepted.
For example, chosen at random from a google search:
http://www.sba.gov/IG/advisorymemoappendix00-12-01.pdf .
Has your boss heard the phrase "good enough for government
work"? ;-)

Barring that, one ironclad solution is to change the last formula
in order to force the sum to be 100%. Of course, this is valid
only when you know the numbers do indeed represent 100%
-- that is, no data errors.

For example:
A1: 6074
A2: 7052
A3: 6105
A4: =sum(A1:A2)
B1: =round(A1/A4,2)
B2: =round(A2/A4,2)
B3: =round(1-sum(B1:B2),2)
B4: =sum(B1:B3)

However, the manager of Office 3 might complain because
his percentage is underreported, since 31.75% is closer to
32%, not 31%.