Array's cumulative sum

Question

I have an integer array of values and want to find a simple way of calculating its cumulative sum (S = Data(1) + Data(2) + ... + Data(x)).

I already created this function:

Function CumulativeSum(Data() As Integer, k As Integer) As Integer
    For entry = 1 To k
        CumulativeSum = CumulativeSum + Data(entry)
    Next entry
End Function

and it's working fine. However, I wonder if there's a better way of doing it (mainly without the use of any extra function and essentially using only excel functions like Application.Sum). I made a small search on the web but didn't find anything on this basis.

I know I'm not asking to correct any code and I'm just asking for an alternative which is not the real purpose of this forum. However, I also suspect that the answer could be simple, so... If anyone care to help me I'll appreciate very, very much! If you find an answer to a similar question, please share the link with me and I'll remove this one.

I'm very sorry for probably my lack of explicitly on my demand: I simply want to find a simple way of calculating the cumulative sum using simple functions on the macro routine itself, WITHOUT using the CumulativeSum function I created or any other function created by the user.

why don't you just add a simple sample array with 4 numbers and what you would like to achieve? — MacroMarc, Sep 21 '17 at 18:35
@MacroMarc, thank you for the advice. It probably would helped to get a good answer sooner. However you already gave me exactly the answer I intended! — Pspl, Sep 22 '17 at 07:21
@Pspl, your example and the solution from @MacroMarc are not computing a cumulative sum. It simply computes a regular sum from index 1 to i on a given array. You could also call it sum of a subset or partial sum. For instance in your example you'd have to change `CumulativeSum = CumulativeSum + Data(entry)` by `CumulativeSum = CumulativeSum + Data(entry) * (k - entry + 1)` to compute the cumulative sum up to index k. So please update your question. — Florent B., Sep 22 '17 at 16:30

Vityata · Answer 1 · 2017-09-21T18:27:58.307

If you want to achieve a cumulative array array like Array(a,a+b,a+b+c) from Array(a,b,c), then this is the function to achieve it, if you want to pass start and end parameters:

Public Sub TestMe()

    Dim outputArray     As Variant
    Dim inputArray      As Variant
    Dim counter         As Long

    inputArray = Array(1, 2, 4, 8, 16, 32, 64)

    outputArray = generateCumulativeArray(inputArray, 1, 4)
    For counter = LBound(outputArray) To UBound(outputArray)
        Debug.Print outputArray(counter)
    Next counter

    outputArray = generateCumulativeArray(inputArray, toValue:=4)
    For counter = LBound(outputArray) To UBound(outputArray)
        Debug.Print outputArray(counter)
    Next counter

End Sub

Public Function generateCumulativeArray(dataInput As Variant, _
                                        Optional fromValue As Long = 0, _
                                        Optional toValue As Long = 0) As Variant

    Dim i                   As Long
    Dim dataReturn          As Variant
    ReDim dataReturn(0)
    dataReturn(0) = dataInput(fromValue)

    For i = 1 To toValue - fromValue
        ReDim Preserve dataReturn(i)
        dataReturn(i) = dataReturn(i - 1) + dataInput(fromValue + i)
    Next i
    generateCumulativeArray = dataReturn
End Function

Concerning just summing an array, this is the way to do it: You can use the WorksheetFunction. and you can pass the array as an argument. Thus, you get all the functions, e.g. Average, Min, Max etc:

Option Explicit

Public Sub TestMe()

    Dim k As Variant
    k = Array(2, 10, 200)
    Debug.Print WorksheetFunction.Sum(k)
    Debug.Print WorksheetFunction.Average(k)

End Sub

If you want the sum from a given start to a given end, the easiest way is probably to make a new array and to sum it completely. In Python this is called slicing, in VBA this could be done a bit manually:

Public Sub TestMe()

    Dim varArr          As Variant
    Dim colSample       As New Collection

    varArr = Array(1, 2, 4, 8, 16, 32, 64)

    colSample.Add (1)
    colSample.Add (2)
    colSample.Add (4)
    colSample.Add (8)

    Debug.Print WorksheetFunction.Sum(generateArray(varArr, 2, 4))
    Debug.Print WorksheetFunction.Sum(generateArray(colSample, 2, 4))

End Sub

Public Function generateArray(data As Variant, _
                              fromValue As Long, _
                              toValue As Long) As Variant

    Dim i                   As Long
    Dim dataInternal        As Variant
    Dim size                As Long

    size = toValue - fromValue
    ReDim dataInternal(size)

    For i = LBound(dataInternal) To UBound(dataInternal)
        dataInternal(i) = data(i + fromValue)
    Next i

    generateArray = dataInternal

End Function

The idea is that the generateArray function returns a new array. Thus, its complete sum is what you need. It works also with collections, not only with arrays. Be careful, when using collections, they start with index 1, while arrays (usually) start with 0. If you want to use the same indexing for Arrays and Collections, then change the generateArray function to this one:

Public Function generateArray(data As Variant, _
                              fromValue As Long, _
                              toValue As Long) As Variant

    Dim i                   As Long
    Dim dataInternal        As Variant
    Dim size                As Long

    size = toValue - fromValue
    ReDim dataInternal(size)

    If IsArray(data) Then
        For i = LBound(dataInternal) To UBound(dataInternal)
            dataInternal(i) = data(i + fromValue)
        Next i
    Else
        For i = LBound(dataInternal) To UBound(dataInternal)
            dataInternal(i) = data(i + fromValue + 1)
        Next i
    End If

    generateArray = dataInternal

