J
Jitka
Hi,
need to calculate fact(365) but getting #Num!. Any advice?
Thanks
need to calculate fact(365) but getting #Num!. Any advice?
Thanks
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Gary''s Student
The FACT() function does not work that high.
D
David Biddulph
From Excel help "Specifications and limits":
"Largest allowed positive number 1.79769313486231E+308
"
"Largest allowed positive number 1.79769313486231E+308
"
D
Dana DeLouis
Jitka said:Hi,
need to calculate fact(365) but getting #Num!. Any advice?
Thanks
Hi. As a side note, a math program indicates it is 779 digits long.
Here's an approximate value:
2.510412867555873*10^778
As others have mentioned, it's too large for excel.
(If it helps, the Natural Log of 365! is...)
Log(365.!) = 1792.3316495780505
- - -
Dana DeLouis
D
David Biddulph
You can use Excel to get an rough (15 sig fig) approximation to FACT(365) by
trimming down by a couple of orders of magnitude in each row. If B1:B365
contains =ROW(), you can use an array formula =PRODUCT(B1:B365/100) to get
2.51041E+48 or as number with no decimal places to see that the significant
figures are 251041286755588, and then the number would need to be multiplied
by 10^(2*365), so 1E+730 would change the exponent term from E+48 to E+778
(as Dana gave below).
trimming down by a couple of orders of magnitude in each row. If B1:B365
contains =ROW(), you can use an array formula =PRODUCT(B1:B365/100) to get
2.51041E+48 or as number with no decimal places to see that the significant
figures are 251041286755588, and then the number would need to be multiplied
by 10^(2*365), so 1E+730 would change the exponent term from E+48 to E+778
(as Dana gave below).
H
Harlan Grove
Jitka said:need to calculate fact(365) but getting #Num!. Any advice?
Do you really NEED to calculate 365!, or are you working with numbers
of the form n!/[(n-k)!k!] ? If the latter, and if you refuse to use
any of the standard algebraic reductions common in either probability
or combinatorics, you could probably get by using GAMMALN since ln(n!)
= GAMMALN(n+1), so n!/[(n-k)!k!] = ROUND(EXP(GAMMALN(n+1)/GAMMALN(n-k
+1)/GAMMALN(k+1)),0). The ROUND call is needed because Excel's GAMMALN
is only accurate to 10 decimal places and is really poor around 1 and
2 where it should return 0 exactly.
S
Shane Devenshire
Hi,
Why don't you show us the entire formula you are writing. I doubt you are
just calculating the factorial of 365 (the number of days in the year?)
For example if you are trying to calculate
Pk,n = n!/(n-k)! Then use Excel's PERMUT function
and if you are trying to calcualte
Ck,n = n!/(k!*(n-K)!)
use Excel's COMBIN function.
If this helps, please click the Yes button
Cheers,
Shane Devenshire
Why don't you show us the entire formula you are writing. I doubt you are
just calculating the factorial of 365 (the number of days in the year?)
For example if you are trying to calculate
Pk,n = n!/(n-k)! Then use Excel's PERMUT function
and if you are trying to calcualte
Ck,n = n!/(k!*(n-K)!)
use Excel's COMBIN function.
If this helps, please click the Yes button
Cheers,
Shane Devenshire
H
Harlan Grove
....Shane Devenshire said:For example if you are trying to calculate
Pk,n = n!/(n-k)! Then use Excel's PERMUT function
and if you are trying to calcualte
Ck,n = n!/(k!*(n-K)!)
use Excel's COMBIN function.
ALWAYS best to test!
Why don't you report back what Excel returns for PERMUT(365,182) and
COMBIN(365,182)?
D
David Biddulph
=COMBIN(365,182) gives me 3.1322E+108
What did your test give, Harlan?
--
David Biddulph
What did your test give, Harlan?
--
David Biddulph
....Harlan Grove said:ALWAYS best to test!
Why don't you report back what Excel returns for PERMUT(365,182) and
COMBIN(365,182)?
H
Harlan Grove
David Biddulph said:=COMBIN(365,182) gives me 3.1322E+108
What did your test give, Harlan?
Same. What did PERMUT(365,182) give you?
D
Dana DeLouis
Hi David. That's a great technique. Thanks.
Perhaps we could eliminate the B1:B365 cells with this Array formula...
=PRODUCT(ROW(INDIRECT("A1:A365"))/100)
Another variation of that same equation is:
=EXP(GAMMALN(366)-365*LN(100))
but it's only good for a few digits, as GammaLn is not very accurate.
Thanks. :>)
Dana DeLouis
If B1:B365 contains =ROW()...
Perhaps we could eliminate the B1:B365 cells with this Array formula...
=PRODUCT(ROW(INDIRECT("A1:A365"))/100)
Another variation of that same equation is:
=EXP(GAMMALN(366)-365*LN(100))
but it's only good for a few digits, as GammaLn is not very accurate.
Thanks. :>)
Dana DeLouis