• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculate Percentage Cap

Thomas Kuriakose

Active Member
Respected Sirs,

We have a scenario to calculate percentage cap to not exceed 100%.

a) The values in range G9:G13 are based on the cap with round not to exceed 100%.
b) The values in J9:K13 are based on normal percentage of total without round. These values when copied to an external software total upto 101%.

The solution in a) works when copied to external software. However the apportionment is not distributed as per requirement. if we need to get an option to change values in G11, G12, G13 from 3%, 2% and 3% to 3%, 3% and 2% respectively, or any other proportion, how can we achieve this.

Kindly find attached the file your kind perusal,

Thank you very much.

with regards,
thomas
 

Attachments

Peter Bartholomew

Well-Known Member
The first advice might be 'live with it'. It is a fundamental characteristic of rounding that it introduces significant discretisation errors. To reduce the error, one repeats the calculation without rounding. If you wish to adjust the percentages so that they sum to the correct value, one procedure might be to round every percentage down and count how many, 'n', percent short of 100% you are
= 100 * ( 1 - SUM( FLOOR( values / SUM(values), 0.01 ) ) )
Then round up any 'n' values you choose
= 0.01*(k<=n) + FLOOR( values / SUM(values), 0.01 )
[here I have selected the 'n' values with the lowest index 'k']
 

Thomas Kuriakose

Active Member
Respected Sir,

Thank you so much for this guidance,

If possible, kindly help with a working file I am not at all good at the naming of ranges,

Thank you very much once again,

with regards,
thomas
 
Top