 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2007Calculation Times in Excel XP vs. Excel 2003 and 2007
This is a strange one. I have an Excel workbook that was created in Excel XP. It has 4 total sheets -- 2 with data that's calculated, and 2 that are just for data lookups. Most of the functions employed are standard... included in Excel's library. It also uses 2 custom functions written in VBA running some higher level mathematical algorithms. System Specs: Pentium 4, 3.4 GHz (single-core, non-HT), 1.5 GB DDR RAM, 80GB PATA. Calculation Time: Excel XP -- 1 minute, 45 seconds Excel 2003 -- 8 minutes I have another computer that has Excel 2007 installed which, unlike previous versions, is multi-threaded. System Specs: Core2 Duo E4400, 2.0GHz, 4GB DDR2 RAM, 80GB SATA. Calculation Time: Excel 2007 -- 3 minutes, 20 seconds I cannot for the life of me figure out why Excel XP could be 5 times faster than Excel 2003 running on the same system, and twice as fast as Excel 2007 running on a much more powerful system. Any Excel gurus out there that have any ideas? I have already tried resaving the file in 2007's new XLSM format, but it's just as slow. Turning screen refreshing off and making calculations manually as part of the macro do not speed up the calculations either. This is a well know XL2007 problem that SP1 should address best wishes  SignatureBernard Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme -- Bernard Liengme http://people.stfx.ca/bliengme > This is a strange one. I have an Excel workbook that was created in > Excel XP. It has 4 total sheets -- 2 with data that's calculated, and [quoted text clipped - 28 lines] > calculations manually as part of the macro do not speed up the > calculations either. On Oct 11, 2:18 pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca> wrote: > This is a well know XL2007 problem that SP1 should address > best wishes > -- > Bernard Liengme > Microsoft Excel MVPhttp://people.stfx.ca/bliengme Thank you kindly for the reply. We are not really ready to move to Office 2007 in our environment, I just put the stats for that as a comparison (It's much faster than Excel 2003). The big problem is in the difference between Excel 2002 (XP) and Excel 2003. I cannot figure out what's causing the huge increase in calculation times going up one rather minor version. As for the difference between 2003 and XP; did you save and re-open? A first calculation in a new version may have to rearrange quite a bit and therefore be slower initially. About 2007: there is a setting in Excel Options which controls the use of multiple processors. UDF's in VBA can (presently) never be multithreaded so sometimes it is better to switch off the use of multiple processors. And even then in my experience Excel 2007 is often slower than previous versions. SignatureKind regards, Niek Otten Microsoft MVP - Excel | On Oct 11, 2:18 pm, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca> | wrote: [quoted text clipped - 10 lines] | figure out what's causing the huge increase in calculation times going | up one rather minor version. Yes, I did a "Save As" after calculating in Excel 2003, and that did not help speed up the calculations at all. I also tried converting to XLSM in Excel 2007, then back to XLS. That didn't help much, either. The only difference after that was the actual calculation percentages displayed in the status bar. Before I resaved the file, it would go from 0% to 5% to 6% to 20% to 21%, etc. After re-saving the file, the percentage basically jumped right to 54%, then would go to 55%, 56%, 61%, 80%. While this "looks" better (because it seems to be working faster), it actually takes the same amount of time to calculate that it did before the re-save. Maybe the whole workbook needs to be recreated from scratch. I could see if this was a problem going from Excel XP & 2003 to 2007, as there is a huge difference in the programs, but XP & 2003 are very similar. Seems strange that XP's calculation routines are absolutely killing 2003's. Any design problems in the formulas that would cause extra calculations or iterations should affect both programs... Thanks, Adam > As for the difference between 2003 and XP; did you save and re-open? A first calculation in a new version may have to rearrange > quite a bit and therefore be slower initially. [quoted text clipped - 7 lines] > Niek Otten > Microsoft MVP - Excel If you can send me a zipped copy of the workbook I would be interested in finding out why 2003 is so slow. regards Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com > Yes, I did a "Save As" after calculating in Excel 2003, and that did > not help speed up the calculations at all. I also tried converting to [quoted text clipped - 31 lines] >> Niek Otten >> Microsoft MVP - Excel Charles, Please post the results of your evaluation of the workbook. It would be greatly appreciated. Sincerely, SignatureJim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Charles Williams" wrote in message If you can send me a zipped copy of the workbook I would be interested in finding out why 2003 is so slow. regards Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com "Adam" wrote in message > Yes, I did a "Save As" after calculating in Excel 2003, and that did > not help speed up the calculations at all. I also tried converting to [quoted text clipped - 15 lines] > Thanks, > Adam Adam was kind enough to send me a copy of his workbook: I analysed it with FastExcel, it was straightforward to find the problem. virtually all the calculation time is in column V , in the Layer_EV VBA function. within that the calculation time is going in the Excel LOGINV function. The reason this is slower in Excel 2003 than in Excel 2002 is that MSoft rewrote a number of these stats functions to give greater accuracy. If you have this problem you need to find an alternative to LOGINV that gives acceptable accuracy for your workbook and runs faster. I am not a statistician but here is a webpage with VBA stats functions that has been recommended http://members.aol.com/iandjmsmith/iansNApage.htm regards Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com > Charles, > Please post the results of your evaluation of the workbook. [quoted text clipped - 19 lines] >> Thanks, >> Adam Thank you for the update and providing the reference. SignatureJim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Charles Williams" wrote in message Adam was kind enough to send me a copy of his workbook: I analysed it with FastExcel, it was straightforward to find the problem. virtually all the calculation time is in column V , in the Layer_EV VBA function. within that the calculation time is going in the Excel LOGINV function. The reason this is slower in Excel 2003 than in Excel 2002 is that MSoft rewrote a number of these stats functions to give greater accuracy. If you have this problem you need to find an alternative to LOGINV that gives acceptable accuracy for your workbook and runs faster. I am not a statistician but here is a webpage with VBA stats functions that has been recommended http://members.aol.com/iandjmsmith/iansNApage.htm regards Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com Thank you very much for taking the time to analyze the workbook for me -- your explanation is extremely helpful. I will look into alternatives for the LOGINV function that the UDF is currently employing. Regards, Adam On Oct 13, 6:55 am, "Charles Williams" <Char...@DecisionModels.com> wrote: > Adam was kind enough to send me a copy of his workbook: > [quoted text clipped - 19 lines] > FastExcel 2.3 now available > Name Manager 4.0 now availablewww.DecisionModels.com > Thank you very much for taking the time to analyze the workbook for me > -- your explanation is extremely helpful. I will look into [quoted text clipped - 32 lines] > > - Show quoted text - If you add these functions to the code in <http:/members.aol.com/ iandjmsmith/Examlpes.txt> then inv_lognormal will work with the same parameters as LOGINV. Only inv_normal is required but the others are added for completeness. Ian Smith Public Function pdf_lognormal(ByVal x As Double, ByVal mean As Double, ByVal sd As Double) As Double If (sd <= 0#) Then pdf_lognormal = [#VALUE!] Else pdf_lognormal = Exp(-0.5 * ((Log(x) - mean) / sd) ^ 2) / x / sd / Sqr(twoPi) End If End Function Public Function cdf_lognormal(ByVal x As Double, ByVal mean As Double, ByVal sd As Double) As Double If (sd <= 0#) Then cdf_lognormal = [#VALUE!] Else cdf_lognormal = cnormal((Log(x) - mean) / sd) End If End Function Public Function comp_cdf_lognormal(ByVal x As Double, ByVal mean As Double, ByVal sd As Double) As Double If (sd <= 0#) Then comp_cdf_lognormal = [#VALUE!] Else comp_cdf_lognormal = cnormal(-(Log(x) - mean) / sd) End If End Function Public Function inv_lognormal(ByVal prob As Double, ByVal mean As Double, ByVal sd As Double) As Double If (prob <= 0# Or prob >= 1# Or sd <= 0#) Then inv_lognormal = [#VALUE!] Else inv_lognormal = Exp(mean + sd * invcnormal(prob)) End If End Function Public Function comp_inv_lognormal(ByVal prob As Double, ByVal mean As Double, ByVal sd As Double) As Double If (prob <= 0# Or prob >= 1# Or sd <= 0#) Then comp_inv_lognormal = [#VALUE!] Else comp_inv_lognormal = Exp(mean - sd * invcnormal(prob)) End If Ian, Thanks for the code: I get variable TwoPi not defined when I compile it ... regards Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com >> Thank you very much for taking the time to analyze the workbook for me >> -- your explanation is extremely helpful. I will look into [quoted text clipped - 89 lines] > comp_inv_lognormal = Exp(mean - sd * invcnormal(prob)) > End If On 14 Oct, 09:46, "Charles Williams" <Char...@DecisionModels.com> wrote: > Ian, > [quoted text clipped - 108 lines] > > - Show quoted text - Apologies Charles, I dashed the note off before disappearing for a couple of days. I got the code from an old version I had modified for someone else. The code for pdf_lognormal should be Public Function pdf_lognormal(ByVal x As Double, ByVal mean As Double, ByVal sd As Double) As Double If (sd <= 0#) Then pdf_lognormal = [#VALUE!] Else pdf_lognormal = Exp(-0.5 * ((Log(x) - mean) / sd) ^ 2) / x / sd * OneOverSqrTwoPi End If End Function Ian Smith I added Ian's code to the problem workbook. It is much faster and gives the same results as LOGINV Calculation time reduces to 17 seconds from 257 seconds when calculation is initiated from VBA using Application.Calculate, but takes 55 seconds if you use F9. This is caused by a long-standing bug which refreshes the titlebar in the VBE IDE for each execution of a UDF (the problem has over 9000 calls to the UDF). You can bypass the bug by adding this code to the ThisWorkbook module Private Sub Workbook_Open() Application.OnKey "{F9}", "Recalc" End Sub and adding this code to a standard moduleSub Recalc() Application.Calculate End Sub This will make F9 call Application.CalculateCharles______________________Decision ModelsFastExcel 2.3 now availableName Manager 4.0 now availablewww.DecisionModels.com >> Thank you very much for taking the time to analyze the workbook for me >> -- your explanation is extremely helpful. I will look into [quoted text clipped - 89 lines] > comp_inv_lognormal = Exp(mean - sd * invcnormal(prob)) > End If Thank you so much for your help, Charles. It's amazing how fast it runs through the calculations now. Ian -- fantastic work on compiling all of those Excel functions. You should convert them into an Excel add-on and market it to equity firms. Jerry -- good explanation for the methods Excel is using and why they're inferior to the methods employed by Ian's functions. Sometimes you really have to wonder what Microsoft's application developers are thinking. Regards, Adam I did some more optimization work on the UDF in Adam's workbook, in preparation for my talk on writing fast UDFs at the London Excel Users conference. The calculation time improved from 17 seconds to 1.7: its amazing how fast VBA UDFs can be when they are optimized. Charles ______________________ Decision Models FastExcel 2.3 now available Name Manager 4.0 now available www.DecisionModels.com > Thank you so much for your help, Charles. It's amazing how fast it > runs through the calculations now. [quoted text clipped - 10 lines] > Regards, > Adam Charles, is there a date (or venue) fixed for the London conference yet? Pete On Oct 15, 7:00 pm, "Charles Williams" <Char...@DecisionModels.com> wrote: > I did some more optimization work on the UDF in Adam's workbook, in > preparation for my talk on writing fast UDFs at the London Excel Users [quoted text clipped - 24 lines] > > - Show quoted text - http://www.exceluserconference.com/UKEUC.html SignatureRegards, Peo Sjoblom > Charles, > [quoted text clipped - 33 lines] >> >> - Show quoted text - Thanks Peo, still not fully organised yet, but at least we have dates now. Pete > http://www.exceluserconference.com/UKEUC.html > [quoted text clipped - 47 lines] > > - Show quoted text - Additional information: Excel's ...INV functions numerically invert Excel's ...DIST functions. In 2002, MS tightened the convergence criteria for NORMINV/NORMSINV, and in 2003 MS migrated that tightened convergence criteria to other ...INV functions, which is the basis for their improvement. Most are still inadequate, because MS didn't improve the ...DIST functions which limit their accuracy. The one exception is that the accuracy of NORMDIST/NORMSDIST was greatly improved in 2003. I don't have immediate access to XP, but I suspect that LOGINV used the improved NORMINV in XP (compare numerical results in the tails with 2000), so that the real culprit here is the longer time to evaluate NORMDIST in each iteration of NORMINV. Ian Smith (author of your recommended library, and another respondent in this thread) sidestepped the issue altogether by using well accepted (cf. PPND16 in http://lib.stat.cmu.edu/apstat/241 ) rational polynomial approximations to NORMSINV that have been around for at least 2 decades and give essentially machine accuracy without any iteration. The result is something that is apparently faster than Excel's compiled code, despite running in VBA. I have no clue as to why MS chose to numerically invert for NORMINV/NORMSINV/LOGINV when this better alternative was available. Jerry > Adam was kind enough to send me a copy of his workbook: > [quoted text clipped - 16 lines] > regards > Charles
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.