End Function

Or write Option Base 1 on top and the array will start from 1 (not advised!).

A cumulative sum should return `a + a+b + a+b+c` for the input `[a, b, c]`. Unless mistaken, your answer doesn't address the question since slicing the array to sum it with `WorksheetFunction.Sum` will compute a regular sum and not a cumulative sum. — Florent B., Sep 21 '17 at 15:35
@FlorentB. - thanks, I have just edited, including this information. — Vityata, Sep 21 '17 at 18:23
@FlorentB. The OP's function doesn't return a cumulative sum either, and he/she stated that the expected result should be *"`= Data(1) + Data(2) + ... + Data(x)`"*, so I'm guessing that what's required is actually a regular sum, and not a cumulative sum **although the OP included `cumulative sum` in the title, question, and tags**. — 41686d6564 stands w. Palestine, Sep 21 '17 at 18:37
@AhmedAbdelhameed my `CumulativeSum` function returns a cumulative sum... At least on my machine! `CumulativeSum(Data, 3) = Data(1) + Data(2) + Data(3)`. Did you even try it?? — Pspl, Sep 22 '17 at 07:15
@Pspl I'm not a mathematician, and I might be wrong, but AFAIK a Cumulative Sum is a sequence *not a single numeric value*, hence a function that returns a Cumulative Sum should return an array/collection instead of an integer. e.g. `{ Data(1), Data(1) + Data(2), Data(1) + Data(2) + Data(3) }`. Yours is a Sum *(regular sum)* and not a Cumulative Sum (otherwise, what's the difference between the two?). Check [this definition](http://mathworld.wolfram.com/CumulativeSum.html). — 41686d6564 stands w. Palestine, Sep 22 '17 at 11:41
@AhmedAbdelhameed sorry for keep this discussion, but I am a mathematician (really) and the definition you mentioned say "the cumulative sums of the sequence (...) are...". This is plural. Each sum is a cumulative sum (how I mentioned) and all of them makes a sequence named "partial sums sequence". However, it seems to me, that this is a "muu" discussion (like a cow makes, it doesn't matter). It's only a linguistic misunderstanding! Honesty I think I was pretty clear on my question... — Pspl, Sep 22 '17 at 13:38

Mrig · Answer 2 · 2017-09-21T14:06:28.617

2

For cumulative sum try the following

Function CumulativeSum(Data() As Integer, k As Integer) As Integer
    Dim tempArr
    tempArr = Data
    ReDim Preserve temp(0 To k - 1)
    CumulativeSum = WorksheetFunction.Sum(tempArr)
End Function

EDIT :

Sub Demo()
    Dim MyArray
    Dim i As Long
    MyArray = Array(1, 2, 3, 4, 5, 6, 7, 8, 9)

    Debug.Print MyArray(LBound(MyArray))
    For i = LBound(MyArray) + 1 To UBound(MyArray)
        MyArray(i) = MyArray(i - 1) + MyArray(i)
        Debug.Print MyArray(i)
    Next i
End Sub

Above code updates array arr from
1, 2, 3, 4, 5, 6, 7, 8, 9
to
1, 3, 6, 10, 15, 21, 28, 36, 45

edited Sep 21 '17 at 14:06

answered Sep 21 '17 at 12:38

Mrig

11,612
2
13
27

There's no need to adjust my function. Mine's working fine. What I want is determine a way of doing the same without a function in a simple way. Sorry. – Pspl Sep 21 '17 at 13:11
@Pspl - If I am not wrong, you are passing an array (of say 10 item) with k (say 5), then you should be able to sum first 5 items of the passed array. – Mrig Sep 21 '17 at 13:13
@Pspl - If that's correct then that's what my function do in a simple way compared to your function. – Mrig Sep 21 '17 at 13:16
Yes. You're not wrong! But a function like `sum(array, ...)` with some kind of argument would be better... That's what I'm looking for... – Pspl Sep 21 '17 at 13:17
1

@Pspl - See updated code. This code converts array `MyArray` with `10,20,30,40,50` to `10,30,60,100,150`. – Mrig Sep 21 '17 at 13:43

score 2 · Accepted Answer · answered Sep 21 '17 at 19:07

2

Try this:

Sub test()
Dim arr As Variant
arr = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)

Dim mySum As Long, k As Long
Dim wsf As WorksheetFunction
Set wsf = Application.WorksheetFunction

k = 6
'operative line below
mySum = wsf.Sum(wsf.Index(arr, 1, Evaluate("ROW(1:" & k & ")")))

MsgBox mySum

End Sub

answered Sep 21 '17 at 19:07

MacroMarc

3,214
2
11
20

1

Exactly what I was looking for.... A way of getting the cumulative sum using only simple excel functions! Thank you very much!! – Pspl Sep 22 '17 at 07:10

score 0 · Answer 4 · answered May 14 '20 at 09:32

This function returns an array with the cumulative sum of the original vector.

Function CumuVector(Vec As Variant) As Variant()
Dim element, v() As Variant
Dim i As Integer
lastindexinvec = 0
For Each element In Vec
    lastindexinvec = last + 1
Next 
ReDim v(lastindexinvec) As Variant
i = 0
For Each element In Vec
    If i < last Then
    sum = sum + element
    v(i) = sum
    i = i + 1
    End If
Next
CumuVector = v
End Function

Array's cumulative sum

4 Answers4