Factorial function...whaaaaaa?

  • Thread starter Thread starter Geoff
  • Start date Start date
G

Geoff

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
 
FACT() is a worksheetfunction. So you can try something like the below

TotalCalc = WorksheetFunction.Fact(n + 4) / (WorksheetFunction.Fact(4) * _
WorksheetFunction.Fact(n))
 
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.
 
Geoff said:
I have the following equation in my code:
TotalCalc = Fact(n + 4) / (Fact(4) * Fact(n))
Click to expand...

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 -----
 
Another option might be:

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

= = =
HTH :>)
Dana DeLouis
 
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]
 
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)



Chip Pearson said:
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]




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
Click to expand...
Click to expand...
 
Back
Top