Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frustrated with Factorials
"Jerry W. Lewis" wrote in message ...
Huh? 364! = 6.87784347275582E775 which cannot be represented in double precision. But you can get its base 10 logarithm as =GAMMALN(364+1)/LN(10) Jerry I wrote: The factorial of 364 - AS EVERYBODY KNOWS - is: 68778434727558170665560119859758980014152110328427 61579965094573671535928279708882100278906575412101 19895131900300885716731727975982739174066996381113 80472283834806453514358733364634223094358532372731 30157400036944390560551807672851497408504437122220 63339347215710551931977898157831445366638128499001 07187614208971784984648790424495130430227400674872 32548096023952876678545319718750098925530231415881 33466056154901044661283183534535046028934954645191 46982514199769387253600408194477090596739724944349 28259379407490602564339394828766015776585992771688 09125842563048132741261544896935060354832360324970 23805268456191795728613542506025326335769192545591 12463771028421986413805768882733973504000000000000 00000000000000000000000000000000000000000000000000 00000000000000000000000000 exactly. When I constructed my high-precision maths, I used two bytes for the "powers-of-two", and not one. Such a specialised maths will indeed resolve the factorials to quite high values. My own system gave a dynamic range of ten to the power of 9863 down to ten-to-the -9862. That is the behaviour of a two-byte "exponent". A double-precision mantissa has no useful effect. Charles Douglas Wehner |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frustrated with Factorials
I agree with your exact value (verification courtesy of Maple 7). My
comment was in response to a claim (which you omitted) that 364! could be computed to full precision using Excel's Analysis ToolPak -- I think we agree that it cannot. Jerry Charles Douglas Wehner wrote: "Jerry W. Lewis" wrote in message ... Huh? 364! = 6.87784347275582E775 which cannot be represented in double precision. But you can get its base 10 logarithm as =GAMMALN(364+1)/LN(10) Jerry I wrote: The factorial of 364 - AS EVERYBODY KNOWS - is: 68778434727558170665560119859758980014152110328427 61579965094573671535928279708882100278906575412101 19895131900300885716731727975982739174066996381113 80472283834806453514358733364634223094358532372731 30157400036944390560551807672851497408504437122220 63339347215710551931977898157831445366638128499001 07187614208971784984648790424495130430227400674872 32548096023952876678545319718750098925530231415881 33466056154901044661283183534535046028934954645191 46982514199769387253600408194477090596739724944349 28259379407490602564339394828766015776585992771688 09125842563048132741261544896935060354832360324970 23805268456191795728613542506025326335769192545591 12463771028421986413805768882733973504000000000000 00000000000000000000000000000000000000000000000000 00000000000000000000000000 exactly. When I constructed my high-precision maths, I used two bytes for the "powers-of-two", and not one. Such a specialised maths will indeed resolve the factorials to quite high values. My own system gave a dynamic range of ten to the power of 9863 down to ten-to-the -9862. That is the behaviour of a two-byte "exponent". A double-precision mantissa has no useful effect. Charles Douglas Wehner |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frustrated with Factorials
"Jerry W. Lewis" wrote in message ...
I agree with your exact value (verification courtesy of Maple 7). My comment was in response to a claim (which you omitted) that 364! could be computed to full precision using Excel's Analysis ToolPak -- I think we agree that it cannot. Jerry AGREED! The highest-precision laboratory maths that I ever wrote gave 65 decimal places. I do not know of any standard maths that gives 776 figures. I created that long number with a string-manipulation program published earlier in this thread http://groups.google.de/groups?hl=de...le.com&rnum=24 Remove the line with b$ - it was included in error. Charles Douglas Wehner |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frustrated with Factorials
what I am attempting to do is write a procedure for a certain range
of "large" numbers in my example I would multiply 364 by 363 and store the results then multiply the stored results by 362 and store the results then multiply the stored results by 361 etc. Someone who's been following this string asked me how long xlPrecision would take to calculate 364!. I thought some others might be interested, so here's my reply: --------------------------------------------------- On a worksheet, on my creaky old Athlon 800 (no money to upgrade..), it took about 4 seconds to calculating 364! down a column. In Excel VBA it took about 3 seconds: ======================================== Dim xlp As xlPrecision.cls_xlPrecision Dim strRet As String Dim i As Integer Dim iStart As Double, iEnd As Double Set xlp = CreateObject("xlPrecision.cls_xlPrecision") strRet = 1 iStart = Time For i = 1 To 364 strRet = xlp.xlpMULTIPLY(strRet, i) Next i iEnd = Time Debug.Print Format(iEnd - iStart, "ss") Workbooks(1).Worksheets(1).Cells(1).Value = strRet --------------------------------------------------- I checked the answer against Charles Douglas Wehner's answer posted 2003-07-15 10:27:00 PST, and they are the same. With xlPrecision's 32,767 significant digits, the largest factorial it can calculate is 10,958!. I guess I should add a xlpFACTORIAL function to xlPrecision. I've been working on adding xlpROOT and xlpPOWER. Take care, Greg Lovern http://PrecisionCalc.com Get Your Numbers Right ================================================== ===== |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frustrated with Factorials
Hello. I probably did not say that correctly. Excel's ATP functions cannot
calculate at that precision. What I meant to say is that when one writes their own vba Factorial function, you can use the functions in the ATP to help your program if desired. The only reason I mentioned it is that Charles has a very nice and interesting web page on Euler's equation, as well as a discussion on Sin's & Cos's. After reading his excellent web page, I got the impression that his final factorial code was written in the time domain. I just pointed out that his excellent Factorial program using Sin & Cos could be continued if he wanted using Excel's ATP. With xlPrecision's 32,767 significant digits, the largest factorial it can calculate is 10,958!. I might be wrong, but 10,958! has 39,511 digits. Digits[10958!] 39511 If you are limited to 2^15, or 32,768, then I believe the size is 9,274! Digits[9274!] 32768 I know the following is not interesting, and this is an old thread. The frequency domain is not all that much faster when calculating 364! because the ratios of n^2 / (n*Log(2,n) is not that great. (That's log(n), base 2). However, when the numbers are large, like 9,274! with 32,767 digits, I think it is about 200 times faster to do it in the frequency domain than the time domain. This is an old thread so I'll stop. However, you are correct, Excel's ATP can not do it directly. -- Dana DeLouis Using Windows XP & Office XP = = = = = = = = = = = = = = = = = "Greg Lovern" wrote in message om... what I am attempting to do is write a procedure for a certain range of "large" numbers in my example I would multiply 364 by 363 and store the results then multiply the stored results by 362 and store the results then multiply the stored results by 361 etc. Someone who's been following this string asked me how long xlPrecision would take to calculate 364!. I thought some others might be interested, so here's my reply: --------------------------------------------------- On a worksheet, on my creaky old Athlon 800 (no money to upgrade..), it took about 4 seconds to calculating 364! down a column. In Excel VBA it took about 3 seconds: ======================================== Dim xlp As xlPrecision.cls_xlPrecision Dim strRet As String Dim i As Integer Dim iStart As Double, iEnd As Double Set xlp = CreateObject("xlPrecision.cls_xlPrecision") strRet = 1 iStart = Time For i = 1 To 364 strRet = xlp.xlpMULTIPLY(strRet, i) Next i iEnd = Time Debug.Print Format(iEnd - iStart, "ss") Workbooks(1).Worksheets(1).Cells(1).Value = strRet --------------------------------------------------- I checked the answer against Charles Douglas Wehner's answer posted 2003-07-15 10:27:00 PST, and they are the same. With xlPrecision's 32,767 significant digits, the largest factorial it can calculate is 10,958!. I guess I should add a xlpFACTORIAL function to xlPrecision. I've been working on adding xlpROOT and xlpPOWER. Take care, Greg Lovern http://PrecisionCalc.com Get Your Numbers Right ================================================== ===== |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frustrated with Factorials
"Dana DeLouis" wrote in message ...
Hello. I probably did not say that correctly. Excel's ATP functions cannot calculate at that precision. What I meant to say is that when one writes their own vba Factorial function, you can use the functions in the ATP to help your program if desired. The only reason I mentioned it is that Charles has a very nice and interesting web page on Euler's equation, as well as a discussion on Sin's & Cos's. All good modern mathematics grows out of the established Art. However, there is a process of RATIONALISING IN THE MIND - of creating simple MIND IMAGES - before one has enough clarity of understanding with which to attempt the new. Such "tricks" as my "FOUR-BOX ALGORITHM" and a visualisation of the "EULER SPIRAL" make things clear. However, to those who have not seen the page, references to a "spiral in complex space" may seem like mysticism. ..., I got the impression that his final factorial code was written in the time domain. No. What I did at that point was to emphasise that there is a FLAW in complex arithmetic. It often GOES WRONG when "amateurs" apply it in a mechanical fashion without using common sense. Electronic engineers - working with REPETITIVE CYCLIC functions in the TIME DOMAIN can usually get away with using complex maths. This is because sinusoidal oscillators produce waves where one is very much like the other. Having shown that the user of complex maths is restricted to cyclic functions, I returned to studies of the Gamma function (or what I call fRactorials - FRACTIONAL factorials). I was now no longer dealing with the time domain. I just pointed out that his excellent Factorial program using Sin & Cos could be continued if he wanted using Excel's ATP. The RECIPROCAL of the Gamma (my Eucalculus curve) seems to spiral in complex space. One would apply Sin & Cos IF IT DID. However, I found out that the spiral is LEFT-HANDED whilst also being RIGHT-HANDED. Such an AMBIDEXTROUS spiral is NONSENSE. There is no way of turning left whilst symmetrically turning right. Therefore there is no Sin or Cos in the Gamma function. But more than this - Nature has shown that the Gamma function is INCOMPATIBLE with complex maths. ONE OR OTHER of these is an ARTEFACT OF MAN. Perhaps BOTH. With xlPrecision's 32,767 significant digits, the largest factorial it can calculate is 10,958!. I might be wrong, but 10,958! has 39,511 digits. Digits[10958!] 39511 If you are limited to 2^15, or 32,768, then I believe the size is 9,274! Digits[9274!] 32768 I had built my own laboratory-standard maths, but no longer have it. I had to flee from Britain at the time the Iraq war was brewing. Without notes, I can only remember that my 2-byte exponent gave 10 to the 9863 down to 10 to the -9862. Divide by 256 for 1-byte: 10 to the 38.5 So one-byte-exponent maths is very poor on factorials. Two bytes does indeed go to about 1500! - but I do not know the EXACT limit. Certainly it is better than 34! which gives 2.9523279 times ten to the 38. Four bytes, in my estimation is OVERKILL. Even three bytes would be a waste, because very few people want to go above 1500! Those who do would have to buy a special math system. The coefficients of my f(r)actorial program are of the form A + B(X) +C(X-squared)... where A, B, C &c are EMPIRICAL. Due to the "transfinite" convergence, there seems to be no rational connexion between A, B, C &c. I was using my own huge maths - when I had it - in roughly the following way: Consider the Arc tangent giving coefficients: 1.000000000001 -0.333333333354 0.200000000176 -0.142857141234 0.111111196341 &c. These could be CORRECT, or they could be the equivalent of 1/1 -1/3 1/5 -1/7 1/9 &c. in which case, the .............1 ............54 ...........176 ..........1234 .........96341 &c. is what I call the "Chebyshev WEDGE", because the error in the highest-order coefficient tapers down almost to nothing at the zero-order coefficient. My use of HUGE MATHS was therefore to try to spot rational (Pythagorean) relationships in the coefficients - or in a GENERATOR FUNCTION for those coefficients. Or in the Generator-generator. The bigger the maths, and the more coefficients used, the more likely it is that a sequence 0.3333333 really is one THIRD, and not some coincidence. For example, Euler's little Gamma is 0.577215664 Pi/2e however, is 0.577863674 Three-figure maths cannot tell them apart. Looking for natural laws, one needs these laboratory standard systems that only the specialist ever uses. It is nice to see this address turning up in this thread: Greg Lovern http://PrecisionCalc.com Get Your Numbers Right He won't put Gill Bates (or "what's-his-face") out of business, but will certainly find a tiny niche market of people who want to do unique things. Charles Douglas Wehner |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frustrated with Factorials
"Dana DeLouis" wrote in message ...
With xlPrecision's 32,767 significant digits, the largest factorial it can calculate is 10,958!. "Greg Lovern" wrote in message om... what I am attempting to do is write a procedure for a certain range of "large" numbers in my example I would multiply 364 by 363 and store the results then multiply the stored results by 362 and store the results then multiply the stored results by 361 etc. Greg Lovern http://PrecisionCalc.com Get Your Numbers Right ================================================== ===== I found a page with the first 1000 factorials ready-computed: http://www.newdream.net/~sage/old/numbers/fact.htm In computers, we often use look-up tables to speed things up. It's a poor substitute for a real large mathematical system, though. Charles Douglas Wehner |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frustrated with Factorials
Charles Douglas Wehner wrote:
"Dana DeLouis" wrote in message ... Hello. I probably did not say that correctly. Excel's ATP functions cannot calculate at that precision. What I meant to say is that when one writes their own vba Factorial function, you can use the functions in the ATP to help your program if desired. The only reason I mentioned it is that Charles has a very nice and interesting web page on Euler's equation, as well as a discussion on Sin's & Cos's. All good modern mathematics grows out of the established Art. However, there is a process of RATIONALISING IN THE MIND - of creating simple MIND IMAGES - before one has enough clarity of understanding with which to attempt the new. Such "tricks" as my "FOUR-BOX ALGORITHM" and a visualisation of the "EULER SPIRAL" make things clear. However, to those who have not seen the page, references to a "spiral in complex space" may seem like mysticism. I fail to see any fundamental difference between the "Euler Spiral" and the "Riemann Surface" which was used to explain the mulitplicity of trig function roots when I took Complex Analysis 30 years ago. Jerry |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frustrated with Factorials
"Jerry W. Lewis" wrote in message ...
Charles Douglas Wehner wrote: Such "tricks" as my "FOUR-BOX ALGORITHM" and a visualisation of the "EULER SPIRAL" make things clear. However, to those who have not seen the page, references to a "spiral in complex space" may seem like mysticism. I fail to see any fundamental difference between the "Euler Spiral" and the "Riemann Surface" which was used to explain the mulitplicity of trig function roots when I took Complex Analysis 30 years ago. Jerry I have investigated as promised. I could not give it too much time, but I think the following is true: In a Riemann Surface, you might have something like "logarithmic graph paper". Consider an exponential drawn on such paper - it comes out STRAIGHT. So the "warp" is a warp of the WRITING SURFACE - to make what is written seem to be more simple. My Euler Spiral is drawn in VERY SIMPLE three-dimensional RECTILINEAR space. The axis into the page is made to represent the imaginary, but the picture could be one of a spring from the suspension system of a car, for example. A car suspension spring will shadow a cosine onto the wall and a sine onto the floor. So it really is MUCH, MUCH LESS elaborate than the Riemann concept. Charles Douglas Wehner. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Frustrated with Factorials
Myrna Larson wrote in message . ..
It's not the adding and subtracting of 48 that slows things down. It's the use of a string to store the number. When you write something like A = ASC(MID$(TheText, i, 1)) - 48 the problem isn't the subtraction of 48. It's the MID$ function. When you use that, Basic creates a new temporary string "behind the scenes", gets it's ASCII value, then deletes the temporary string. Those string operations are what eat up the CPU cycles. In addition, garbage collection could be triggered, too. What I actually wrote is: In particular, the problem with a high- level language is that hidden things are taking place. In the case of the string, data is stored as ASCII - where every digit has 48 added on in order to create the ASCII of the SYMBOL. "HIDDEN THINGS" covers all these small points. One real burden is "interpretation". Compiled languages will produce machine-code that just runs. Interpreted languages do all of the above things - and also compute the SEQUENCE (syntax) again and again. Qbasic seems to be a hybrid. On disk the programs are kept as text. When loaded, however, a "thread" of machine-code addresses is put between the text elements. Thus, Qbasic can be listed by reading the non-thread parts and run by following the thread. It is a violation of set theory, however, to expect a set of instructions to check itself. So some checking is not in the "compile time" section, but in the "run time" section of Qbasic. So when you are programming and "break the thread", Qbasic says that you will have to RUN the program if you want to make this change. Self-checking is made easier by CLOSING THE SET. When the reserved words cannot be extended, a protective shell of syntax-checking can be built around them. All of these points show that a high-level language uses a lot of CPU power. The machine-code factorial generator is actually just 392 bytes. Custom-made assembler programs tend to beat all comers. http://wehner.org/tools/factor.txt Those with time on their hands might like to optimise my code - I suspect many bytes and many clock-cycles are redundant. Maybe you can reduce the size of my program by 391 bytes.....? Charles Douglas Wehner |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
So frustrated! Please help! | Excel Discussion (Misc queries) | |||
Frustrated | New Users to Excel | |||
Frustrated Professor | Excel Worksheet Functions | |||
Frustrated Cook | Excel Worksheet Functions | |||
Stuck and getting frustrated. Can you assist | Excel Discussion (Misc queries) |