{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Man Page RoundToSum","RoundToSum","Read_me","Keys","1_Allocation","2_Allocation","RoundToSum vs Others"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":3,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Man Page RoundToSum","index":0,"n_rows":58,"n_cols":2,"rows_kept":58,"cut":false,"hidden":false,"merged":[]}
["Name"]
[]
["RoundToSum - Calculate rounded summands which exactly add up to the rounded sum of unrounded summands"]
[]
["Synopsis"]
[]
["RoundToSum(vInput, [lDigits], [bAbsSum], [lErrorType], [bDontAmend])"]
[]
["Description"]
[]
["RoundToSum calculates rounded summands which exactly add up to the rounded sum of the unrounded summands."]
["It uses the largest remainder method which minimizes the error to the original unrounded summands."]
[]
["Example"]
[]
["If you need to distribute 1 EUR to three people, all of them should get 1/3 EUR but you need to amend this to whole cents:"]
[]
[]
[]
[]
[]
[]
[]
[]
["Options"]
[]
["vInput"]
[null,"Range or array which contains unrounded values"]
[]
["lDigits"]
[null,"Optional - standard value is 2 if not provided"]
[null,"Number of digits to round to. For example: 0 rounds to integers, 2 rounds to the cent, -3 will use thousands"]
[]
["bAbsSum"]
[null,"Optional - standard value is True if not provided"]
[null,"True - takes the summands as they are"]
[null,"False - works on the summands' percentages to make all percentages add up to 100% exactly"]
[]
["lErrorType"]
[null,"Optional - type of error to be minized; standard value is 1 if not provided"]
[null,"1 - Absolute error"]
[null,"2 - Relative error"]
[]
["bDontAmend"]
[null,"Optional - standard value is False if not provided"]
[null,"True - do not amend the rounded summands to match the rounded sum. This parameter is mainly for ease of use or presentation to see this function's impact"]
[null,"False - perform amendments as described above"]
[]
["See Also"]
[null,"https://www.experts-exchange.com/articles/31683/Round-elements-of-a-sum-to-match-a-total.html"]
[null,"https://www.think-cell.com/en/support/manual/exceldatarounding.shtml"]
[null,"http://sulprobil.com/Get_it_done/IT/Excel_Fun/Worksheet_Functions/Largest_Remainder/Allocation_of_Overheads/allocation_of_overheads.html"]
[null,"http://sulprobil.com/Get_it_done/IT/Excel_Fun/Excel_VBA/D-Hondt_Method/d-hondt_method.html"]
[null,"http://sulprobil.com/Get_it_done/Controlling/Risk_Management/Random_Numbers/Distributions/sbExactRandHistogrm/sbexactrandhistogrm.html"]
[]
["Further Reading"]
[null,"Mosteller, Youtz and Zahn: \"The Distributions of Sums of Rounded Percentages\", Demography (1967), 4, p. 850 - 858"]
[null,"Diaconis and Freedman: \"On Rounding Percentages\", Journal of the American Statistical Association, Vol. 74, No. 366. (Jun., 1979), pp. 359-364"]

{"sheet":"RoundToSum","index":1,"n_rows":35,"n_cols":12,"rows_kept":35,"cut":false,"hidden":false,"merged":[]}
[null,null,"bAbsSum",true,null,null,"Total objective",null,"lErrorType",1]
[null,null,"Decimals to round to:",-3,null,null,"Difference to rounded",null,"bDontAmend",false]
[null,"Absolute values","Relative values","Rounded values","Error","Rank of",{"$f":"ROUND(IF($D$1,SUM(B5:B16),SUM(C5:C16)),$D$2)","v":682000},"Result",null,"VBA",null,"Difference"]
["Total",{"$f":"SUM(B5:B16)","v":681709},{"$f":"SUM(C5:C16)","v":100},{"$f":"SUM(D5:D16)","v":684000},null,"error",{"$f":"G3-D4","v":-2000},{"$f":"SUM(H5:H16)","v":682000},null,{"$f":"SUM(J5:J16)","v":682000},null,{"$f":"J4-H4","v":0}]
[1,4523,{"$f":"B5/$B$4%","v":0.66347957852984185},{"$f":"ROUND(IF($D$1,B5,C5),$D$2)","v":5000},{"$f":"CHOOSE($J$1,D5-IF($D$1,B5,C5),(D5-IF($D$1,B5,C5))*IF($D$1,B5,C5))","v":477},{"$f":"COUNTIF($E$5:$E$16,IF($G$4>0,\"<\",\">\")&$E5)+COUNTIF($E$5:$E5,$E5)","v":3},{"$f":"IF(F5<=ROUND(ABS($G$4*10^$D$2),0),SIGN($G$4)*10^-$D$2,0)","v":0},{"$f":"D5+G5","v":5000},null,{"$f":"RoundToSum(B5:B16,D2,D1,J1,J2)","v":5000},null,{"$f":"J5-H5","v":0}]
[2,456,{"$f":"B6/$B$4%","v":6.6890711432590733E-2},{"$f":"ROUND(IF($D$1,B6,C6),$D$2)","v":0},{"$f":"CHOOSE($J$1,D6-IF($D$1,B6,C6),(D6-IF($D$1,B6,C6))*IF($D$1,B6,C6))","v":-456},{"$f":"COUNTIF($E$5:$E$16,IF($G$4>0,\"<\",\">\")&$E6)+COUNTIF($E$5:$E6,$E6)","v":12},{"$f":"IF(F6<=ROUND(ABS($G$4*10^$D$2),0),SIGN($G$4)*10^-$D$2,0)","v":0},{"$f":"D6+G6","v":0},null,0,null,0]
[3,-78845,-11.565785401102229,-79000,-155,{"$f":"COUNTIF($E$5:$E$16,IF($G$4>0,\"<\",\">\")&$E7)+COUNTIF($E$5:$E7,$E7)","v":11},0,-79000,null,-79000,null,0]
[4,-14491,-2.1256870600212112,-14000,491,{"$f":"COUNTIF($E$5:$E$16,IF($G$4>0,\"<\",\">\")&$E8)+COUNTIF($E$5:$E8,$E8)","v":1},-1000,-15000,null,-15000,null,0]
[5,65789,9.6505987158743682,66000,211,{"$f":"COUNTIF($E$5:$E$16,IF($G$4>0,\"<\",\">\")&$E9)+COUNTIF($E$5:$E9,$E9)","v":7},0,66000,null,66000,null,0]
[6,129512,18.998135568108975,130000,488,{"$f":"COUNTIF($E$5:$E$16,IF($G$4>0,\"<\",\">\")&$E10)+COUNTIF($E$5:$E10,$E10)","v":2},-1000,129000,null,129000,null,0]
[7,15562,2.2827922177938094,16000,438,{"$f":"COUNTIF($E$5:$E$16,IF($G$4>0,\"<\",\">\")&$E11)+COUNTIF($E$5:$E11,$E11)","v":5},0,16000,null,16000,null,0]
[8,548555,80.4676188813702,549000,445,{"$f":"COUNTIF($E$5:$E$16,IF($G$4>0,\"<\",\">\")&$E12)+COUNTIF($E$5:$E12,$E12)","v":4},0,549000,null,549000,null,0]
[9,1590,0.23323734907416507,2000,410,{"$f":"COUNTIF($E$5:$E$16,IF($G$4>0,\"<\",\">\")&$E13)+COUNTIF($E$5:$E13,$E13)","v":6},0,2000,null,2000,null,0]
[10,-897,-0.1315810705154252,-1000,-103,{"$f":"COUNTIF($E$5:$E$16,IF($G$4>0,\"<\",\">\")&$E14)+COUNTIF($E$5:$E14,$E14)","v":10},0,-1000,null,-1000,null,0]
[11,6968,1.0221370115401145,7000,32,{"$f":"COUNTIF($E$5:$E$16,IF($G$4>0,\"<\",\">\")&$E15)+COUNTIF($E$5:$E15,$E15)","v":8},0,7000,null,7000,null,0]
[12,2987,0.43816349791479942,3000,13,{"$f":"COUNTIF($E$5:$E$16,IF($G$4>0,\"<\",\">\")&$E16)+COUNTIF($E$5:$E16,$E16)","v":9},0,3000,null,3000,null,0]
[]
[]
[null,null,"bAbsSum",false,null,null,"Total objective"]
[null,null,"Decimals to round to:",2,null,null,"Difference to rounded"]
[null,"Absolute values","Relative values","Rounded values","Error","Rank of",{"$f":"ROUND(IF($D$19,SUM(B23:B25),SUM(C23:C25)),$D$20)","v":100},"Result",null,"VBA",null,"Result"]
["Total",{"$f":"SUM(B23:B25)","v":611},{"$f":"SUM(C23:C25)","v":99.999999999999986},{"$f":"SUM(D23:D25)","v":99.99},null,"error",{"$f":"G21-D22","v":1.0000000000005116E-2},{"$f":"SUM(H23:H25)","v":100},null,{"$f":"SUM(J23:J25)","v":100},null,{"$f":"J22-H22","v":0}]
[1,45,{"$f":"B23/$B$22%","v":7.3649754500818325},{"$f":"ROUND(IF($D$19,B23,C23),$D$20)","v":7.36},{"$f":"CHOOSE($J$1,D23-IF($D$19,B23,C23),(D23-IF($D$19,B23,C23))*IF($D$19,B23,C23))","v":-4.9754500818322001E-3},{"$f":"COUNTIF($E$23:$E$25,IF($G$22>0,\"<\",\">\")&$E23)+COUNTIF($E$23:$E23,$E23)","v":1},{"$f":"IF(F23<=ROUND(ABS($G$22*10^$D$20),0),SIGN($G$22)*10^-$D$20,0)","v":0.01},{"$f":"D23+G23","v":7.37},null,{"$f":"RoundToSum(B23:B25,D20,D19,J1,J2)","v":7.37},null,0]
[2,555,{"$f":"B24/$B$22%","v":90.834697217675938},{"$f":"ROUND(IF($D$19,B24,C24),$D$20)","v":90.83},{"$f":"CHOOSE($J$1,D24-IF($D$19,B24,C24),(D24-IF($D$19,B24,C24))*IF($D$19,B24,C24))","v":-4.6972176759396689E-3},{"$f":"COUNTIF($E$23:$E$25,IF($G$22>0,\"<\",\">\")&$E24)+COUNTIF($E$23:$E24,$E24)","v":2},{"$f":"IF(F24<=ROUND(ABS($G$22*10^$D$20),0),SIGN($G$22)*10^-$D$20,0)","v":0},{"$f":"D24+G24","v":90.83},null,90.83,null,0]
[3,11,1.8003273322422257,1.8,-3.2733224222569746E-4,{"$f":"COUNTIF($E$23:$E$25,IF($G$22>0,\"<\",\">\")&$E25)+COUNTIF($E$23:$E25,$E25)","v":3},0,1.8,null,1.8,null,0]
[]
[]
[null,null,"bAbsSum",true,null,null,"Total objective"]
[null,null,"Decimals to round to:",3,null,null,"Difference to rounded"]
[null,"Absolute values","Relative values","Rounded values","Error","Rank of",{"$f":"ROUND(IF($D$28,SUM(B32:B35),SUM(C32:C35)),$D$29)","v":2.2149999999999999},"Result",null,"VBA",null,"Result"]
["Total",{"$f":"SUM(B32:B35)","v":2.2149278314526493},{"$f":"SUM(C32:C35)","v":99.999999999999986},{"$f":"SUM(D32:D35)","v":2.2159999999999997},null,"error",{"$f":"G30-D31","v":-9.9999999999988987E-4},{"$f":"SUM(H32:H35)","v":2.2149999999999999},null,{"$f":"SUM(J32:J35)","v":2.2149999999999999},null,{"$f":"J31-H31","v":0}]
[1,{"$f":"RAND()","v":0.97774821070370443},{"$f":"B32/$B$31%","v":44.143569682920692},{"$f":"ROUND(IF($D$28,B32,C32),$D$29)","v":0.97799999999999998},{"$f":"CHOOSE($J$1,D32-IF($D$28,B32,C32),(D32-IF($D$28,B32,C32))*IF($D$28,B32,C32))","v":2.5178929629554592E-4},{"$f":"COUNTIF($E$32:$E$36,IF($G$31>0,\"<\",\">\")&$E32)+COUNTIF($E$32:$E32,$E32)","v":3},{"$f":"IF(F32<=ROUND(ABS($G$31*10^$D$29),0),SIGN($G$31)*10^-$D$29,0)","v":0},{"$f":"D32+G32","v":0.97799999999999998},null,{"$f":"RoundToSum(B32:B35,D29,D28,J1,J2)","v":0.97799999999999998},null,0]
[2,{"$f":"RAND()","v":0.81569030935834275},{"$f":"B33/$B$31%","v":36.826947486743904},{"$f":"ROUND(IF($D$28,B33,C33),$D$29)","v":0.81599999999999995},{"$f":"CHOOSE($J$1,D33-IF($D$28,B33,C33),(D33-IF($D$28,B33,C33))*IF($D$28,B33,C33))","v":3.0969064165720095E-4},{"$f":"COUNTIF($E$32:$E$36,IF($G$31>0,\"<\",\">\")&$E33)+COUNTIF($E$32:$E33,$E33)","v":2},{"$f":"IF(F33<=ROUND(ABS($G$31*10^$D$29),0),SIGN($G$31)*10^-$D$29,0)","v":0},{"$f":"D33+G33","v":0.81599999999999995},null,0.81599999999999995,null,0]
[3,0.41562727183308168,18.764822308476507,0.41599999999999998,3.7272816691830135E-4,{"$f":"COUNTIF($E$32:$E$36,IF($G$31>0,\"<\",\">\")&$E34)+COUNTIF($E$32:$E34,$E34)","v":1},-1E-3,0.41499999999999998,null,0.41499999999999998,null,0]
[4,5.8620395575204043E-3,0.26466052185889122,6.0000000000000001E-3,1.3796044247959584E-4,{"$f":"COUNTIF($E$32:$E$36,IF($G$31>0,\"<\",\">\")&$E35)+COUNTIF($E$32:$E35,$E35)","v":4},0,6.0000000000000001E-3,null,6.0000000000000001E-3,null,0]

{"sheet":"Read_me","index":2,"n_rows":10,"n_cols":1,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Original (German) from: https://www.controllingportal.de/Marktplatz/Excel-Tools/Gemeinkostenumlage.html"]
["Downloaded on: 20/01/2018  11:21:00"]
[]
["1. Original was wrong: 2_Umlage!C11 should read: ='1_Umlage'!J28"]
["2. Graphic gemeinkostenumlage-1.gif on https://www.controllingportal.de/Marktplatz/Excel-Tools/Gemeinkostenumlage.html also showed wrong value 625."]
["3. This calculation corrects this error and is calculating correctly down to the cent so that the rounded sum is identical to the sum of rounded values."]
["4. Now this calculation is fit for purpose with existing accounting systems."]
[]
["Bernd Plumhoff"]
["2018-01-21T11:20:00"]

{"sheet":"Keys","index":3,"n_rows":26,"n_cols":16,"rows_kept":26,"cut":false,"hidden":false,"merged":[]}
[]
["Phase 1 - Allocation of overhead costs to all cost centers"]
[null,null,"Overhead Cost Centers",null,null,null,null,null,null,null,"Secondary Cost Centers",null,null,"Primary Cost Centers"]
["Key","Total","Management","Secretariat","Accounting","Controlling","HR","Marketing","Trainees","Workers Council","Factory 1","Factory 2","Car Park","Production1","Production2","Production3"]
["per Head",{"$f":"SUM(C5:P5)","v":102},1,1,3,1,2,3,3,1,12,10,null,20,20,25]
["sqm",{"$f":"SUM(C6:P6)","v":2685},50,40,100,30,50,50,null,15,250,350,100,500,550,600]
["uniform",{"$f":"SUM(C7:P7)","v":14},1,1,1,1,1,1,1,1,1,1,1,1,1,1]
["Weighted",{"$f":"SUM(C8:P8)","v":16},2,1,1,1,1,2,1,1,1,1,1,1,1,1]
[]
[]
["Phase 2 - Allocation of Overhead Cost Centers and Secondary Cost Centers to Primary Cost Centers"]
[]
[]
["Secondary Cost Center","Key","Production1","Production2","Production3","Total"]
[]
["Management","Weighted",0.3,0.4,0.3,{"$f":"SUM(C16:E16)","v":1}]
["Secretariat","Weighted",0.4,0.5,0.1,1]
["Accounting","Weighted",0.3,0.13,0.56999999999999995,1]
["Controlling","uniform",1,1,1,3]
["HR","per Head",20,20,25,65]
["Marketing","Weighted",0.3,0.42,0.28000000000000003,1]
["Trainees","uniform",1,1,1,3]
["Workers Council","per Head",20,20,25,65]
["Factory 1","Weighted",0.25,0.2,0.55000000000000004,1]
["Factory 2","Weighted",0.2,0.2,0.6,1]
["Car Park","Weighted",0.4,0.3,0.3,1]

{"sheet":"1_Allocation","index":4,"n_rows":32,"n_cols":18,"rows_kept":32,"cut":false,"hidden":false,"merged":[]}
["Allocation of overhead costs to all cost centers"]
[]
[null,null,null,"Overhead Cost Centers",null,null,null,null,null,null,null,"Secondary Cost Centers",null,null,"Primary Cost Centers",null,null,"Total"]
["Cost Category","Key","Overhead Costs","Management","Secretariat","Accounting","Controlling","HR","Marketing","Trainees","Workers Council","Factory 1","Factory 2","Car Park","Production1","Production2","Production3"]
["Travel expenses","Weighted",10000,{"$f":"RoundToSum($C5/INDEX(Keys!$B$5:$B$8,MATCH($B5,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B5,Keys!$A$5:$A$8,0),),,,,$R$32)","v":1250},625,625,625,625,1250,625,625,625,625,625,625,625,625,{"$f":"SUM(D5:Q5)","v":10000}]
["Supervisory board","uniform",3000,{"$f":"RoundToSum($C6/INDEX(Keys!$B$5:$B$8,MATCH($B6,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B6,Keys!$A$5:$A$8,0),),,,,$R$32)","v":214.28},214.28,214.28,214.28,214.28,214.28,214.29,214.29,214.29,214.29,214.29,214.29,214.29,214.29,3000]
["Hospitality","Weighted",2000,{"$f":"RoundToSum($C7/INDEX(Keys!$B$5:$B$8,MATCH($B7,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B7,Keys!$A$5:$A$8,0),),,,,$R$32)","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Presents","Weighted",1000,{"$f":"RoundToSum($C8/INDEX(Keys!$B$5:$B$8,MATCH($B8,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B8,Keys!$A$5:$A$8,0),),,,,$R$32)","v":125},62.5,62.5,62.5,62.5,125,62.5,62.5,62.5,62.5,62.5,62.5,62.5,62.5,1000]
["Fees, charges","uniform",500,{"$f":"RoundToSum($C9/INDEX(Keys!$B$5:$B$8,MATCH($B9,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B9,Keys!$A$5:$A$8,0),),,,,$R$32)","v":35.72},35.72,35.72,35.72,35.72,35.72,35.71,35.71,35.71,35.71,35.71,35.71,35.71,35.71,499.99999999999989]
["Car costs","Weighted",4500,{"$f":"RoundToSum($C10/INDEX(Keys!$B$5:$B$8,MATCH($B10,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B10,Keys!$A$5:$A$8,0),),,,,$R$32)","v":562.5},281.25,281.25,281.25,281.25,562.5,281.25,281.25,281.25,281.25,281.25,281.25,281.25,281.25,4500]
["Hardware, equipment","Weighted",200,{"$f":"RoundToSum($C11/INDEX(Keys!$B$5:$B$8,MATCH($B11,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B11,Keys!$A$5:$A$8,0),),,,,$R$32)","v":25},12.5,12.5,12.5,12.5,25,12.5,12.5,12.5,12.5,12.5,12.5,12.5,12.5,200]
["External audit","uniform",10000,{"$f":"RoundToSum($C12/INDEX(Keys!$B$5:$B$8,MATCH($B12,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B12,Keys!$A$5:$A$8,0),),,,,$R$32)","v":714.28},714.28,714.28,714.28,714.28,714.28,714.29,714.29,714.29,714.29,714.29,714.29,714.29,714.29,10000]
["Other operating expenses","sqm",5500,{"$f":"RoundToSum($C13/INDEX(Keys!$B$5:$B$8,MATCH($B13,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B13,Keys!$A$5:$A$8,0),),,,,$R$32)","v":102.42},81.94,204.84,61.45,102.42,102.42,0,30.73,512.1,716.95,204.84,1024.21,1126.6300000000001,1229.05,5500.0000000000009]
["Energy costs","sqm",6000,{"$f":"RoundToSum($C14/INDEX(Keys!$B$5:$B$8,MATCH($B14,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B14,Keys!$A$5:$A$8,0),),,,,$R$32)","v":111.73},89.39,223.47,67.040000000000006,111.73,111.73,0,33.520000000000003,558.66,782.12,223.46,1117.32,1229.05,1340.78,6000]
["Insurances","per Head",5000,{"$f":"RoundToSum($C15/INDEX(Keys!$B$5:$B$8,MATCH($B15,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B15,Keys!$A$5:$A$8,0),),,,,$R$32)","v":49.02},49.02,147.06,49.02,98.04,147.06,147.06,49.02,588.23,490.2,0,980.39,980.39,1225.49,5000]
["Legal costs","Weighted",5000,{"$f":"RoundToSum($C16/INDEX(Keys!$B$5:$B$8,MATCH($B16,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B16,Keys!$A$5:$A$8,0),),,,,$R$32)","v":625},312.5,312.5,312.5,312.5,625,312.5,312.5,312.5,312.5,312.5,312.5,312.5,312.5,5000]
["Accounting costs","Weighted",1000,{"$f":"RoundToSum($C17/INDEX(Keys!$B$5:$B$8,MATCH($B17,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B17,Keys!$A$5:$A$8,0),),,,,$R$32)","v":125},62.5,62.5,62.5,62.5,125,62.5,62.5,62.5,62.5,62.5,62.5,62.5,62.5,1000]
["Stationary","Weighted",2000,{"$f":"RoundToSum($C18/INDEX(Keys!$B$5:$B$8,MATCH($B18,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B18,Keys!$A$5:$A$8,0),),,,,$R$32)","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Telecommunication","Weighted",2500,{"$f":"RoundToSum($C19/INDEX(Keys!$B$5:$B$8,MATCH($B19,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B19,Keys!$A$5:$A$8,0),),,,,$R$32)","v":312.5},156.25,156.25,156.25,156.25,312.5,156.25,156.25,156.25,156.25,156.25,156.25,156.25,156.25,2500]
["Shipping and mailing costs","Weighted",2000,{"$f":"RoundToSum($C20/INDEX(Keys!$B$5:$B$8,MATCH($B20,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B20,Keys!$A$5:$A$8,0),),,,,$R$32)","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Books, magazines","Weighted",1000,{"$f":"RoundToSum($C21/INDEX(Keys!$B$5:$B$8,MATCH($B21,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B21,Keys!$A$5:$A$8,0),),,,,$R$32)","v":125},62.5,62.5,62.5,62.5,125,62.5,62.5,62.5,62.5,62.5,62.5,62.5,62.5,1000]
["Money transfer fees","Weighted",500,{"$f":"RoundToSum($C22/INDEX(Keys!$B$5:$B$8,MATCH($B22,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B22,Keys!$A$5:$A$8,0),),,,,$R$32)","v":62.5},31.25,31.25,31.25,31.25,62.5,31.25,31.25,31.25,31.25,31.25,31.25,31.25,31.25,500]
["Damages, compensation","uniform",250,{"$f":"RoundToSum($C23/INDEX(Keys!$B$5:$B$8,MATCH($B23,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B23,Keys!$A$5:$A$8,0),),,,,$R$32)","v":17.850000000000001},17.850000000000001,17.850000000000001,17.850000000000001,17.86,17.86,17.86,17.86,17.86,17.86,17.86,17.86,17.86,17.86,250.00000000000006]
["Working clothes","Weighted",1500,{"$f":"RoundToSum($C24/INDEX(Keys!$B$5:$B$8,MATCH($B24,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B24,Keys!$A$5:$A$8,0),),,,,$R$32)","v":187.5},93.75,93.75,93.75,93.75,187.5,93.75,93.75,93.75,93.75,93.75,93.75,93.75,93.75,1500]
["Handicapped fee","uniform",2000,{"$f":"RoundToSum($C25/INDEX(Keys!$B$5:$B$8,MATCH($B25,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B25,Keys!$A$5:$A$8,0),),,,,$R$32)","v":142.85},142.85,142.85,142.85,142.86000000000001,142.86000000000001,142.86000000000001,142.86000000000001,142.86000000000001,142.86000000000001,142.86000000000001,142.86000000000001,142.86000000000001,142.86000000000001,2000.0000000000009]
["Training and further education","Weighted",2000,{"$f":"RoundToSum($C26/INDEX(Keys!$B$5:$B$8,MATCH($B26,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B26,Keys!$A$5:$A$8,0),),,,,$R$32)","v":250},125,125,125,125,250,125,125,125,125,125,125,125,125,2000]
["Other operating expenses","Weighted",1500,{"$f":"RoundToSum($C27/INDEX(Keys!$B$5:$B$8,MATCH($B27,Keys!$A$5:$A$8,0))*INDEX(Keys!$C$5:$P$8,MATCH($B27,Keys!$A$5:$A$8,0),),,,,$R$32)","v":187.5},93.75,93.75,93.75,93.75,187.5,93.75,93.75,93.75,93.75,93.75,93.75,93.75,93.75,1500]
["Total",null,{"$f":"SUM(ROUND(C5:C27,2))","v":68950},{"$f":"SUM(ROUND(D5:D27,2))","v":5975.6500000000005},{"$f":"SUM(ROUND(E5:E27,2))","v":3639.0799999999995},{"$f":"SUM(ROUND(F5:F27,2))","v":3994.0999999999995},{"$f":"SUM(ROUND(G5:G27,2))","v":3596.2399999999993},{"$f":"SUM(ROUND(H5:H27,2))","v":3730.94},{"$f":"SUM(ROUND(I5:I27,2))","v":6073.7099999999991},{"$f":"SUM(ROUND(J5:J27,2))","v":3565.82},{"$f":"SUM(ROUND(K5:K27,2))","v":3532.03},{"$f":"SUM(ROUND(L5:L27,2))","v":5077.7499999999991},{"$f":"SUM(ROUND(M5:M27,2))","v":5408.0299999999988},{"$f":"SUM(ROUND(N5:N27,2))","v":3847.0600000000004},{"$f":"SUM(ROUND(O5:O27,2))","v":6540.6799999999994},{"$f":"SUM(ROUND(P5:P27,2))","v":6754.83},{"$f":"SUM(ROUND(Q5:Q27,2))","v":7214.079999999999},{"$f":"SUM(D28:Q28)","v":68949.999999999985}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Check",{"$f":"R28-C28","v":0}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"bDontAmend",false]

{"sheet":"2_Allocation","index":5,"n_rows":21,"n_cols":11,"rows_kept":21,"cut":false,"hidden":false,"merged":[]}
["Allocation of Overhead Cost Centers and Secondary Cost Centers to Primary Cost Centers"]
[]
["Allocated Cost Centers","Direct Costs","Allocation Phase 1","Total","Production1","Production2","Production3","Total"]
[]
["Management",111666,{"$f":"TRANSPOSE('1_Allocation'!D28:N28)","v":5975.6500000000005},{"$f":"SUM(B5:C5)","v":117641.65},{"$f":"RoundToSum($D5/Keys!$F16*Keys!C16:E16,,,,$K$21)","v":35292.49},47056.66,35292.5,{"$f":"SUM(ROUND(E5:G5,2))","v":117641.65}]
["Secretariat",34627,3639.0799999999995,{"$f":"SUM(B6:C6)","v":38266.080000000002},{"$f":"RoundToSum($D6/Keys!$F17*Keys!C17:E17,,,,$K$21)","v":15306.43},19133.04,3826.61,{"$f":"SUM(ROUND(E6:G6,2))","v":38266.080000000002}]
["Accounting",96834,3994.0999999999995,100828.1,{"$f":"RoundToSum($D7/Keys!$F18*Keys!C18:E18,,,,$K$21)","v":30248.43},13107.65,57472.02,{"$f":"SUM(ROUND(E7:G7,2))","v":100828.1}]
["Controlling",83875,3596.2399999999993,87471.24,{"$f":"RoundToSum($D8/Keys!$F19*Keys!C19:E19,,,,$K$21)","v":29157.08},29157.08,29157.08,{"$f":"SUM(ROUND(E8:G8,2))","v":87471.24}]
["HR",53765,3730.94,57495.94,{"$f":"RoundToSum($D9/Keys!$F20*Keys!C20:E20,,,,$K$21)","v":17691.060000000001},17691.060000000001,22113.82,{"$f":"SUM(ROUND(E9:G9,2))","v":57495.94}]
["Marketing",239170,6073.7099999999991,245243.71,{"$f":"RoundToSum($D10/Keys!$F21*Keys!C21:E21,,,,$K$21)","v":73573.11},103002.36,68668.240000000005,{"$f":"SUM(ROUND(E10:G10,2))","v":245243.71000000002}]
["Trainees",147397,3565.82,150962.82,{"$f":"RoundToSum($D11/Keys!$F22*Keys!C22:E22,,,,$K$21)","v":50320.94},50320.94,50320.94,{"$f":"SUM(ROUND(E11:G11,2))","v":150962.82}]
["Workers Council",471,3532.03,4003.03,{"$f":"RoundToSum($D12/Keys!$F23*Keys!C23:E23,,,,$K$21)","v":1231.7},1231.7,1539.63,{"$f":"SUM(ROUND(E12:G12,2))","v":4003.03}]
["Factory 1",125225,5077.7499999999991,130302.75,{"$f":"RoundToSum($D13/Keys!$F24*Keys!C24:E24,,,,$K$21)","v":32575.69},26060.55,71666.509999999995,{"$f":"SUM(ROUND(E13:G13,2))","v":130302.75}]
["Factory 2",2398512,5408.0299999999988,2403920.0299999998,{"$f":"RoundToSum($D14/Keys!$F25*Keys!C25:E25,,,,$K$21)","v":480784},480784.01,1442352.02,{"$f":"SUM(ROUND(E14:G14,2))","v":2403920.0300000003}]
["Car Park",26992,3847.0600000000004,30839.06,{"$f":"RoundToSum($D15/Keys!$F26*Keys!C26:E26,,,,$K$21)","v":12335.62},9251.7199999999993,9251.7199999999993,{"$f":"SUM(ROUND(E15:G15,2))","v":30839.059999999998}]
["Phase 1 Allocation",null,null,null,{"$f":"'1_Allocation'!O28","v":6540.6799999999994},{"$f":"'1_Allocation'!P28","v":6754.83},{"$f":"'1_Allocation'!Q28","v":7214.079999999999},{"$f":"SUM(ROUND(E16:G16,2))","v":20509.59}]
["Phase 2 Allocation",{"$f":"SUM(B5:B15)","v":3318534},48440.409999999989,3366974.4099999997,778516.54999999993,796796.77,1791661.09,3366974.4100000006]
["Directs Costs",null,null,null,738060,854000,650360,{"$f":"SUM(ROUND(E18:G18,2))","v":2242420}]
["Total Primary Cost Centers",null,null,null,{"$f":"SUM(E16:E18)","v":1523117.23},{"$f":"SUM(F16:F18)","v":1657551.6},{"$f":"SUM(G16:G18)","v":2449235.17},{"$f":"SUM(H16:H18)","v":5629904},null,"Check",{"$f":"H19-SUM(E18:G18)-SUM(B5:B15)-SUM('1_Allocation'!C5:C27)","v":0}]
[]
["Overhead rate",null,null,null,{"$f":"(E17+E16)/E18","v":1.0636767065008266},{"$f":"(F17+F16)/F18","v":0.94092693208430911},{"$f":"(G17+G16)/G18","v":2.7659683406113538},{"$f":"(H17+H16)/H18","v":1.5106376147198119},null,"bDontAmend",false]

{"sheet":"RoundToSum vs Others","index":6,"n_rows":44,"n_cols":10,"rows_kept":44,"cut":false,"hidden":false,"merged":[]}
[null,null,"I","II","III","IV","V","VI","VII","VIII"]
[null,null,"Original unrounded","RoundToSum","Cascading Round","Simple Round & Amend Last","Simple Round","Difference II - V","Difference III - V","Difference IV - V"]
[null,"Summands",678.64745790355039,{"$f":"TRANSPOSE(RoundToSum(C3:C42))","v":678.65},{"$f":"ROUND(SUM($C$3:$C3),2)-SUM(E$2:E2)","v":678.65},{"$f":"ROUND($C3,2)","v":678.65},{"$f":"ROUND($C3,2)","v":678.65},{"$f":"IF(ABS(D3-$G3)<0.000001,\"\",D3-$G3)","v":""},{"$f":"IF(ABS(E3-$G3)<0.000001,\"\",E3-$G3)","v":""},{"$f":"IF(ABS(F3-$G3)<0.000001,\"\",F3-$G3)","v":""}]
[null,null,146.70294787419326,146.69999999999999,{"$f":"ROUND(SUM($C$3:$C4),2)-SUM(E$2:E3)","v":146.70000000000005},{"$f":"ROUND($C4,2)","v":146.69999999999999},146.69999999999999,{"$f":"IF(ABS(D4-$G4)<0.000001,\"\",D4-$G4)","v":""},"",""]
[null,null,808.43077862674488,808.43,{"$f":"ROUND(SUM($C$3:$C5),2)-SUM(E$2:E4)","v":808.43},808.43,808.43,"","",""]
[null,null,878.05950037628986,878.06,{"$f":"ROUND(SUM($C$3:$C6),2)-SUM(E$2:E5)","v":878.06000000000017},878.06,878.06,"","",""]
[null,null,801.00136836525542,801,{"$f":"ROUND(SUM($C$3:$C7),2)-SUM(E$2:E6)","v":801},801,801,"","",""]
[null,null,895.21500286954404,895.21,{"$f":"ROUND(SUM($C$3:$C8),2)-SUM(E$2:E7)","v":895.22000000000025},895.22,895.22,-9.9999999999909051E-3,"",""]
[null,null,55.048054476254272,55.05,{"$f":"ROUND(SUM($C$3:$C9),2)-SUM(E$2:E8)","v":55.049999999999272},55.05,55.05,"","",""]
[null,null,57.686330690508655,57.69,{"$f":"ROUND(SUM($C$3:$C10),2)-SUM(E$2:E9)","v":57.680000000000291},57.69,57.69,"",-9.999999999706688E-3,""]
[null,null,740.31512839191248,740.31,{"$f":"ROUND(SUM($C$3:$C11),2)-SUM(E$2:E10)","v":740.31999999999971},740.32,740.32,-1.0000000000104592E-2,"",""]
[null,null,437.47827951351604,437.48,{"$f":"ROUND(SUM($C$3:$C12),2)-SUM(E$2:E11)","v":437.47000000000025},437.48,437.48,"",-9.9999999997635314E-3,""]
[null,null,185.28145702375974,185.28,{"$f":"ROUND(SUM($C$3:$C13),2)-SUM(E$2:E12)","v":185.28999999999996},185.28,185.28,"",9.9999999999624833E-3,""]
[null,null,950.95522261636324,950.96,{"$f":"ROUND(SUM($C$3:$C14),2)-SUM(E$2:E13)","v":950.94999999999982},950.96,950.96,"",-1.0000000000218279E-2,""]
[null,null,692.09654543499551,692.1,{"$f":"ROUND(SUM($C$3:$C15),2)-SUM(E$2:E14)","v":692.10000000000036},692.1,692.1,"","",""]
[null,null,147.16810615157073,147.16999999999999,{"$f":"ROUND(SUM($C$3:$C16),2)-SUM(E$2:E15)","v":147.17000000000007},147.16999999999999,147.16999999999999,"","",""]
[null,null,237.13755196767238,237.14,{"$f":"ROUND(SUM($C$3:$C17),2)-SUM(E$2:E16)","v":237.13000000000011},237.14,237.14,"",-9.9999999998772182E-3,""]
[null,null,487.21542128640482,487.22,{"$f":"ROUND(SUM($C$3:$C18),2)-SUM(E$2:E17)","v":487.22000000000025},487.22,487.22,"","",""]
[null,null,364.46415079650006,364.46,{"$f":"ROUND(SUM($C$3:$C19),2)-SUM(E$2:E18)","v":364.45999999999913},364.46,364.46,"","",""]
[null,null,525.25379069070914,525.25,{"$f":"ROUND(SUM($C$3:$C20),2)-SUM(E$2:E19)","v":525.26000000000022},525.25,525.25,"",1.0000000000218279E-2,""]
[null,null,186.87463645249213,186.87,{"$f":"ROUND(SUM($C$3:$C21),2)-SUM(E$2:E20)","v":186.8700000000008},186.87,186.87,"","",""]
[null,null,731.73327691775671,731.73,{"$f":"ROUND(SUM($C$3:$C22),2)-SUM(E$2:E21)","v":731.73999999999978},731.73,731.73,"",9.9999999997635314E-3,""]
[null,null,629.67516929952444,629.66999999999996,{"$f":"ROUND(SUM($C$3:$C23),2)-SUM(E$2:E22)","v":629.67000000000007},629.67999999999995,629.67999999999995,-9.9999999999909051E-3,-9.9999999998772182E-3,""]
[null,null,76.543445401375038,76.540000000000006,{"$f":"ROUND(SUM($C$3:$C24),2)-SUM(E$2:E23)","v":76.539999999999054},76.540000000000006,76.540000000000006,"","",""]
[null,null,796.27098206960591,796.27,{"$f":"ROUND(SUM($C$3:$C25),2)-SUM(E$2:E24)","v":796.27000000000044},796.27,796.27,"","",""]
[null,null,718.87609015215787,718.88,{"$f":"ROUND(SUM($C$3:$C26),2)-SUM(E$2:E25)","v":718.8799999999992},718.88,718.88,"","",""]
[null,null,822.83693120276348,822.84,{"$f":"ROUND(SUM($C$3:$C27),2)-SUM(E$2:E26)","v":822.84000000000015},822.84,822.84,"","",""]
[null,null,816.42653791487487,816.43,{"$f":"ROUND(SUM($C$3:$C28),2)-SUM(E$2:E27)","v":816.42000000000007},816.43,816.43,"",-9.9999999998772182E-3,""]
[null,null,815.42994024853044,815.43,{"$f":"ROUND(SUM($C$3:$C29),2)-SUM(E$2:E28)","v":815.43000000000029},815.43,815.43,"","",""]
[null,null,925.45135014760399,925.45,{"$f":"ROUND(SUM($C$3:$C30),2)-SUM(E$2:E29)","v":925.46000000000095},925.45,925.45,"",1.00000000009004E-2,""]
[null,null,130.59914361898927,130.6,{"$f":"ROUND(SUM($C$3:$C31),2)-SUM(E$2:E30)","v":130.59000000000015},130.6,130.6,"",-9.9999999998487965E-3,""]
[null,null,743.13804885743468,743.14,{"$f":"ROUND(SUM($C$3:$C32),2)-SUM(E$2:E31)","v":743.1399999999976},743.14,743.14,"","",""]
[null,null,397.66616506415829,397.67,{"$f":"ROUND(SUM($C$3:$C33),2)-SUM(E$2:E32)","v":397.67000000000189},397.67,397.67,"","",""]
[null,null,759.5541377526614,759.55,{"$f":"ROUND(SUM($C$3:$C34),2)-SUM(E$2:E33)","v":759.54999999999927},759.55,759.55,"","",""]
[null,null,517.7971852922675,517.79999999999995,{"$f":"ROUND(SUM($C$3:$C35),2)-SUM(E$2:E34)","v":517.79999999999927},517.79999999999995,517.79999999999995,"","",""]
[null,null,668.91988469458875,668.92,{"$f":"ROUND(SUM($C$3:$C36),2)-SUM(E$2:E35)","v":668.92000000000189},668.92,668.92,"","",""]
[null,null,927.62804808399301,927.63,{"$f":"ROUND(SUM($C$3:$C37),2)-SUM(E$2:E36)","v":927.63000000000102},927.63,927.63,"","",""]
[null,null,690.02998264265091,690.03,{"$f":"ROUND(SUM($C$3:$C38),2)-SUM(E$2:E37)","v":690.02999999999884},690.03,690.03,"","",""]
[null,null,10.443835438042836,10.44,{"$f":"ROUND(SUM($C$3:$C39),2)-SUM(E$2:E38)","v":10.43999999999869},10.44,10.44,"","",""]
[null,null,994.04588541696262,994.05,{"$f":"ROUND(SUM($C$3:$C40),2)-SUM(E$2:E39)","v":994.04999999999927},994.05,994.05,"","",""]
[null,null,719.36129325469597,719.36,{"$f":"ROUND(SUM($C$3:$C41),2)-SUM(E$2:E40)","v":719.36000000000058},719.36,719.36,"","",""]
[null,null,871.31921685258851,871.32,{"$f":"ROUND(SUM($C$3:$C42),2)-SUM(E$2:E41)","v":871.31999999999971},{"$f":"ROUND(C43,2)-SUM(F3:F41)","v":871.29000000000451},871.32,"","",-2.9999999995538928E-2]
[null,"Total",{"$f":"SUM(C3:C42)","v":23038.778281831463},23038.779999999995,23038.78,23038.78,23038.809999999994,-3.0000000000086402E-2,-2.9999999998324256E-2,-2.9999999995538928E-2]
[null,"ABS Difference to Original",null,{"$f":"SUMPRODUCT(ABS(D3:D42-$C$3:$C$42))","v":0.10722091329125938},{"$f":"SUMPRODUCT(ABS(E3:E42-$C$3:$C$42))","v":0.15334147456946212},{"$f":"SUMPRODUCT(ABS(F3:F42-$C$3:$C$42))","v":0.13505349650177045}]