Sum arithmetic progression cells ?

hpbaxxter · May 23, 2014

Hi!
My question is : Is it possible to create a formula that would give the sum of cells that are in arithmetic progression in excel?

Example:
Let's first choose 4 cells that are in arithmetic progression, B14 , B20 , B26 and B32 for instance(the common difference here is 6). So what I want to do is: I want to type a formula in another cell, lets suppose C5, that will automatically give me the sum of the values of B14,B20,B26 and B32. I am aware that I can just type on C5 =B14+B20+B26+B32 but and if I wanted the sum of 90 cells??Wouldn't it be too much work to type all the cells? Does Anyone know a formula for it?

thanks! :]

VBA Geek · May 23, 2014

Code:

=sumproduct(b14:b32*(mod(row(b14:b32)-row(b14),6)=0))

shg · May 23, 2014

If you have n terms of an arithmetic series whose first value is a1 and the constant difference is d, then the sum is

=n*a + n*(n-1)*d/2

hpbaxxter · May 23, 2014

it didn't work
I think you dint understand me !
I want to sum a specific cells which they are making an arithmetic progression (unlimited cells)
as :b14,b20, b26, b32 .......

shg · May 23, 2014

You don't need all of the cells, you only need the first cell, the difference, and the number of cells:

[Table="width:, class:grid"][tr][td] [/td][td]

A

[/td][td]

B

[/td][/tr]
[tr][td]

1

[/td][td]

14

[/td][td]A1: Input[/td][/tr]

[tr][td]

2

[/td][td]

20

[/td][td]A2: =A1+6[/td][/tr]

[tr][td]

3

[/td][td]

26

[/td][td]A3: =A2+6[/td][/tr]

[tr][td]

4

[/td][td]

32

[/td][td]A4: =A3+6[/td][/tr]

[tr][td]

5

[/td][td]

38

[/td][td]A5: =A4+6[/td][/tr]

[tr][td]

6

[/td][td]

44

[/td][td]A6: =A5+6[/td][/tr]

[tr][td]

7

[/td][td]

50

[/td][td]A7: =A6+6[/td][/tr]

[tr][td]

8

[/td][td]

56

[/td][td]A8: =A7+6[/td][/tr]

[tr][td]

9

[/td][td][/td][td][/td][/tr]

[tr][td]

10

[/td][td]

280

[/td][td]A10: =SUM(A1:A8)[/td][/tr]

[tr][td]

11

[/td][td]

280

[/td][td]A11: =8*A1 + 8*(8-1)*6/2[/td][/tr]
[/table]

EDIT: Perhaps I misunderstood; if you want to sum every Nth cell, then use VBA Geek's formula.

hpbaxxter · May 24, 2014

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]8[/TD]
[TD]=A1+A3+A5+A7+A9 ..[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]0[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD] 7
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Like in this table , I want to sum A1+A3+A5+A7+......... Can I write a function to do this calculate ?

VBA Geek · May 24, 2014

The 6 in my formula will determine how many rows to skip

Code:

=sumproduct(b14:b32*(mod(row(b14:b32)-row(b14),[B]6[/B])=0))

so in the above case each 6 rows are summed up, in your last case you need to replace 6 with 2 and also update the B14:B32 reference to A1:A17

shg · May 24, 2014

If you just want to specify the three values,

[Table="width:, class:grid"][tr][td] [/td][td]

A

[/td][td]

B

[/td][td]

C

[/td][td]

D

[/td][td]

E

[/td][/tr]
[tr][td]

1

[/td][td]

1

[/td][td][/td][td]

Beg

[/td][td]

2

[/td][td][/td][/tr]

[tr][td]

2

[/td][td]

2

[/td][td][/td][td]

Step

[/td][td]

3

[/td][td][/td][/tr]

[tr][td]

3

[/td][td]

3

[/td][td][/td][td]

Qty

[/td][td]

4

[/td][td][/td][/tr]

[tr][td]

4

[/td][td]

4

[/td][td][/td][td][/td][td]

26

[/td][td]D4: =SUMPRODUCT(INDEX(A:A, Beg):INDEX(A:A, Beg + Step * (Qty - 1)),
--(MOD(ROW(INDEX(A:A, Beg):INDEX(A:A, Beg + Step * (Qty - 1))) - Beg, Step) = 0))[/td][/tr]

[tr][td]

5

[/td][td]

5