# help on a formula

#### mark gardner

##### Board Regular
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?

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### Willem!

##### Board Regular
Hi,

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

Code:
``````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

##### Active Member
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

##### MrExcel MVP
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)

Last edited:

#### Weaver

##### Well-known Member
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!

Replies
19
Views
373
Replies
1
Views
93
Replies
0
Views
159
Replies
7
Views
201
Replies
2
Views
144

1,191,191
Messages
5,985,211
Members
439,947
Latest member
fabiannic

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back