Factorial

[ctrnz]

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

 

[Jonmo1]

Welcome to the board...

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

 

[Richard Schollar]

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]

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]

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

Here is a UDF possibility:

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]

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]

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

 

[tusharm]

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]

Hello Tushar,

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

 

[riaz]

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