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

 

[Fusionista]

and i've figured out column C matters, too.

Actually column L instead of C. Because column L has the WBS Element and I only have to use unique values there for column O working hours

 

[awoohaw]

Formatting doesn't change the fact that the values still have more than 2 decimal hidden. But I have to use 2 decimal places and somehow add/substract whatever is left to have 100% total

why? If you only display format to .## the formulas work the way you need. It is just that the "apparent" sum does not match.

 

[awoohaw]

Actually column L instead of C. Because column L has the WBS Element and I only have to use unique values there for column O working hours

also, i dont see how L is filtering the way you want. explain this:

Book1.xlsx
	J	K	L	M	N	O
1	Cost Element From	Cost Element To	WBS Element	Receiver %	Total h of every month	Total WBS
2	410000	443333	XYLD01	3.69	3.69	3.69
3	410000	443333	200642	0.41		3.39
4	410000	443333	200643	0.41		3.39
5	410000	443333	200662	0.41		3.39
6	410000	443333	200665	0.41		3.39
Sheet1

Cell Formulas
Range	Formula
N2:N6	N2	=IF(E2<>E3,SUMIFS(M:M,E:E,E2,I:I,I2),"")
O2:O6	O2	=IF(COUNTIFS(I$2:I2,I2,L$2:L2,L2)=1,SUMIFS(M:M,I:I,I2,L:L,L2),"")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
N1:N1196	Expression	=AND(ISNUMBER($N1),$N1>99.99)	text	NO
N1:N1196	Expression	=AND(ISNUMBER($N1),$N1<99.99)	text	NO

 

[Fusionista]

why? If you only display format to .## the formulas work the way you need. It is just that the "apparent" sum does not match.

Not really sure, but as it's for accounting and they can't use values with more than 2 decimal places. So that's the reason why the column P eventually has to sum up to exactly 100% for each client

 

[awoohaw]

are you on the accounting team? If not, its apples an oranges then.. You're changing the actual values to fit your round peg into their square hole. Don't change the actual digit % to fit a different area's needs.

 

[Fusionista]

also, i dont see how L is filtering the way you want. explain this:

Every client has to have 100% of working hours total. Client ALS has 3.69 for some reason and is the odd one out basically

 

[awoohaw]

I would bet they have rounded their hours in your report.
somebody actually reported their time worked to the second? :

Book1.xlsx
	U	V
8
9	03:41:24	=TEXT(3.69/24,"hh:mm:ss")
Sheet1

Cell Formulas
Range	Formula
V8	V8	=IF(P8="","",IF(COUNTIFS(P$2:P8,">0",I$2:I8,I8)=SUM(--(FILTER($O$2:$O$50000,($O$2:$O$50000>0)*($I$2:$I$50000=I8),0)<>"")),SUM(P$2:P8)/(1+COUNTIF(V$1:V7,">0")),""))
U9	U9	=TEXT(3.69/24,"hh:mm:ss")
V9	V9	=FORMULATEXT(U9)

 

[Fusionista]

It's some kind of a clocking system, so I guess it's very precise :D

 

[awoohaw]

It's some kind of a clocking system, so I guess it's very precise :D

I'm sorry, I don't think it is right to change data to fix something for reporting. you can take the difference after the round in the total and add it to
rounded(sum of pct) - (sum of pct) but how will you ever know the needed precision. a differnce of .001 could further mess up your calculations. I would ask your accounting team to give you the methodology of how they want that done (or how they do it) and document it. So that when they come to you and ask why record #1 is .27% and record #40 is .28% with the same raw number what will you do? I am fetching my ten foot pole.