Excel - Factorial function...whaaaaaa?

Asked By Geoff

27-Jan-10 01:54 AM

I have the following equation in my code:

TotalCalc = Fact(n + 4) / (Fact(4) * Fact(n))

When I compile, it highlights the first Fact and tells me "Sub or Function
not defined".

TIA

27-Jan-10 01:59 AM

FACT() is a worksheetfunction. So you can try something like the below

TotalCalc = WorksheetFunction.Fact(n + 4) / (WorksheetFunction.Fact(4) * _
WorksheetFunction.Fact(n))

--
Jacob

27-Jan-10 02:36 AM

Or, to lessen the amount of typing...

With WorksheetFunction
TotalCalc = .Fact(n + 4) / (.Fact(4) * .Fact(n))
End With

To the OP... be sure to note the "dot" in front of each of the Fact function
names.

--
Rick (MVP - Excel)

27-Jan-10 03:10 AM

For n>166, Fact(n+4) exceeds the computation limits of the Double data type.

And for n>14, Fact(n+4) exceeds 2^53-1, the largest integer that can be
represented exactly with the Double data type.  So the result of your
expression is infinitesimally different from the correct result, which can
lead to anomalies in some expressions.

It would behoove you to reduce the formula algebraically, namely:

TotalCalc = (n+4)*(n+3)*(n+2)*(n+1) / 24

Not only is the result more accurate for a wider range of n, but also it is
more efficient.

Even if 4 in Fact(n+4) and Fact(4) is actually variable, I think an
algorithm that reduces the formula algebraically would be more reliable,
albeit perhaps less efficient.


----- original message -----

27-Jan-10 12:58 PM

Another option might be:

TotalCalc = WorksheetFunction.Combin(n+4,4)

= = =
HTH  :>)
Dana DeLouis

27-Jan-10 04:07 PM

As others have indicated, you can use Excel's built in FACT worksheet
function. However, you can roll your own with a recursive function in
VBA.   Recursive functions are those function that call themselves. In
the code below, the Fact function calls itself decrementing the input
value until it is 1.

See http://www.cpearson.com/Excel/RecursiveProgramming.aspx for an
introduction to recursion.

Function Fact(N As Long) As Long
If N = 1 Then
Fact = 1
Else
Fact = N * Fact(N - 1)
End If
End Function

Usage:

Sub AA()
Dim L As Long
L = Fact(6)
Debug.Print CStr(L) ' displays 120
End Sub

Then your original code will work with no modification.



Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]

27-Jan-10 06:06 PM

Okay, as long as you posted a recursive Factorial function in VB, I figured
others might find this non-recursive function of some interest. The
following function will calculate factorials up to 29 accurate digits of
display before reverting to exponential display (but note the caution after
the function code)...

Function BigFactorial(N As Long) As Variant
Dim X As Long
If N < 28 Then
BigFactorial = CDec(1)
Else
BigFactorial = CDbl(1)
End If
For X = 1 To N
BigFactorial = X * BigFactorial
Next
End Function

However, you have to watch out for overflows with Decimal data types (that
is what the CDec function produces) when used in calculations... once an
expression using a Decimal data type calculates to more than 28/29 digits
(depending of if it contains a decimal point or not), it will produce an
overflow error. So, if you tried to use the above function like this

MsgBox 10 * BigFactorial(27)

you would get an overflow error but

MsgBox 10 * BigFactorial(28)

would work fine (the difference being in the first case BigFactorial returns
a Variant with a Decimal subtype whereas in the second case the Variant's
subtype is a Double).

--
Rick (MVP - Excel)