Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 6th 08, 05:44 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 72
Default is it posible to have a formula for factorial via vba code?

Hi all,

i'm trying to build a complex formula using factorial but i havent
foud an operator that calculate a simple factorial,any idea?

  #2   Report Post  
Old April 6th 08, 06:03 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2008
Posts: 1
Default is it posible to have a formula for factorial via vba code?

Below my signature is a standard posting I have used over in the compiled VB
newsgroups, but the function will work just as well in Excel's VBA world.
Depending on how "complex" a formula you are trying to build, pay attention
to the cautions in my write up. You might be able to avoid them by
restricting the maximum size permitted before floating point results are
returned (change the 28 in the first statement to something lower).

Rick

You could cast (you can't Dim) a Variant variable as a Decimal type 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 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(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 -- 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).



"thread" wrote in message
...
Hi all,

i'm trying to build a complex formula using factorial but i havent
foud an operator that calculate a simple factorial,any idea?


  #3   Report Post  
Old April 6th 08, 06:11 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
Default is it posible to have a formula for factorial via vba code?

On Apr 6, 8:44*am, thread wrote:
i'm trying to build a complex formula using factorial but i havent
foud an operator that calculate a simple factorial,any idea?


Perhaps the easy way is:

WorksheetFunction.Fact(n)
  #4   Report Post  
Old April 7th 08, 09:08 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 72
Default is it posible to have a formula for factorial via vba code?

thanks it was very helpfull

On 6 אפריל, 19:11, joeu2004 wrote:
On Apr 6, 8:44*am, thread wrote:

i'm trying to build a complex formula using factorial but i havent
foud an operator that calculate a simple factorial,any idea?


Perhaps the easy way is:

WorksheetFunction.Fact(n)


  #5   Report Post  
Old August 29th 14, 11:02 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2014
Posts: 1
Default is it posible to have a formula for factorial via vba code?

In case you are not into Big numbers (numbers that excel does not normally handle)
Use worksheet.function in VBA. Uses fact() in excel in vba.

On Monday, April 7, 2008 1:38:58 PM UTC+5:30, thread wrote:
thanks it was very helpfull

On 6 אפריל, 19:11, joeu2004 wrote:
On Apr 6, 8:44*am, thread wrote:

i'm trying to build a complex formula using factorial but i havent
foud an operator that calculate a simple factorial,any idea?


Perhaps the easy way is:

WorksheetFunction.Fact(n)




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Auto Maximize posible? Mike Excel Discussion (Misc queries) 1 October 16th 07 02:38 PM
is it posible to do payslips for a small business in excel letitia winkelman Excel Worksheet Functions 1 March 19th 07 03:19 AM
Factorial (like =FACT) function? mr tom Excel Worksheet Functions 8 June 13th 06 01:45 AM
Range of Factorial Function Rushi Excel Discussion (Misc queries) 7 September 17th 05 12:28 AM
Creating "factorial" result from three lists... Phil Excel Discussion (Misc queries) 3 February 25th 05 09:15 PM


All times are GMT +1. The time now is 10:55 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017