# Factorial

#### ctrnz

##### New Member
Excel has excellent built in function FACT() which does n! calculations.
But I don't need 1*2*3*4*5. Instead I need 1+2+3+4+5 calculation.
Excel has such built in function?

Thank You,
AK

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### Jonmo1

##### MrExcel MVP
Welcome to the board...

Never Mind, I think I misunderstood your post...

Last edited:

#### Richard Schollar

##### MrExcel MVP
Not as such but you can do this:

=SUMPRODUCT(ROW(INDIRECT("1:" & A1)))

Where A1 holds the upper bound number (in this case 5).

#### barry houdini

##### MrExcel MVP
Hello ctrnz, welcome to MrExcel

If you have n in A1 then to sum all integers from 1 to n try

=(A1+A1^2)/2

e.g. if A1 contains 5 the above formula in B1 gives 15

#### MrKowz

##### Well-known Member

Hmm, So you need something more akin to a SUMMATION.

Here is a UDF possibility:

Code:
``````Public Function SUMMATION(n As Integer)
Dim i As Integer
SUMMATION = 0
For i = 1 To n
SUMMATION = SUMMATION + i
Next i
End Function``````

#### ctrnz

##### New Member
Not as such but you can do this:

=SUMPRODUCT(ROW(INDIRECT("1:" & A1)))

Where A1 holds the upper bound number (in this case 5).

It is what i needed. Not familiar with INDIRECT function but it woked just as imagined
Thank You veru much!

#### Richard Schollar

##### MrExcel MVP

Thanks but you really should use Barry's approach...

#### tusharm

##### MrExcel MVP
The sum of all integers 1, 2, ..., n is n*(n+1)/2. I prefer this formulation to Barry's (n+n^2)/2, though the two are algebraically the same.
Excel has excellent built in function FACT() which does n! calculations.
But I don't need 1*2*3*4*5. Instead I need 1+2+3+4+5 calculation.
Excel has such built in function?

Thank You,
AK

#### barry houdini

##### MrExcel MVP
Hello Tushar,

Why do you prefer that version? Is it more transparent....or is there something else?

#### riaz

##### Well-known Member
If I might be so bold to add to Tushar's post

All the solutions here presented start from adding up from 1 upwards. If you need to have your starting point as something else, you need to modify to say

=(A1*((A1)+1)/2)-(B1*((B1)-1)/2)

where A1 houses the "top of the range" to be counted to, and B1 is the starting point (both numbers inclusive).

Replies
4
Views
47
Replies
3
Views
78
Replies
5
Views
201
Replies
13
Views
256
Replies
3
Views
84

1,132,672
Messages
5,654,669
Members
418,147
Latest member
dorkas

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