need help on a function

[mark gardner]

I need a function or formula that will read a number in a cell and then give me the sum of all numers between 1 and that number. Can anyone help me?

 

[Andrew Poulsom]

If A1 contains your number try:

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

 

[Oaktree]

Welcome to the board.

If A1 stores your number, try:

=SUM(ROW(INDIRECT("1:"&A1))), which is an array formula and must be confirmed with CTRL+SHIFT+ENTER (doing so will result in Excel putting { }'s around your formula in the formula bar).

Of course, this limits your input to a maximum of 65536. If that doesn't suit your needs, you'll likely need to use VBA.

 

[Richard Schollar]

Hi

A possible VBA solution utilising a UDF could be:

Function SumOneToN(LNumber As Long) As Double
Dim n As Integer, temp As Double
n = 1
If LNumber < 0 Then
    n = -1
    LNumber = Abs(LNumber)
End If
If LNumber <= 1 Then SumOneToN = LNumber * n: Exit Function
Do While LNumber > 0
    temp = temp + LNumber
    LNumber = LNumber - 1
Loop
SumOneToN = temp * n
End Function

Which will also work with negative numbers if that interests you.

Best regards

Richard