Round & Integer Function with Controlled Total

[farisi]

Hi,
I have a data like this for each Province and City, and simply convert the decimal "Value" to Integer "Value_Integer" using simple round & integer function.

However, as you can see, the total using column "Value" in Province A, is different from the Total using "Value_Integer" because of that round function.
What I want is if :
1. Total between original value and integer value for that Province is the same (like Province B) , do not do anything
2. Total between original value and integer value for that Province is different (like Province B), column "Value_Integer" will change a little bit, with maximum difference with original value is only 1, so it match with the total from decimal value.

Any help will be very appreciated.
Thank you so much.

 

[RudRud]

Book1
	D	E	F	G
1	4.299	4	20	20
2	4.254	4
3	11.447	11
4	11.6	12
Sheet3

Cell Formulas
Range	Formula
F1:G1	F1	=SUM(D1:D3)

column E instead of using round, you may use custom number format = 0 , but when 11.6 will become 12.

 

[farisi]

Hello, thank you for your comment.

I understand your method, but its still not the one that I want.
Because what I want is truly change the value to integer value but keep the total consistent (not just as the display), since that column will be changed to integer later on (cant accept decimal value )

Thank you

 

[Peter_SSs]

Is this what you mean?

23 06 02.xlsm
	A	B	C	D	E
1
2	A		4.299	4	20
3	A		4.254	4
4	A		11.447	12
5	B		3.255	3	20
6	B		4.655	5
7	B		2.155	2
8	B		9.935	10
Adjust

Cell Formulas
Range	Formula
D2:D8	D2	=IF(A2=A3,ROUND(C2,0),LOOKUP(9^9,E$1:E2)-SUMIF(A$1:A1,A2,D$1:D1))
E2	E2	=SUM(C2:C4)
E5	E5	=SUM(C5:C8)

 

[farisi]

Thank you, Almost like that.
But sometimes if data is hundred or thousand the total difference between total integer and total decimal can reach 5 or something.
So with your formula, that difference will be all applied to last data for each subgroup, right?

 

[Peter_SSs]

So with your formula, that difference will be all applied to last data for each subgroup, right?

Yes. I'm not sure how else you could approach it.