Rounding values to 2 decimel while keeping total 100%

In the attached file the values in column K ("Unique value %) should be rounded to 2 decimel but the "Total %" in column L should still stay exactly 100%.
So I can't use directly the ROUND function, as it messes up the 100 value, like this:

Formula:

Please Login or Register  to view this content.

But is there a way to propotionally add/remove value to round the numbers to 2 decimel and keep the total value 100?

The formulas in J,K,L columns are there all thanks to member Estevaoba

Also there could be an instance where there are 3 values all 33,333333%. So rounding them to 100 could be an issue. Right?

Just use a helper column which you can hide away/group shut.
This keeps it plain and simple and auditable.

Another possibility would be that you simply format column K to only show 2 decimals (but this retains the underlying exact values and removes your 100% aggregation problem).

Formatting the column K doesn't solve the problem here really. Sure, it shows only 2 decimal place, but the others still exist - they just don't show.
But I need to round the column K so, that the Total is still 100. Not 100,01 or 101,02 etc

You can't expect to round intermediate results and pretend the onward calculations to ignore that, unless you're willing to also round the end-result then.

As I wrote before, use a helper column as you want essentially two separate calculations.

Still having trouble with this.
If I use this formula to ROUND the values in column K to 2 decimal places the first sum is 100,01, but needs to be exactly 100.

Formula:

Please Login or Register  to view this content.

So what kind of other helper column could I use to have the total of 100?
As the data in column K could sum to 100,5 etc as well. And I can't just substract whatever is over 100 from the total sum.

Could you use a Min function in the total column to return 100 for anything over that?

=IF(K2="";"";IF(COUNTIFS(K$2:K2;">0";E$2:E2;E2)=SUM(--(FILTER($J$2:$J$50000;($J$2:$J$50000>0)*($E$2:$E$50000=E2);0)<>""));MIN(SUM(K$2:K2)/(1+COUNTIF(L$1:L1;">0"));100);""))

Thank You! It does work for the Total column of 100, but I'd still have to somehow make the column K equal to 100 as well.
As RaulSerg pointed out, I'd have to use some helper columns, but at the moment I don't have the slightest clue of the process.

Is it possible to evenly add or substract what ever is necessary for the sum of one client, so the column K (or newly created helper column) equals 100?
So if the total in column L for client AGA is 100,01 - I'd have to substract 0,1 from one value in column K.

Sorry, if I'm getting too confusing with this :D

What about putting the min in column K?

=IF(J2="";"";MIN(J2/SUM(FILTER($J$2:$J$50000;($J$2:$J$50000>0)*($E$2:$E$50000=E2)))*100;100))

Nope, or did work, but not if I tried ROUNDing the formula. Then I'd still get 100,01.

How could I make a helper column to distribute difference proportionally among the values in column K?

So I created a new worksheet with some data. The original worksheet has many-many more rows. But I chose those three.
Also started with a helper column S, but have no idea how to continue from there on...


The problems are:

1.The column M should equal 100 for each client, but ALS has only one value in one month. But the calculation still gives 100 for it.
So there's something wrong with the formula?

2. Client UMM has a total of 100,08 in column P, so the excess 0,08 should be distributed between the values of client UMM in column P.

3. Client VAU has a total of 99,99, so 0,01 should be somehow added to one value in column P.

It should continue with extra helper columns, but what should be the next one then?

It's necessary for the accounting, so that's why it has to be rounded to 2 decimal places and total sum has to be exactly 100% and in this worksheet the column P (or any other column associated with column P) has to eventually be 100

Guess I haven't read the rules so thoroughly.

The link to other forum: https://www.mrexcel.com/board/thread...l-100.1257377/