How to calculate cumulative % based off of the last row of another column

B

Bath93

New Member
Joined
Oct 12, 2016
Messages
2
I am trying to find the cumulative percent for a pareto chart. The pareto is based off of the amount of time for required tests vs cumulative percent of a given date range. I update the date range from a date picker and it pulls data from a database and places it onto my sheet. I then copy the data and remove duplicates, then sumif to the last row of the type of test to add up the time. Then I find cumulative time. The amount of required tests can change based off the selected date range, so to apply the formulas properly I find the last row of the required tests and copy the formulas down to that row. But I cant seem to get the cumulative % to cooperate with me. The normal code "=N50/$N$72" works fine for what I have shown, but if I update the date and there are less or more tests, then it wont calculate the cumulative % because there wont be a value there or it will be the wrong value. The 1st required test column is actually much longer, the second column is the list without duplicates. Any help would greatly be appreciated!

This is the code I am using.
Required Test1 cell is "I49"
Code: 
Sub formulapaint()

  Dim LastRow As Long
    With ActiveSheet
        LastRow = .Cells(.Rows.Count, "M").End(xlUp).Row
    End With
    
Range("M50:M" & LastRow).Formula = "=SUMIF($I$50:$I$65000,L50,$J$50:$J$65000)"
Range("N51:N" & LastRow).Formula = "=N50+M51"
' everything up to this point works fine, after this point I'm not sure about and have just been messing around trying to get it to work
Dim OLastRow As Long
    With ActiveSheet
        OLastRow = .Cells(.Rows.Count, "N").End(xlUp).Row
    End With

QLastRow = Range("N" & OLastRow)
Range("O50:O" & LastRow).Formula = N50 / QLastRow
       
End Sub
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 596"]
<colgroup><col width="171" style="width: 128pt; mso-width-source: userset; mso-width-alt: 6253;"> <col width="46" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;"> <col width="64" style="width: 48pt;"> <col width="171" style="width: 128pt; mso-width-source: userset; mso-width-alt: 6253;"> <col width="88" style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;"> <col width="138" style="width: 104pt; mso-width-source: userset; mso-width-alt: 5046;"> <col width="116" style="width: 87pt; mso-width-source: userset; mso-width-alt: 4242;"> <tbody>[TR]
[TD="class: xl67, width: 171, bgcolor: #E26B0A"]Required Test1[/TD]
[TD="class: xl68, width: 46, bgcolor: #E26B0A"]Time[/TD]
[TD="class: xl67, width: 64, bgcolor: #E26B0A"] [/TD]
[TD="class: xl67, width: 171, bgcolor: #E26B0A"]Required Test2[/TD]
[TD="class: xl68, width: 88, bgcolor: #E26B0A"]Total Time[/TD]
[TD="class: xl68, width: 138, bgcolor: #E26B0A"]Cumulative Time[/TD]
[TD="class: xl69, width: 116, bgcolor: #E26B0A"]Cumulative %[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Symmetry[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:05[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Control Cut[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]116:52[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]116:52[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]36.68%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Special Cut[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:21[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Complete Cut[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]78:17[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]195:09[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]61.25%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Symmetry[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:05[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Control Plan Measurements[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]45:45[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]240:54[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]75.61%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Zeiss[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Tool Change[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]17:07[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]258:01[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]80.98%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Complete Cut[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2:24[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Thread Demolding[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]14:20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]272:21[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]85.48%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]1st Piece Inspection[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:36[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]1st Piece Inspection[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]9:11[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]281:32[/TD]
[TD="class: xl71, bgcolor: transparent, align: right"]88.37%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Symmetry[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:05[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Surface Finish (Rz)[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]8:03[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]289:35[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]90.89%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Complete Cut[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2:26[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Symmetry[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]6:40[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]296:15[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]92.98%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Control Cut[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1:23[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Special Cut[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5:41[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]301:56[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]94.77%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Thread Demolding[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:16[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Change Over[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]5:28[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]307:24[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]96.48%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Control Cut[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2:36[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Zeiss[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]3:59[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]311:23[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]97.73%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Thread Demolding[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:30[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Helix and Inclination Angle[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1:44[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]313:07[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]98.28%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Symmetry[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:05[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Special[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1:18[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]314:25[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]98.69%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Control Cut[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]2:27[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]IncAngle + Mid to Mid[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1:05[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]315:30[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]99.03%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Symmetry[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:05[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Ball Groove-Mahr[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:52[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]316:22[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]99.30%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Control Plan Measurements[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:50[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]ZeissPT[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:31[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]316:53[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]99.46%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Surface Finish (Rz)[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:10[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Pt (Finish)[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:28[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]317:21[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]99.61%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Zeiss[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:00[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Chamfer[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:20[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]317:41[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]99.71%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Control Plan Measurements[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:40[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Adjustment[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:17[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]317:58[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]99.80%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Tool Change[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:19[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Tooth Profile (Mahr)[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:16[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]318:14[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]99.88%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Control Cut[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1:15[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Chamfer sensor[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:10[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]318:24[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]99.94%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Control Cut[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]1:34[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Length[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:07[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]318:31[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]99.97%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Thread Demolding[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:20[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Pt (Mahr)[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]0:05[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]318:36[/TD]
[TD="class: xl70, bgcolor: transparent, align: right"]100.00%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

J
  • Question Question
pulling data from one chart to another
Replies
1
Views
811
jb00976
J
H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff
S
  • Question Question
pivot table calculated field - by broken out values in a column
Replies
1
Views
642
smakatura
S
B
  • Question Question
How to deduct one row
Replies
4
Views
835
Fluff
Fluff
G
  • Question Question
Transpose matching data from columns to rows
Replies
3
Views
857
bwaaack
B

Forum statistics

Threads
1,223,911
Messages
6,175,331
Members
452,636
Latest member
laura12345

Share this page

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
Back
Top