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]

wha tis the formula in column P then?

 

[Fusionista]

https://docs.google.com/spreadsheets/d/184RPAEFLnW_6sMxzze7TO9D0PAuiT9mp/edit?usp=drive_link&ouid=100728432843304392323&rtpof=true&sd=true

This link should work

 

[awoohaw]

https://docs.google.com/spreadsheets/d/184RPAEFLnW_6sMxzze7TO9D0PAuiT9mp/edit?usp=drive_link&ouid=100728432843304392323&rtpof=true&sd=true

This link should work

no:

 

[awoohaw]

https://docs.google.com/spreadsheets/d/184RPAEFLnW_6sMxzze7TO9D0PAuiT9mp/edit?usp=drive_link&ouid=100728432843304392323&rtpof=true&sd=true

This link should work

But what identifies each group of rows? what column has a record identifyer?

 

[Fusionista]

Sorry, granted the access now, but I think the file got messed up now as it's using some Google sheets links

 

[bebo021999]

I can see you are using Ex365. But I have Ex2016 then I can not help.
I am out.

 

[awoohaw]

Sorry, granted the access now, but I think the file got messed up now as it's using some Google sheets links

i don't need the google file. just tell me how you calculate column P and what is the row() identifyer column that groups the values together?

 

[awoohaw]

I can see you are using Ex365. But I have Ex2016 then I can not help.
I am out.

I'm not sure if his solution requires a 365 formula, but it is possible. I think SUMIFS or something like that is what is needed. but OP isn't telling what the IF portion is based on.

 

[Fusionista]

Column P values come from this formula

=IF(O2=""; ""; ROUND(O2 / SUM(FILTER($O$2:$O$50000; ($O$2:$O$50000 > 0) * ($I$2:$I$50000 = I2))) * 100; 2))

So it calculates the hours in column O of the client from column I and has to sum 100 total for one client. And it does without rounding the P values to 2 decimal places.
But for the accounting it is required to work with 2 decimal places and I can't figure it out at the moment how to make it sum 100% for every clients percentages in column P.

 

[Fusionista]

As in the example file I have one client UMM sum the percentage to 100,08 in column P and the other client VAU has 99,99. So For UMM I should substract 0,08 from the values in column P and for VAU I would have to add 0,01 to column P somehow