Cummulative Totals

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carlee - 23 Sep 2007 22:58 GMT

Hi all,

I use the code below to produce cummulative totals for a given month (Days
1-31)

Issue:
If i use this code to produce a row of data, for a mid-month report,
specifically a graph, where i run out of data, the unknown days contain the
last known value.

Question:
How can tell the code that if there is no value for the date, display a '0',
otherwise, calculate the cummunlative value for the day in question.

Code:
Range("C58") = Range("C11")
Range("D58") = Application.WorksheetFunction.Sum(Range("C58,D11"))
Range("E58") = Application.WorksheetFunction.Sum(Range("D58,E11"))
Range("F58") = Application.WorksheetFunction.Sum(Range("E58,F11"))
Range("G58") = Application.WorksheetFunction.Sum(Range("F58,G11"))
Range("H58") = Application.WorksheetFunction.Sum(Range("G58,H11"))

Range("I58") = Application.WorksheetFunction.Sum(Range("H58,I11"))

Range("J58") = Application.WorksheetFunction.Sum(Range("I58,J11"))
Range("K58") = Application.WorksheetFunction.Sum(Range("J58,K11"))
Range("L58") = Application.WorksheetFunction.Sum(Range("K58,L11"))
Range("M58") = Application.WorksheetFunction.Sum(Range("L58,M11"))

Range("N58") = Application.WorksheetFunction.Sum(Range("M58,N11"))
Range("O58") = Application.WorksheetFunction.Sum(Range("N58,O11"))
Range("P58") = Application.WorksheetFunction.Sum(Range("O58,P11"))
Range("Q58") = Application.WorksheetFunction.Sum(Range("P58,Q11"))
Range("R58") = Application.WorksheetFunction.Sum(Range("Q58,R11"))

Range("S58") = Application.WorksheetFunction.Sum(Range("R58,S11"))
Range("T58") = Application.WorksheetFunction.Sum(Range("S58,T11"))
Range("U58") = Application.WorksheetFunction.Sum(Range("T58,U11"))
Range("W58") = Application.WorksheetFunction.Sum(Range("V58,W11"))

Range("X58") = Application.WorksheetFunction.Sum(Range("W58,X11"))
Range("Y58") = Application.WorksheetFunction.Sum(Range("X58,Y11"))
Range("Z58") = Application.WorksheetFunction.Sum(Range("Y58,Z11"))
Range("AA58") = Application.WorksheetFunction.Sum(Range("Z58,AA11"))
Range("AB58") = Application.WorksheetFunction.Sum(Range("AA58,AB11"))

Range("AC58") = Application.WorksheetFunction.Sum(Range("AB58,AC11"))
Range("AD58") = Application.WorksheetFunction.Sum(Range("AC58,AD11"))
Range("AE58") = Application.WorksheetFunction.Sum(Range("AD58,AE11"))
Range("AF58") = Application.WorksheetFunction.Sum(Range("AE58,AF11"))
Range("AG58") = Application.WorksheetFunction.Sum(Range("AF58,AG11"))

Signature

Carlee

Reply to this Message

Don Guillett - 23 Sep 2007 23:27 GMT

This should make your formulas a bit more efficiently.

Sub df()
'Range("C58") = Range("C11")
Cells(58, 3) = Cells(11, 3)
For i = 4 To 34
Cells(58, i) = Application.Sum(Range(Cells(58, i), Cells(11, i + 1)))
Next
End Sub

Not quite sure about the rest but maybe you can use an IF
sub df1()
Cells(58, 3) = Cells(11, 3)
for i=4 to 34
if cells(i,11)>0 then 'or cells(i,58)
Cells(58, i) = Application.Sum(Range(Cells(58, i), Cells(11, i + 1)))
end if
next
end sub

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi all,
>
[quoted text clipped - 56 lines]
> Range("AG58") =
> Application.WorksheetFunction.Sum(Range("AF58,AG11"))

Reply to this Message

Carlee - 24 Sep 2007 00:18 GMT

Hi there,
i am a bit unsure how to implement this code. How can 'call' this on my
sheet to make it work?

Signature

Carlee

> This should make your formulas a bit more efficiently.
>
[quoted text clipped - 75 lines]
> > Range("AG58") =
> > Application.WorksheetFunction.Sum(Range("AF58,AG11"))

Reply to this Message

Don Guillett - 24 Sep 2007 01:27 GMT

Just try it. If it doesn't do it for you don't save.....
I could probably figure out the rest if I saw your workbook. Send to my
address below, if desired.

Signature

Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett1@austin.rr.com

> Hi there,
> i am a bit unsure how to implement this code. How can 'call' this on my
[quoted text clipped - 102 lines]
>> > Range("AG58") =
>> > Application.WorksheetFunction.Sum(Range("AF58,AG11"))

Reply to this Message

Carlee - 24 Sep 2007 01:34 GMT

Hi there,
Well...I tried it, and it worked, however, the numbers were not correct.
I will sent the workbook along

Signature

Carlee

> Just try it. If it doesn't do it for you don't save.....
> I could probably figure out the rest if I saw your workbook. Send to my
[quoted text clipped - 106 lines]
> >> > Range("AG58") =
> >> > Application.WorksheetFunction.Sum(Range("AF58,AG11"))

Reply to this Message