Solved: Function similar to FACT() to SUM numbers, not multiply

[brunces]

Friends,

Hi! How are you all?

Please, I need some function (or whatever) to do the following...

I have a random number in cell A1. In cell B1 I want that number plus its previous integer number, plus its previous, plus its previous, etc... until it hits 1.

Example:

If A1 = 5, B1 = 5 + 4 + 3 + 2 + 1.
If A1 = 10, B1 = 10 + 9 + 8 + 7 + 6 + 5 + 4 + 3 + 2 + 1.
If A1 = 15, B1 = 15 + 14 + 13 + 12 + 11 + 10 + 9 + 8 + 7 + 6 + 5 + 4 + 3 + 2 + 1.
And so on...

The function must work with any number in cell A1, even if it's a huge number (Ex.: 8,691,542). The number typed in A1 will always be integer.

Please, does anybody know if there's a function in Excel which can do such a thing? If not, is it possible to create such function (or whatever), using VBA?

That would be something like the FACT (factorial) function, but using sum, not multiplication.

I hope you can help me.

Thank you all for your attention, guys.

Hugs,

Bruno

[mdmackillop]

Hi Bruno,
Try =((A1+1)/2)*A1
Regards
MD

[Zack Barresse]

Don't know of a function that will do this. I'm sure you can construct one, but I like VBA. 8-)

[vba]Function SUMFACT(celRef As Variant) As Long
Dim i As Long, celVal As Long, tmpFact As Long
On Error GoTo ErrOut
If Abs(celRef) <> celRef Then GoTo ErrOut
For i = 1 To celRef
tmpFact = tmpFact + i
Next i
SUMFACT = tmpFact
Exit Function
ErrOut:
SUMFACT = "#Error!"
End Function[/vba]

[brunces]

mdmackillop,
Very good, man! thank you very much.

--------

firefytr,

I also had created this code below...

[vba]
Sub TotalSum()
Dim varResult As Integer
varResult = Range("A1").Value
If varResult = 0 Then
MsgBox "Type a number in cell A1!"
Exit Sub
ElseIf varResult = 1 Then
Range("B1").Value = 1
Exit Sub
Else:
Range("B1").Value = 0
While varResult > 0
Range("B1").Value = Range("B1").Value + varResult
varResult = varResult - 1
Wend
End If
End Sub
[/vba]

But, I posted this thread because maybe someone would have a smoother conception, like mdmackillop's. Anyway, I appreciate your function code very much, buddy! It's always good to have different sources.

Thank you all, guys.

Hugs,

Bruno

[Zack Barresse]

Very nice MD! Didn't even try the formula route as I couldn't think of a native formula to do this. Plus I was already in my VBE. (I know, poor excuse. LOL!)

Glad you got it working Bruno. Take care.

[mdmackillop]

When all else fails, there is always ..... Arithmetic

[Bob Phillips]

Ah, Gauss' elementary school formula (elementary for him that is).

I had an NG discussion on this with my old mukka Harlan Grove, and I came up with this formula for summing all numbers starting and finishing at any point (i.e. not necessarily starting at 1). I came up with

=SUMPRODUCT(--(ROW(INDIRECT(A1&":"&A2))))

Harlan came up with

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

I don't think it takes an Einstein (or even a Gauss) to see which is the better.

.

[brettdj]

ROFL

The Gauss story is a cute one, it's one of my favourite historical mathamtaic stories. And unlike Fermats Last theorem I can follow the logic

The formula for sumarising such a series is taught in Australian high school maths, I'm assuming this is standard pratice elsewhere?

Cheers

Dave

[brunces]

XLD,

Your formula is very cool, buddy! Congratulations!

Hugs for all.

Bruno

[Bob Phillips]

XLD,

Your formula is very cool, buddy! Congratulations!

Hugs for all.

Bruno

Thanks, but I hope that you mean the second one, and that is Harlan's (the man is genius in my view, cantankerous, but talented).