Problem with showing right numbers after round

nova_sweet

New Member
Joined
Nov 27, 2016
Messages
34
Hello,

I am struggeling with the following problem.

I tried to calculate the amount of casenumbers for each worker (1 to 1000 cases) by taking their weekly work-hours into account.
I tried to let Excel do the math without rounding any numbers but in the end I have to because I cannot have a case numbered "345,78", it must be 346. And there it gets wrong sometimes. Not always....I am not very good at math, so probably my math-understanding of it all is the problem.

I uploaded a version of the sheet, because I am not sure how to explain it better. If you check the numbers you will see that somewhere it turns wrong.

Thankful for any input.

Best regards,
nova_sweet

https://www.file-upload.net/download-12836458/Casenumbersfromto.xlsx.html
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

nova_sweet

New Member
Joined
Nov 27, 2016
Messages
34
Maybe better to show it this way; I did not know how to do this at first ;) ....still not working???

HTML:
ABCDEFGH71a20,55,3735%53,735255570010545482s20,55,3735%53,735255570551075493d3910,2228%102,2280472108210102104f307,8637%78,6369593721128879115g3910,2228%102,2280472289391102126h4110,7471%107,4705111392498107137j3910,2228%102,2280472499600102148k307,8637%78,6369593760167979159l256,5531%65,53079948680744661610y3910,2228%102,22804727458471021711r3910,2228%102,22804728489491021812r19,55,1114%51,114023599501000511913v00,0000%00201400,0000%00211500,0000%00221600,0000%00231700,0000%00241800,0000%00251900,0000%00262000,0000%00272100,0000%00282200,0000%00292300,0000%00302400,0000%00312500,0000%0032381,510003334[CENTER][COLOR=#161120][B]Cases 1 to 1000[/B][/COLOR][/CENTER]

Worksheet FormulasCellFormulaC32=SUM(C7:C31)D7=C7/C32E7=D7*1000F7=1G7=IF($E7>0,ROUND(SUM($E$7:$E7),0),"")H7=E7D8=C8/C32E8=D8*1000F8=IF($E8>0,SUM($E7:$E$7)+1,"")G8=IF($E8>0,SUM($E$7:$E8),"")H8=E8D9=C9/C32E9=D9*1000F9=IF($E9>0,SUM($E$7:$E8)+1,"")G9=IF($E9>0,SUM($E$7:$E9),"")H9=E9D10=C10/C32E10=D10*1000F10=IF($E10>0,SUM($E$7:$E9)+1,"")G10=IF($E10>0,SUM($E$7:$E10),"")H10=E10D11=C11/C32E11=D11*1000F11=IF($E11>0,SUM($E$7:$E10)+1,"")G11=IF($E11>0,SUM($E$7:$E11),"")H11=E11D12=C12/C32E12=D12*1000F12=IF($E12>0,SUM($E$7:$E11)+1,"")G12=IF($E12>0,SUM($E$7:$E12),"")H12=E12D13=C13/C32E13=D13*1000F13=IF($E13>0,SUM($E$7:$E12)+1,"")G13=IF($E13>0,SUM($E$7:$E13),"")H13=E13D14=C14/C32E14=D14*1000F14=IF($E14>0,SUM($E$7:$E13)+1,"")G14=IF($E14>0,SUM($E$7:$E14),"")H14=E14D15=C15/C32E15=D15*1000F15=IF($E15>0,SUM($E$7:$E14)+1,"")G15=IF($E15>0,SUM($E$7:$E15),"")H15=E15D16=C16/C32E16=D16*1000F16=IF($E16>0,SUM($E$7:$E15)+1,"")G16=IF($E16>0,SUM($E$7:$E16),"")H16=E16D17=C17/C32E17=D17*1000F17=IF($E17>0,SUM($E$7:$E16)+1,"")G17=IF($E17>0,SUM($E$7:$E17),"")H17=E17D18=C18/C32E18=D18*1000F18=IF($E18>0,SUM($E$7:$E17)+1,"")G18=IF($E18>0,SUM($E$7:$E18),"")H18=E18D19=C19/C32E19=D19*1000F19=IF($E19>0,SUM($E$7:$E18)+1,"")G19=IF($E19>0,SUM($E$7:$E19),"")H19=E19D20=C20/C32E20=D20*1000F20=IF($E20>0,SUM($E$7:$E19)+1,"")G20=IF($E20>0,SUM($E$7:$E20),"")H20=E20D21=C21/C32E21=D21*1000F21=IF($E21>0,SUM($E$7:$E20)+1,"")G21=IF($E21>0,SUM($E$7:$E21),"")H21=E21D22=C22/C32E22=D22*1000F22=IF($E22>0,SUM($E$7:$E21)+1,"")G22=IF($E22>0,SUM($E$7:$E22),"")H22=E22D23=C23/C32E23=D23*1000F23=IF($E23>0,SUM($E$7:$E22)+1,"")G23=IF($E23>0,SUM($E$7:$E23),"")H23=E23D24=C24/C32E24=D24*1000F24=IF($E24>0,SUM($E$7:$E23)+1,"")G24=IF($E24>0,SUM($E$7:$E24),"")H24=E24D25=C25/C32E25=D25*1000F25=IF($E25>0,SUM($E$7:$E24)+1,"")G25=IF($E25>0,SUM($E$7:$E25),"")H25=E25D26=C26/C32E26=D26*1000F26=IF($E26>0,SUM($E$7:$E25)+1,"")G26=IF($E26>0,SUM($E$7:$E26),"")H26=E26D27=C27/C32E27=D27*1000F27=IF($E27>0,SUM($E$7:$E26)+1,"")G27=IF($E27>0,SUM($E$7:$E27),"")H27=E27D28=C28/C32E28=D28*1000F28=IF($E28>0,SUM($E$7:$E27)+1,"")G28=IF($E28>0,SUM($E$7:$E28),"")H28=E28D29=C29/C32E29=D29*1000F29=IF($E29>0,SUM($E$7:$E28)+1,"")G29=IF($E29>0,SUM($E$7:$E29),"")H29=E29D30=C30/C32E30=D30*1000F30=IF($E30>0,SUM($E$7:$E29)+1,"")G30=IF($E30>0,SUM($E$7:$E30),"")H30=E30D31=C31/C32E31=D31*1000F31=IF($E31>0,SUM($E$7:$E30)+1,"")G31=IF($E31>0,SUM($E$7:$E31),"")H31=E31E32=SUM(E7:E31)
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,132,686
Messages
5,654,750
Members
418,150
Latest member
Yerraashok

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top