Calculation Times in Excel XP vs. Excel 2003 and 2007

[Adam]

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.

 

[Bernard Liengme]

This is a well know XL2007 problem that SP1 should address
best wishes

 

[Adam]

This is a well know XL2007 problem that SP1 should address
best wishes

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.

 

[Niek Otten]

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.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| On Oct 11, 2:18 pm, "Bernard Liengme" <[email protected]>
| 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.
|

 

[Adam]

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

 

[Charles Williams]

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

 

[Jim Cone]

Charles,
Please post the results of your evaluation of the workbook.
It would be greatly appreciated.
Sincerely,
--
Jim 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

 

[Charles Williams]

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

 

[Jim Cone]

Thank you for the update and providing the reference.
--
Jim 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

 

[Adam]

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

 

[iandjmsmith]

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









- 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

 

[Charles Williams]

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
alternatives for the LOGINV function that the UDF is currently
employing.

Regards,
Adam









- 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

 

[Charles Williams]

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
alternatives for the LOGINV function that the UDF is currently
employing.

Regards,
Adam









- 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

 

[iandjmsmith]

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 availablewww.DecisionModels.com

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- Hide quoted text -

- 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

 

[Guest]

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]

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

 

[Charles Williams]

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

 

[Pete_UK]

Charles,

is there a date (or venue) fixed for the London conference yet?

Pete

 

[Peo Sjoblom]

http://www.exceluserconference.com/UKEUC.html


--


Regards,


Peo Sjoblom

 

[Pete_UK]

Thanks Peo,

still not fully organised yet, but at least we have dates now.

Pete