help on a formula

[mark gardner]

I have a sheet where I need to input a number and if the number is 1, I need a corresponding cell to return a 1, if it is a 2, I need a 3 (1+2), if it is a 3, I need a 6 (1+2+3)...etc. Is there a formula I can use?

thanks in advance

 

[Willem!]

Hi,

I would go VBA on this one. Try something like:

Function Summ(rngInput As Range)
Dim i As Long
Dim j As Long
 
    j = 0
    For i = 1 To rngInput.Value
        j = j + i
    Next
Summ = j
End Function

HTH

 

[Swaroon]

Instead of a formula that does it all, you could try a simple work around

on a separate tab,
cell A1 enter 1,cell A2 enter 2 etc
cell B1 enter 1,cell B2 enter 3 etc

Then do a vlookup on the first tab, target cell being where you have the 1, 2 etc, range being the cols A and B on the second tab.

VLookup sees a 1 and returns a 1, sees a 2 returns a 3 etc

Might be a bit simple but it wold work.

 

[mikerickson]

if your cell of entry is A1

=A1*(A1+1)/2

You are summing an arithmetic sequence
1=1
1+2 = 3
1+2+3+...+n = n*(n+1)/2

a + (a+t) + (a+2t) + ... + (a+nt) = (n+1)*(2a+nt) / (n+1)

 

[Weaver]

if your cell of entry is A1

=A1*(A1+1)/2

You are summing an arithmetic sequence
1=1
1+2 = 3
1+2+3+...+n = n*(n+1)/2

a + (a+t) + (a+2t) + ... + (a+nt) = (n+1)*(2a+nt) / (n+1)

My solution too - saw it on Think of a Number, many years ago!