Sigma sums

[Larry Coon]

I'm looking for a formula to do sums, but the "sigma"
kind of sum (iterating over a formula and summing the
results), not the SUM() function kind of sums (summing
over a range of cells). For example, if cell A1
contains the number of iterations, A2 contains some
constant, and my formula is 1 + $A$2 * (i - 1), where
i is the counter from 1 to $A$1, I want something like
the following pseudocode.

int sum = 0;
for (int i = 1; i <= $A$1; i++) {
sum = sum + (1 + $A$2 * (i - 1));
}

Does that make sense?

Thanks for any help.

 

[Luke M]

The easiest way may be to simply create a UDF. (user-defined-function). Open
up the VBE (Visual Basic Editor) by pressing Alt+F11. Now, we need a blank
module, so go to Insert, Module. Paste the following in:


'=========================>
Function SigmaSum(StartValue As Integer, _
EndValue As Integer, Constant As Variant) As Variant

SigmaSum = 0
For i = StartValue To EndValue
SigmaSum = SigmaSum + (1 + Constant * (i - 1))
Next
End Function
'=========================>



Close the VBE. Back in your workbook, your formula becomes:
=SigmaSum(1,$A$1,$A$2)

 

[Bernard Liengme]

If A1 = 5 and A2 =2, then
=1+SUMPRODUCT(A2*ROW(1:100)*(ROW(1:100)<A1))
will compute
1+ (2+4+6+8) = 21

If A1 is > 100, adjust the 100 in the formula to a larger value
Any use?
best wishes