The problem is zero should not be a possibility and it is
occurring because I have not properly converted this part
of the math formula to code.
Are you trying to implement this formula which you posted in your first
message?
F!
------------
n!(F-n)!
where F! is shorthand for (F)x(F-1)x(F-2) ...x3x2x1.
If so, you can add the BigFactorial function I show below to your project
and then just write the formula directly. That is, the above formula would
become this in code...
BigFactorial(F) / (BigFactorial(n) * BigFactorial(F - n))
Note that there is some important discussions included with the BigFactorial
function, so you should read it carefully. While I don't say so in the text
below, you should be aware that your calculations will become meaningless if
the factorial is returned in exponential format due to the loss of accuracy
inherent in having to shove large numbers into that format.
Rick
Below is a combination of two posts I given in the past to someone who asked
about implementing a Factorial function...
You could cast (you can't Dim) a Variant variable as a Decimal type (96-bit
number) and get some 28 or 29 digits of accuracy depending if there is a
decimal in the answer or not. Simply Dim a variable as Variant and CDec a
number (any number will do) into it to make it the Decimal type. Thereafter,
that variable will track 28/29 digits of accuracy. For example the following
function will calculate factorials up to 29 digits of display before
reverting to exponential display.
Function BigFactorial(ByVal N As Integer) As Variant
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 -- once
over 28/29 characters, they will produce an overflow error. So, if you tried
to use the above function like this
Debug.Print 10*BigFactorial(27)
you would get an overflow error but
Debug.Print 10*BigFactorial(28)
would work fine (the difference being in the first case BigFactorial has a
Decimal subtype and in the second case the subtype is a Double).
More generally, if a Variant variable is assigned a value that was cast to
Decimal, any calculation involving that variable will be "performed" as a
Decimal; and then the result cast back to the variable receiving it. If the
result is assigned back to the variable that was originally cast to Decimal,
that variable continues to contain a Decimal type value. For example,
X = CDec(135.6)
X = X - 135
X = X / 7
Print X == 0.0857142857142857142857142857
You have to be careful with this though . . . all VB functions return
non-Decimal data.and assigning *that* back to the Variant that was cast as
Decimal "collapses" it back to a less robust data type. For example,
continuing the code above
X = Sqr(X)
Print X == 0.29277002188456
