PDA

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



brunces
10-11-2005, 09:22 AM
Friends,

Hi! How are you all? http://vbaexpress.com/forum/images/smilies/001.gif

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. http://vbaexpress.com/forum/images/smilies/001.gif

Thank you all for your attention, guys. http://vbaexpress.com/forum/images/smilies/001.gif

Hugs,

Bruno

mdmackillop
10-11-2005, 09:30 AM
Hi Bruno,
Try =((A1+1)/2)*A1
Regards
MD

Zack Barresse
10-11-2005, 09:51 AM
Don't know of a function that will do this. I'm sure you can construct one, but I like VBA. 8-)

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

brunces
10-11-2005, 10:11 AM
mdmackillop,
Very good, man! thank you very much. :)

--------

firefytr,

I also had created this code below...


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


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
10-11-2005, 11:54 AM
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. :D (I know, poor excuse. LOL!)

Glad you got it working Bruno. Take care.

mdmackillop
10-11-2005, 04:20 PM
When all else fails, there is always ..... Arithmetic:thumb

Bob Phillips
10-11-2005, 05:11 PM
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
10-11-2005, 08:14 PM
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
10-12-2005, 02:56 PM
XLD,

Your formula is very cool, buddy! Congratulations! :)

Hugs for all.

Bruno

Bob Phillips
10-12-2005, 03:39 PM
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).