Formula confusion

[harry buggy]

im writing a VBA programme where x = 12 in cell A3 and cell B3 = 234

and in cell C3

=IF<>, IF(B3<=0," Integer in B3 must be positive",(A3^B3)/B3)

this gives the value 1.4428E+250

when i change cell B3 to a negative value it shows " integer in b3
must be positive

is this right??? where i want cell C3 to show the value of x raised to
the power n divided by n factorial?

and want to show that a negative value of n canot be used, where n is
cell b3

also is there anyway of checking for errors in this??

thanks

 

[Guest]

=FACT(B3) will give the Factorial


try:

=IF(B3<>"",IF(B3<=0," Integer in B3 must be positive",(A3^B3)/FACT(B3)),"")

 

[Guest]

Your formula isn't doing what you say you want: maybe it should read
(A3^B3)/B3! or in spreadsheet lingo =(A3^B3)/FACT(B3)

Another problem with this is that FACT(234) exceeds Excel's capacity. The
max is FACT(170).

In code you need to write for formula like this:

Function myFormula()
IF B3<>"" then
myFormula=""
ElseIF B3<=0 then
myFormula="Interger in B3 must be positive"
Else
MyFormula = (Range(A3)^Range(B3))/WorksheetFunction.Fact(Range(B3))
End IF
End Function

Another point you might want to consider FACT(0) and A3^0 are both legal.
In addition Excel will except non integers for FACT or ^.

 

[Harlan Grove]

Another problem with this is that FACT(234) exceeds Excel's
capacity. The max is FACT(170).

....

But 12^234/234! is within Excel's numerical capability. You just need
to use some basic numerical cleverness.

=EXP(LN(12)*234-GAMMALN(234+1))

returns 1.48921424053937E-202, which agrees somewhat with bc (scale =
217),

..0000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000\
00000000000000000000000000000000000000000000000000000000000000000\
00000001489214240465480301

which in turn agrees somewhat more closely with R,

1.48921424046555e-202

Basically, Excel sucks at this sort of thing. There may be
alternatives that work better in Excel, but the OP would need to
mention what he really wants to do.

But back to the OP's question,

=IF(n<0,"n must be nonnegative",EXP(LN(x)*n-GAMMALN(n)))

 

[Harlan Grove]

=IF(n<0,"n must be nonnegative",EXP(LN(x)*n-GAMMALN(n)))

I would screw that up. It should be

=IF(n<0,"n must be nonnegative",EXP(LN(x)*n-GAMMALN(n+1)))