Factorial

ctrnz

New Member
Joined
Nov 5, 2008
Messages
3
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
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board...

Never Mind, I think I misunderstood your post...
 
Last edited:

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Jun 30, 2008
Messages
6,653
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Nov 5, 2008
Messages
3
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
Joined
Apr 19, 2005
Messages
23,707

ADVERTISEMENT

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

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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
Joined
Mar 23, 2005
Messages
20,825
Hello Tushar,

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

riaz

Well-known Member
Joined
Jun 27, 2006
Messages
779
If I might be so bold to add to Tushar's post (y)

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).
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top