Rounding values to 2 decimel while keeping total 100%

[Fusionista]

Hello!
In the attached file the values in column K ("Unique value %) should be rounded to 2 decimal 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.

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

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

So I created a new worksheet with some data. The original worksheet has many-many more rows. But I chose those three clients.
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?

Uploaded the Excel file here: Easyupload.io - Upload Files and Share Them Easily

 

[awoohaw]

Welcome to the forum. Would be kind enough to post the examples in a table?

 

[bebo021999]

The downloading link ask me to login.
try to use google drive instead.

 

[awoohaw]

The downloading link ask me to login.
try to use google drive instead.

Welcome to the forum. Would be kind enough to post the examples in a table?

just copy and paste into the thread? Although the xl2bb is usually a great tool, it prob isn't necessary for your question. But posting as a table (not a picture) is helpful. You have to log in to post or reply to questions, regardless.

 

[bebo021999]

just copy and paste into the thread? Although the xl2bb is usually a great tool, it prob isn't necessary for your question. But posting as a table (not a picture) is helpful. You have to log in to post or reply to questions, regardless.

LOL, I am not the OP!

 

[Fusionista]

Sorry, I think I'm only able to post an image. But I also uploaded the Excel file in the first post.

 

[awoohaw]

Sorry, I think I'm only able to post an image. But I also uploaded the Excel file in the first post.

View attachment 110083

View attachment 110084

no, copy... come to forum. start a reply paste (click in a reply).
posting a picture is making the forum type everything necessary for the formula. what columns are you calculating for the individual percentages? column O or column P?

 

[Fusionista]

The downloading link ask me to login.
try to use google drive instead.

It's not nececcary to log in. It allows you to download the file without logging in.

 

[Fusionista]

no, copy... come to forum. start a reply paste (click in a reply).
posting a picture is making the forum type everything necessary for the formula. what columns are you calculating for the individual percentages? column O or column P?

The column P values are already ROUNDed to 2 decimal places. But it might not be the right way at all.

 

[bebo021999]

It's not nececcary to log in. It allows you to download the file without logging in.

No, It did not.
Try to upload it to google drive then send a link