Calculating cumulative percentage

[arian29]

I need help with automating cumulative percentage in VBA. I have the column "Amount" present.

What i am doing in excell (manual procedure)>>
<TABLE style="WIDTH: 364pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=484 border=0><COLGROUP><COL style="WIDTH: 74pt; mso-width-source: userset; mso-width-alt: 3584" span=2 width=98><COL style="WIDTH: 21pt; mso-width-source: userset; mso-width-alt: 1024" width=28><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 12pt; mso-width-source: userset; mso-width-alt: 585" width=16><COL style="WIDTH: 121pt; mso-width-source: userset; mso-width-alt: 5888" width=161><TBODY><TR style="HEIGHT: 12pt" height=16><TD class=xl86 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 74pt; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" width=98 height=16>Amount</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 74pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=98>Percent</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 21pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=28></TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=83>Formula used for cumulative percentage in previous cell(Percent)</TD><TD class=xl87 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 12pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=16> </TD><TD class=xl88 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 121pt; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" width=161> </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl74 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>91</TD><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>24%</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl77 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">D38/SUM($B$38:$B$49)</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl78 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>35</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>33%</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">(D39+D38)/SUM($B$38:$B$43)</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl80 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>38</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>43%</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">(D40+D39+D38)/SUM($B$38:$B$43)</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl80 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>98</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>69%</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">(D41+D40+D38+D39)/SUM($B$38:$B$43)</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl80 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 11.25pt" height=15><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 11.25pt; BACKGROUND-COLOR: transparent" align=right height=15>62</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right>86%</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">(D42+D41+D38+D39+D40)/SUM($B$38:$B$43)</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl80 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12pt" height=16><TD class=xl81 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 12pt; BACKGROUND-COLOR: transparent" align=right height=16>54</TD><TD class=xl82 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent" align=right>100%</TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl84 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">(D43+D42+D38+D39+D40+D41)/SUM($B$38:$B$43)</TD><TD class=xl83 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl85 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

 

[VoG]

Try like this

Excel Workbook
	C	D
37	Amount	Percent
38	91	24%
39	35	33%
40	38	43%
41	98	69%
42	62	86%
43	54	100%
Sheet1

 

[perco754]

I think you were looking for VBA right?

You simply loop through all rows updating the cumulated sum (partSum var) and setting the percentage value at each step.

The constSum is equal to your SUM($B$38:$B$43) but since I don't know th value of that sum I had to calculate an average based on your data (the value in cell B45)

Sub calcCumPercent()
 
    Dim firstRow As Integer, lastRow As Integer, r As Integer
    Dim partSum As Double, constSum As Double
    Dim rng As Range
    Dim ws As Worksheet
    Dim wb As Workbook
 
    Set wb = Application.ActiveWorkbook
    Set ws = wb.Sheets(1)
    Set rng = ws.Range("B45")
    firstRow = 39
    lastRow = 44
    partSum = 0
    constSum = rng.Value
 
    For r = firstRow To lastRow
        Set rng = ws.Cells(r, 5)
        partSum = partSum + rng.Value
        Set rng = ws.Cells(r, 6)
        rng.Value = partSum / constSum
    Next r
 
End Sub