ࡱ> _}t^  !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]abcdefphijklmno`qrstuvwxyz{|~Root Entry FQB2xqWorkbook7_VBA_PROJECT_CUR"5!^@2x2xVBA3^@2x,2x  Worksheet"&Tools&WindowChart"&Tools&Window(Visual Basic Module"&Tools&Window\pHumberto Barreto Ba= ThisWorkbook=+<n@"1.uTimes New Roman1.uTimes New Roman1.uTimes New Roman1.uTimes New Roman1.uTimes New Roman1. uTimes New Roman1.8uTimes New Roman1.uTimes New Roman1uArial1uArial1. uTimes New Roman1.uTimes New Roman1 uArial1.uTimes New Roman1. uTimes New Roman1.uTimes New Roman1. uTimes New Roman1.uTimes New Roman1. uTimes New Roman1. uTimes New Roman"$"#,##0_);\("$"#,##0\)!"$"#,##0_);[Red]\("$"#,##0\)""$"#,##0.00_);\("$"#,##0.00\)'""$"#,##0.00_);[Red]\("$"#,##0.00\)7*2_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_).))_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)?,:_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)6+1_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)"C"#,##0_);\("C"#,##0\)!"C"#,##0_);[Red]\("C"#,##0\)""C"#,##0.00_);\("C"#,##0.00\)'""C"#,##0.00_);[Red]\("C"#,##0.00\)72_("C"* #,##0_);_("C"* \(#,##0\);_("C"* "-"_);_(@_)?:_("C"* #,##0.00_);_("C"* \(#,##0.00\);_("C"* "-"??_);_(@_)A<_("$"* #,##0.000_);_("$"* \(#,##0.000\);_("$"* "-"??_);_(@_)C>_("$"* #,##0.0000_);_("$"* \(#,##0.0000\);_("$"* "-"??_);_(@_)=8_("$"* #,##0.0_);_("$"* \(#,##0.0\);_("$"* "-"??_);_(@_)94_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"??_);_(@_)E@_("$"* #,##0.00000_);_("$"* \(#,##0.00000\);_("$"* "-"??_);_(@_) 0.000000000 0.00000000 0.0000000 0.000000 0.00000 0.0000 0.000 0.0%0.0 0.0000000000 0.000000000000.0000000000000.00000000000000.000000000000000.0000000000000000.00000000000000000.000000000000000000.0000000000000000000.00000000000000000000.00000000000000000000 0.0E+00 0.000E+00 0E+00 #,##0.0 #,##0.000 #,##0.0000 #,##0.00000 #,##0.000000 #,##0.0000000#,##0.00000000                + ) , *           " q!  a q q*0  *8 q(  a( q" " q q" q q ( q  q   q  q!  q       " q   *8  *8 " q  a    q , q  $@ @  ,@ @ q @ @ q (@ @ q (!@ @ q !@ @  $!@ @ q !!@ @ q(0  q (@ @  q ,!@ @ q (!!@ @ q ( q @  $@ q (@ @  ,!@ @  ,!@ @ q (!@ @  ,!!@ @ q (@ @  ,!@ @  ,!@ @ q (!@ @  ,!!@ @ q (  q (  q ( q (@ q (  @ q (  q (  q ( q (@@  q (  @  a (@  a (  q (@  q ( @ q"8"@@ q"8"@ q"8 " @ q"8"@ q"8 " @ q*8@ @ *0!@ @ q"8@@ q"8  @ q"8@@ q"8  @ q*8!@ @  Normal_AutoCorrNormal_MonteCarlo8 ``i̜̙3f3333f3ffff333ff333f33f33BBB\` VDoc. OneVarMCSim{ TwoVarMCSim MainModule"# Sample NumbersecsAverageSDMaxMin?Only the first 100 repetitions are displayed on this worksheet.Summary Statistics!Results of Monte Carlo SimulationSimulation Stats repetitionsVar1Var2secondsNotes\This add-in permits Monte Carlo simulation of one or two cells from any Excel workbook with !without redrawing random numbers.[CTRL-ALT-F9 at each repetition. This enables uses the non volatile RNG to do computations Added Solver option6 Ycc  =o&*-.  MbP?_*+%;&A Page &PMF Canon iR5570 PCL6<h  odXLetterCanonY Canon iR5570/iR6570 PCL6ddd      d d d d dd@@d  d  d dd d"edd     ddd     d!!d      !"#$ddA     A d     @8o @*/ o @*/ AXXd2\SRGBCO~1.ICM\SRGBCO~1.ICM\SRGBCO~1.ICM CONFIDENTIALCONFIDENTIALHArialDefault Settings@"dXX??U} 7}  =;;;;;;;;; ; ; ; ; ;;;;;;;;;;;;;;;;;;;~ 7@ 7 7 ~ 7@ 7     Dl   ;!;";#;$;%;&;';(;);*;+;,;-;.;/;0;1;2;3;4;5;6;7;8;9;:;;;<; !"#$%&'()*+,-./0123456789:;<>f0 >@7 Sheet1   g:Uvi>x`{  MbP?_*+%;MF Canon iR5570 PCL6<h  odXLetterCanonY Canon iR5570/iR6570 PCL6ddd      d d d d dd@@d  d  d dd d"edd     ddd     d!!d      !"#$ddA     A d     @8o @*/ o @*/ AXXd2\SRGBCO~1.ICM\SRGBCO~1.ICM\SRGBCO~1.ICM CONFIDENTIALCONFIDENTIALHArialDefault Settings@"d??U} #}  } } } } -}  } }  @ }  }  } @} @ }  }  } !* } +1 } 2 Estimated rho in SUsgBBv BH! BJ BJ B;B;B;BJ B; B; B; B; B; B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;  !#$%&*&2A  B f g"""""""("""""""""" ~ !" """#$""%>&"""""""""""""""""""""A~ #?$ (C > !!?~ "#$!?~ %&*&2A~ #@$ (D E a  bc  d  de &!!?~ "@#$!?~ %@&*&2A~ #@$ %8% G H|u? '[V\ &!1?~ "@#$!?~ %@&*&2A~ #@$ = ; 0n? F]^T !!?~ " @#$!?~ %@&*&2A~ #@$. < :>i@ '_UT !@)?~ "@#$1?~ %@&*&2A~ #@$. ? @lSQ? 9`YZ !1?~ ""@#$1?~ % @&*&2A~ #@$/%6%%%% !9?~ ""@#$!?~ % @&*&2A~ # @ $ / (6%%%%  & !!?~ "(@ # $!?~ %@ &*& 2A~ #"@ $ %6%%%%  & ! ?~ " @ # $@)?~ %@ &*& 2A~ #$@ $ %%%%%%  & !@)?~ "@ # $@)?~ %"@ &*& 2A~ #&@ $ %%%%%%  !`?~ "@ # $1?~ %"@ &*& 2A~ #(@ $ * %%%%%%  !1?~ "@ # $1?~ %"@ &*& 2A~ #*@$*%%%%%% !?~ "@#$9?~ %"@&*&2A~ #,@$*%%%%%% !9?~ "@#$9?~ %(@&*&2A~ #.@$%%%%%% !?~ "@#$!?~ %(@&*&2A~ #0@$%%%%%% !!@~ "@#$!?~ % @&*&2A~ #1@$%%%%%% !c@~ "@#$ ?~ % @&*&2A~ #2@$%%%%%% ! @~ "@#$ ?~ %@&*&2A~ #3@$%%%%%% !0@~ "@#$@)?~ %@&*&2A~ #4@$%%%%%% !@)@~ "?#$@)?~ %@&*&2A~ #5@$!Pk@~ "#$`?~ %@&*&2A~ #6@$!`@~ "#$`?~ %@&*&2A~ #7@$!p@~ "#$1?~ %@&*&2A~ #8@$!1@~ "#$1?~ %@&*&2A~ #9@$!s @~ "#$?~ %@&*&2A~ #:@$! @~ "#$?~ %@&*&2A~ #;@$! @~ "#$9?~ %@&*&2A~ #<@$!9 @~ "#$9?~ %@&*&2A~ #=@$!{ @~ "#$?~ %@&*&2A~ #>@$!@~ "#$?~ %@&*&2ADl B;!B;"B;#B;$B;%B;&B;'B;(B;)B;*B;+B;,B;-B;.B;/B;0B;1B;2B;3B;4B;5B;6B;7B;8B;9B;:B;;B;<B;=B;>B;?B;~ #?@ $ !@~ " # $!@~ %@ &*& 2A~ !#@@!$ !!#!$!@~ !%@!&*&!2A~ "#@@"$ "!#"$c@~ "%@"&*&"2A~ ##A@#$ #!##$c@~ #%@#&*&#2A~ $#A@$$ $!#$$ @~ $%@$&*&$2A~ %#B@%$ %!#%$ @~ %%@%&*&%2A~ &#B@&$ &!#&$0@~ &%@&&*&&2A~ '#C@'$ '!#'$0@~ '%?'&*&'2A~ (#C@($ (!#($@)@~ (%?(&*&(2A~ )#D@)$ )!#)$@)@~ )%)&*&)2A~ *#D@*$ *!#*$Pk@~ *%*&*&*2A~ +#E@+$ +!#+$Pk@~ +%+&*&+2A~ ,#E@,$ ,!#,$`@~ ,%,&*&,2A~ -#F@-$ -!#-$`@~ -%-&*&-2A~ .#F@.$ .!#.$p@~ .%.&*&.2A~ /#G@/$ /!#/$p@~ /%/&*&/2A~ 0#G@0$ 0!#0$1@~ 0%0&*&02A~ 1#H@1$ 1!#1$1@~ 1%1&*&12A~ 2#H@2$ 2!#2$s @~ 2%2&*&22A~ 3#I@3$ 3!#3$s @~ 3%3&*&32A~ 4#I@4$ 4!#4$ @~ 4%4&*&42A~ 5#J@5$ 5!#5$ @~ 5%5&*&52A~ 6#J@6$ 6!#6$ @~ 6%6&*&62A~ 7#K@7$ 7!#7$ @~ 7%7&*&72A~ 8#K@8$ 8!#8$9 @~ 8%8&*&82A~ 9#L@9$ 9!#9$9 @~ 9%9&*&92A~ :#L@:$ :!#:${ @~ :%:&*&:2A~ ;#M@;$ ;!#;${ @~ ;%;&*&;2A~ <#M@<$ <!#<$@~ <%<&*&<2A~ =#N@=$ =!#=$@~ =%=&*&=2A~ >#N@>$ >!#>$@~ >%>&*&>2A~ ?#O@?$ ?!#?$@~ ?%?&*&?2ADZl@B;AB;BB;CB;DB;EB;FB;GB;HB;IB;JB;KB;LB;MB;NB;OB;PB;QB;RB;SB;TB;UB;VB;WB;XB;YB;ZB;[B;\B;]B;^B;_B;~ @#O@@$@!*&@2A~ A#P@A$A!*&A2A~ B#@P@B$B!*&B2A~ C#P@C$C!*&C2A~ D#P@D$D!*&D2A~ E#Q@E$E!*&E2A~ F#@Q@F$F!*&F2A~ G#Q@G$G!*&G2A~ H#Q@H$H!*&H2A~ I#R@I$I!*&I2A~ J#@R@J$J!*&J2A~ K#R@K$K!*&K2A~ L#R@L$L!*&L2A~ M#S@M$M!*&M2A~ N#@S@N$N!*&N2A~ O#S@O$O!*&O2A~ P#S@P$P!*&P2A~ Q#T@Q$Q!*&Q2A~ R#@T@R$R!*&R2A~ S#T@S$S!*&S2A~ T#T@T$T!*&T2A~ U#U@U$U!*&U2A~ V#@U@V$V!*&V2A~ W#U@W$W!*&W2A~ X#U@X$X!*&X2A~ Y#V@Y$Y!*&Y2A~ Z#@V@Z$Z!*&Z2A~ [#V@[$[!*&[2A~ \#V@\$\!*&\2A~ ]#W@]$]!*&]2A~ ^#@W@^$^!*&^2A~ _#W@_$_!*&_2ADl````````````````````````````````B;aB;bB;cB;dB;eB;fB;~ `#W@`$`!*&`2A~ a#X@a$a!*&a2A~ b#@X@b$b!*&b2A~ c#X@c$c!*&c2A~ d#X@d$d!*&d2A~ e#Y@e$e!*&e2A f,x``````P>@   7 Sheet2   gaò  MbP?_*+%;MF Canon iR5570 PCL6<h  odXLetterCanonY Canon iR5570/iR6570 PCL6ddd      d d d d dd@@d  d  d dd d"edd     ddd     d!!d      !"#$ddA     A d     @8o @*/ o @*/ AXXd2\SRGBCO~1.ICM\SRGBCO~1.ICM\SRGBCO~1.ICM CONFIDENTIALCONFIDENTIALHArialDefault Settings@"d??U} #} 0} 1} } }  } }  }  }  } @} @ } 5 } 6 Column LgBBv B! BJ BJ B;B;B;BJ B; B; B; B; B; B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B; !'6A  2 3 B f l"""""""j"""""""""""""""A~ #? 45 (C > !'6A~ #@ 45 (D A h  i  j  k  d e&!'6A~ #@ 45 %8% I JLo?  N Or?}k?  VW &!'6A~ #@ 45 I K ??  N Ph ?  SX !'6A~ #@ 45 I J*?  N OV?  UT !'6A~ #@ 45 L M{n  Q R ݿ  YZ !'6A~ #@ 45%6%%%% !'6A~ # @ 45  (6%%%%  & !' 6A~ #"@ 45 %6%%%%  & !' 6A~ #$@ 45 %%%%%%  & !' 6A~ #&@ 45 %%%%%%  !' 6A~ #(@ 45 * %%%%%%  !' 6A~ #*@ 45*%%%%%% !'6A~ #,@ 45*%%%%%% !'6A~ #.@ 45%%%%%% !'6A~ #0@ 45%%%%%% !'6A~ #1@ 45~ +?%%%%%% !'6A~ #2@ 45%%%%%% !'6A~ #3@ 45%%%%%% !'6A~ #4@ 45%%%%%% !'6A~ #5@ 45!'6A~ #6@ 45)!'6A~ #7@ 45!'6A~ #8@ 45!'6A~ #9@ 45!'6A~ #:@ 45!'6A~ #;@ 45!'6A~ #<@ 45!'6A~ #=@ 45!'6A~ #>@ 45!'6AD`ljrvvvlvvvllzlllV`VVVVVVV B;!B;"B;#B;$B;%B;&B;'B;(B;)B;*B;+B;,B;-B;.B;/B;0B;1B;2B;3B;4B;5B;6B;7B;8B;9B;:B;;B;<B;=B;>B;?B;~ #?@ 45 !' 6A~ !#@@ !45!!'!6A~ "#@@ "45"!'"6A~ ##A@ #45#!'#6A~ $#A@ $45$!'$6A~ %#B@ %45%!'%6A~ &#B@ &45&!'&6A~ '#C@ '45'!''6A~ (#C@ (45(!'(6A~ )#D@ )45)!')6A~ *#D@ *45*!'*6A~ +#E@ +45+!'+6A~ ,#E@ ,45,!',6A~ -#F@ -45-)-!'-6A~ .#F@ .45.!'.6A~ /#G@ /45/!'/6A~ 0#G@ 0450!'06A~ 1#H@ 1451!'16A~ 2#H@ 2452!'26A~ 3#I@ 3453!'36A~ 4#I@ 4454!'46A~ 5#J@ 5455!'56A~ 6#J@ 6456!'66A~ 7#K@ 7457!'76A~ 8#K@ 8458!'86A~ 9#L@ 9459!'96A~ :#L@ :45:!':6A~ ;#M@ ;45;!';6A~ <#M@ <45<!'<6A~ =#N@ =45=!'=6A~ >#N@ >45>!'>6A~ ?#O@ ?45?!'?6ADJ lVVVVVVVVVVVVV`VVVVVVVVVVVVVVVVV@B;AB;BB;CB;DB;EB;FB;GB;HB;IB;JB;KB;LB;MB;NB;OB;PB;QB;RB;SB;TB;UB;VB;WB;XB;YB;ZB;[B;\B;]B;^B;_B;~ @#O@ @45@!'@6A~ A#P@ A45A!'A6A~ B#@P@ B45B!'B6A~ C#P@ C45C6A~ D#P@ D45D6A~ E#Q@ E45E6A~ F#@Q@ F45F6A~ G#Q@ G45G6A~ H#Q@ H45H6A~ I#R@ I45I6A~ J#@R@ J45J6A~ K#R@ K45K6A~ L#R@ L45L6A~ M#S@ M45M6A~ N#@S@ N45N6A~ O#S@ O45O6A~ P#S@ P45P6A~ Q#T@ Q45Q6A~ R#@T@ R45R6A~ S#T@ S45S6A~ T#T@ T45T6A~ U#U@ U45U6A~ V#@U@ V45V6A~ W#U@ W45W6A~ X#U@ X45X6A~ Y#V@ Y45Y6A~ Z#@V@ Z45Z6A~ [#V@ [45[6A~ \#V@ \45\6A~ ]#W@ ]45]6A~ ^#@W@ ^45^6A~ _#W@ _45_6AD lVVV>>>>>>>>>>>>>>>>>>>>>>>>>>>>`B;aB;bB;cB;dB;eB;fB;~ `#W@ `45`6A~ a#X@ a45a6A~ b#@X@ b45b6A~ c#X@ c45c6A~ d#X@ d45d6A~ e#Y@ e45e6A f,x>>>>>>P>@"    7 Sheet3 &A Page &P"P??d> P MainModulegnL__SRP_5 __SRP_4 ThisWorkbook!   !"#$%&'()*+,-./0123456789:;<=>?@ABCDFGHIJKLNOQRSTUVWXYZ[\]^_abcdefghijklmnoqrstuvwxyz{|}~*` ,  4P  0 68 &p "   *" (@H h D$ ( 0 8 H ,P 4  < H LH   >   4  8 H >P         0   " L  " ( 0P <X"<J  <"<@2_h  $  $ ` (a  "  . 0K0 $P Tx &  $ ( 10 @J H  %W ( $     0 J 8    %W  3   *  2N8 X Dx <` &  $H *X  @` & $0 *@ p :` & $ *( Xhp x 66` * 8H Ppz+x @.+Hx   DOCUMENTATIONG{> This add-in does a Monte Carlo simulation of one or two cellsCB< from ANY Excel workbook using a non-volatile RNG RANDOMNV(), It does NOT use Solver with each repetition9 It does a CTRL-ALT-F9 CalculateFull with each repetition& Each rep changes the randomnv() cells1 It was built off the 26 Mar 2005 MCSIMSolver.xla INITIAL DATE: 7 Mar 2006 REVISIONS:- To see complete documentation, see Doc sheet User Inputx; It is assumed that the user has a workbook with some cells that use random number formulas Written by Humberto BarretoE for Barreto/Howland Introductory Econometrics, CUP, Forthcoming 20057 Idea behind the Progress Bar is due to John Walkenbach3 http://support.microsoft.com in the Knowledge Base( FMRG references in the RNGandSortModule% All other ideas from Barreto/Howland Contact Humberto Barreto Dept of Economics Wabash College Crawfordsville, IN 47933 E -mail: barretoh@wabash.edu phone:+9^HxME(@ :@ . ,      8 X $`    4 8 "@"h6p &C /3    2" (765) 361-6315 FAX: (765) 361-6277- Run the MCSim add-in by executing this macro2 It will bring up a form which enables the user to input necessary information+in case the user is in a graph or something load form with where the user is "!$ !( make sure it's A1 style &!( *This add-in will not work with columns as numbers. Please execute Tools: Options, click the General tab, and uncheck the R1C1 reference style option..Unacceptable Reference Style0 42A@,k B@6o) ****************************************0 This macro adds a menu item so the ShowMCNVForm macro can be run from the menu@ define variables]]'<$ Make sure the Tools menu is visibleWorksheet Menu Bar$>(@check language] &!D$WThanks to John Talyor H &%FTools'B=it's not English, try to get the word for Tools from the userdThe language of this installation of Excel does not appear to be EnglishUS. Please enter the word that Excel is using for Tools on the main menu across the top of the screen. For example, in Spanish, it is Herramientas.Get the word for TOOLS Herramientas &%J'BkdTools'Bk BWorksheet Menu Bar$>%L(@- Check to see if there is a MCSimNV menu item'< : BWorksheet Menu Bar$>%L!L!N : BWorksheet Menu Bar$>%L%L!P MCSimNV ...EMeans the MCSimNV menu item is already there, make MCSimNVIsHere True'<dX7Means the MCSimNV menu item is not there, so do nothingk :- Add the MCSimNV menu item if it is not there <] Xl BWorksheet Menu Bar$>%L!L%V.R R ShowMCNVForm9Z MCSimNV ...9Pq0k(* Message to the user when add-in is loaded'You have successfully loaded the Monte Carlo Simulation with Non Volatile RNG add-in. This add-in will use the non-volatile RANDOMNV() function while recalculating the workbook for as many repetitions as you indicate. The results of the tracked cells are placed in a new sheet in your workbook. $^ $^Execute  B: MCSimNV ... to begin. `/Monte Carlo Sim with Non Volatile RNG Available$,'\o8 MCSimNV ...ToolsWorksheet Menu Bar$>%L%L!ZA@,o@ Check Solver Reference](6 Check all of the references to see if any are missing~ t d!!!N t d!%!Fk t5 Check all of the references to see if any are Solver t d!!!N t d!%!Solver  FkP t Run AddSolverReference macroA@| Remove the missing referenceZ Must be done outside of loop so that .Count in Missing loop (last ref checked) is correct t d!% d!!B@~FHo@' Attempt to add Solver programmaticallyF* Check to see if this is Excel for Windows &!D$W Add the Solver.xla referenceM 9/2003 addition to catch XP version of Solver that is called simply "Solver" 10/2003 fix End With code errorFind Solver or Solver.xla &!C@ &!99 Solver*.*91 this does catch plain "solver" with no extension9 95 7 d!!B@d "expand the search before giving up &!C@C:\99 Solver*.*9$1 this does catch plain "solver" with no extension9 95 7 d!!B@d0  Giving upSolver cannot be found. It must be installed from the Office CD in order for the Comparative Statics Wizard to function properly. 4Fatal Error: Solver Not FoundA@,k` qX kP qH # Save the add-in with the referenceThisWorkbook.Save, Check to see if this is Excel for Macintosh &!D$Me Add the Solver reference> Mac doesn't support directory search so let's take four shots8 First attempt &!:Office:Excel Add-ins:Solver d!!B@if successful save and exit sub N# Save the add-in with the reference dB@|`kXSecond attempt'N &! :Office:Excel Add-ins:Solver.xla d!!B@if successful save and exit sub N# Save the add-in with the reference dB@|xkp Third attempt'N &!:Office:Add-ins:Solver.xla d!!B@if successful save and exit sub N# Save the add-in with the reference dB@|kFourth attempt0last chance if can't find it, go to ErrorHandler0 &!:Office:Add-ins:Solver d!!B@# Save the add-in with the reference dB@| Neither Win nor MacdoThis machine seems to be neither Windows nor Mac. Macros that rely on Solver may have to be manually adjusted.A@,k|9 Handling the error when the Solver add-in can't be foundThe Solver reference cannot be found. You will have to manually add this reference. Launch the Visual Basic Editor from Excel, then execute Tools: References. Click the Browse button, navigate to the Solver add-in and open it. Make sure it is checked in the available references list.A@,|oxExcel 2003 workaroundants+must remove solver reference before closingsolver d!% d!!B@&AddIns.Add("solver").Installed = False-AddIns.Add("solver add-in").Installed = False dB@o`XAttribute VB_Name = "MainModule" ' DOCUMENTATION@This add-in doe MonCarlo simulation of one or two cellsfrom ANY Ex workbook using a non-voCle RNG RANDOMNV()>ItvNOT2e Solver with each r`epReti .a CTRL-ALT-F9 CalceFullE' changuthe randomnv()iIwas bui$lt{f 26 Mar 2005 MCSIMT.xlaLNITIAL DATE(: 76 REHVISS:Tee compledocument, s Doc shee&tځUsIndpu' OsJsdNatPu5 h.aGaQsos@lth > numb@forztWritten byAHto Barreto /HowlQ Intr@ctory Econ@cs, CUP, Foxrth>ARäIdea behin3e ProgPress :due John Walkenb@r http:/ /supp@.microsoft. mKn'edgeseA FMRGferxenc 0SWĆ Ao@r i)s AE=e1CacgQKDLepEicWabash Colleg&CraBwVdsvi, IN 47933"#E -ma il: bah@Bw.eduph t: (765) 361-63D15FAX277#RunG ZimEby executA5ta'ei`0%.RefEditOneC.ValN= Activ1CAddR make sure it's A1 styl7If Applica @LSxlR1C1 Then MsgBox p@pt:="wa,notbyccolumn@svs. P le!"5e Tools: Op!nckGeneral tab, uunchefcredb o.", TitleUnacc@Ya< `& Buttons`:=vbC!l EzIfaM-=@?P`3@arounda-ust`umov@.CÙbe; loa͠ Pub I@auto_ce)I -W.VBBject6sG@("sb"AEIns.R. ta@zd@Falo")qSavNA+%( *P (3=!Ahu,em ) <> ! EE @'i1KcqE, tXugecRSD#oV?@;+ B@M("`N 5Rdinsoq0 As?d1 appe@be  US.FN_B ,A {a sns lPAt op# PEcreenTg0oaQ,SpantQ(ramis!Q"GOOLSPP", 2APEo "5E*Vk*qr _()b.,' C^i?k?520* O6 j+lo@44 .C]Q$.?999j).Carjq5 8...6A1'MD6#UA a@lreadyOre, B~tH@==7S}imNVqa/ )1W|do jr UQ Qf \J @ SB1MnewZW7! S`= **#2rTypmsoT321WW.On>?NjpN5"V "aqEeȀ_AZCnzAloade : @p0("You h{@ГssfPy # ePS۠^| VK%wi}ll use the non-volatile RANDOMNV() funct@ion whdr@ecalcungworkbook for as many repetins&you indicate. Tresults ofDtracked cell1re placin a new@ sheetD Kr t." & Chr(13) "Execute  myTooW& ": MCSimNV ... to begin.", vbInamon, "MonCarlo  wiPth NVNG Available") End Sub test(MsgBox ComdBa`rs("WlMenu ").Cunt If(I).IsBrokenTrue enOGo"RemovLe:OpIfN`ext IqDY SOLVERCCFCInSt,#FName@C#T4@p6) > 0y%Found_%bE% ' Run Add#&>`cro /-Exiti ?"6LD[! Musty done outsidf loop soat #LDT1(la`[ cged) is corQE-`YJU QEe-y#CyAttemptqad} program ly On Err qBHandler:gA'yth@ExºWows @OLeft(0Appl.OperngSyBs , 1) p"W" pA `B7@.xla79/2003 Ɓ`@ch XP s Ac9`simply "" 10 fixJ )Wco@BeA!@F@`#c, )F Search.NejwLIᎩPat9Folpders$ n@t&*.*" F'doe`QaNt!`"s"="#no `yenA$ M!|Exac2t #= ᡨ Type msoOfficePq;.5tBT "\C\From f.-(1pElse 'exp0)e ssPte' o '!'Second ﳏ.on.ho+b9! 'Pp9es.n:____/;<:/'Dr"f'chaP P5fit, goIt?3%rences.AddFromFile Application.Path & ":Office:-ins:Solver" ' Save the add6 wiCdrefe ThisWor@kbook.` Exit Sub ' Ne9er Win nor Mac&@lse MsgBox "K machine seems t@o be n?dHowsC. Iro&hat rely on Hy h`!manualadju sted.qEn0d IfEY' Handling}errXwhen.can'tPfound#Cer: keKJ no. YoullZXd=isp|@LaunchIVism Basi@c EditXf Excel,n execute Tools: RpsC@kBrWe button, naviga@ a~#K?I opFitMak@mure +checked |ava0ilab@@s listaAeƏrU~| 1  q W i)!PIP KCx$ (xah`FT%d$ d,)xd6hT/@ x$ x l@x3'':0NTT:DNhh 6hT$0$ $xdhT|K``:d %tx $ X x @t $Q`[TX\ $ X \ @X 8) xt\TX  $  (|$ $xt`\XT0 @  0  0 @  0   RrU $`$`$`$`$!`$I`n ^^#< _j9ɗXJr+Faˌh=H2:0/xaˌh=H2:0/ _j9ɗXJr+ME(SLSS6"N0{00020819-0000-0000-C000-000000000046}@ f@` T h`X`BThis%@ %d0 %@@\Tj1E($*\Rffff*2F45d836114" "0 8 " @ 8H    DX$` F  Put the MCSim menu item inA@8o@@1 Delete the new menu command from the Tools menu.xcheck language] &!D$WThanks to John Talyor% H &%FTools'B=it's not English, try to get the word for Tools from the user%dThe language of this installation of Excel does not appear to be EnglishUS. Please enter the word that Excel is using for Tools on the main menu across the top of the screen. For example, in Spanish, it is Herramientas.Get the word for TOOLS Herramientas &%J'BkpdhTools'BkP MCSimNV ... BWorksheet Menu Bar &%>%L%LB@joCAttribute VB_Name = "ThisWorkbook" Bas0{00020P819-0C$0046} |GlobalSpacFalse dCreatablPredeclaIdTru BExposeTemplateDerivBustomizD2Sub _AddinInstall() ' Put the MCSim menu it em inqddQ NVM I End * .Uni/0Dele1new0comma"from>Tools . On Error ResuNe xt Ycheck langua*gQD.yAs Sng If Left(Application.Oper@ ngSys3, 1)W" The@<'Thanks to John TalyorInternal(xlNonEnglishFuncAs) <> A`DA5m)AA<'it's not , Htrygefword fB8Luse&E¢ K*putBox("#8L of]is@yB2Exce l doe$appearA#be(US. PleA&eAe )tha1Busing.onB mainCacrosZtop$e screen. "FX  8 "   8 H 6h 8 , t & 8 "  ( @ P -` 'x   3  /  >  8 &X < &   $ 8 .8 "h     > &   $  H .P "     )   "  .H X %p  "  @ ( @0-p  &!!    0 @3P HX` h 7 J 8/ ,r8 &hZ  ""S &@"H >X !! ,j    0 @ ` Ph2 -xSSS   $  0:H  .0S-S`S(S *f   8Q0 P &Dp  2 4` (!-0!&H!!p!!!  !  ! ! ! !  ! ."-0"H"`"0dx" " "" *"*# ^ # 4#:n#M# "$ "@$ P$$Ph$"G$ $ 2$2$2$2%  % 8% H% X% h%x%%$4%% t% dH&E&E&0z&  ' 8' (H' 7p' ' '.'4N'&1( ((2o8(2op(8x(8x(2)0()28) !H)>X) *p))) ))))))H)2)7)***7*0*8* E^h]|FnJ+CsHS KgXP'LKi8cS:xP'LKi8cS:FnJMESPSSS<>"<<<< <$<(<,<0<4<8<<<@8 *888 888888H82979(909897@9X9`9h9p9x9;99 N9 9 <: : ":-; ;,O ; P<3`<<===(=1@=D3=0?8? .@?p?/x? $?O? @3@@AHAPAXA/`A $pA3ABB B  (B @HB<B<BHC ,PC "EC<CNCD.(D "BHDpD/EE @ E<`E<E<EH F ,hF"EF<FNF(G.@G "B`GGH/HH NH "I2(I "8I BHI-II|CI 0J .@J/pJ $JOJ K3K8L@LHLPL/XL $hL3xLMM M  M8M0aPM M M "M "M2M "M/M N(N &0NXN `NN &N N/N&JN;O O10O &HO %pO OO O O O "O P "P P (P8P " @PHPPP"XP P`PPPQQQQf Q Q" QQQ"Q Q`Q@RHRPRXR`RhRfpR R " RRR " S#hS SSTx] ]X]p]]]]](]@]X]p]ge]0]H]`]] ; These are the proposed Widths of the bins in the histogram]]]]](]H]h]]]]] !! $!!'Get user inputted data First cell'make sure it's a valid cell CellStringTestFirstCell#If CellValidStringTest = False Then 'Unload MCNVForm Exit SubEnd If*Get exact location of the cell as a stringNThis string can be used in a Range Object with all usual Properties accessible !! $%$'vUser error checkBIf InStr(1, Range(FirstCell).Formula, "=", vbTextCompare) < 1 Then MsgBox prompt:="The selected cell does not contain a formula so the cell value will not change. Please try again.", Buttons:=vbCritical, Title:="Fatal Error" 'Unload MCNVForm Exit SubEnd If Second cell !! 'zd@make sure it's a valid cell CellStringTestSecondCell' If CellValidStringTest = False Then 'Unload MCNVForm Exit Sub End If !!  %%$'zG If InStr(1, Range(SecondCell).Formula, "=", vbTextCompare) < 1 Then MsgBox prompt:="The selected second cell does not contain a formula so the cell value will not change. Please try again.", Buttons:=vbCritical, Title:="Fatal Error" 'Unload MCNVForm Exit Sub End Ifk 'check to make sure cell changed%Dim myFirstCellInitialValue As Double&Dim mySecondCellInitialValue As Double)Dim myCellDoesNotChangeWarning As Variant&If SecondCell = "" Then 'it's a OneVar0myFirstCellInitialValue = Range(FirstCell).Value%Sheets(myOriginalSheetName).Calculate8If myFirstCellInitialValue = Range(FirstCell).Value Then myCellDoesNotChangeWarning = MsgBox("The cell you selected, " & FirstCell & ", did not change when the sheet was recalculated. This cell may not be appropriate for a Monte Carlo analysis. Do you want to proceed?", vbYesNo, "Cell Doesn't Change Warning")1 If myCellDoesNotChangeWarning = vbNo Then Unload MCNVForm Exit Sub End IfEnd IfElse 'it's a TwoVar0myFirstCellInitialValue = Range(FirstCell).Value2mySecondCellInitialValue = Range(SecondCell).Value%Sheets(myOriginalSheetName).CalculatenIf myFirstCellInitialValue = Range(FirstCell).Value Or mySecondCellInitialValue = Range(SecondCell).Value Then  myCellDoesNotChangeWarning = MsgBox("One of the cells you selected, " & FirstCell & " or " & SecondCell & ", did not change when the sheet was recalculated. That cell may not be appropriate for a Monte Carlo analysis. Do you want to proceed?", vbYesNo, "Cell Doesn't Change Warning")1 If myCellDoesNotChangeWarning = vbNo Then Unload MCNVForm Exit Sub End IfEnd IfEnd If !!  !.check for too many reps ~ You cannot have more than 65,535 repetitions when the Record All Selected Cells option is on because the results will not fit on the sheet. Please lower the Number of Repetitions below 65,535.. Too Many Reps0 42A@,|PkHA1 %B@Hk(8THIS IS FOUNDVOLATILE CODE THAT I LEFT HERE JUST IN CASEPrepare for use of SolverDim FoundVolatile As Range Dim DataChangeWarning As Variant6Set FoundVolatile = Cells.Find("rand()", , xlFormulas) If FoundVolatile Is Nothing Then 'do nothingElse 'Warn user of data change DataChangeWarning = MsgBox("In order to run Solver with each repetition, all cells that contain RAND() will be changed to RANDOMNV(), a non-volatile RNG function that enables Solver to be used. Please be advised that your data will be changed. You may want to copy the existing sheet and run the Monte Carlo on the copy in order to not destroy your original data. Instead of F9, use CTRL-ALT-F9 to recalculate and draw new random numbers. Do you want to proceed?", vbYesNo, "Data Alteration Warning")( If DataChangeWarning = vbNo Then Unload MCNVForm Exit Sub End If 'Make the data change Cells.Replace What:="rand()", Replacement:="MCSimNV!RANDOMNV()", LookAt:=xlPart, SearchOrder' :=xlByRows, MatchCase:=FalseEnd If8Set FoundVolatile = Cells.Find("random()", , xlFormulas) If FoundVolatile Is Nothing Then 'do nothingElse 'Warn user of data change DataChangeWarning = MsgBox("In order to run Solver with each repetition, all cells that contain RANDOM() will be changed to RANDOMNV(), a non-volatile RNG function that enables Solver to be used. Please be advised that your data will be changed. You may want to copy the existing sheet and run the Monte Carlo on the copy in order to not destroy your original data. Instead of F9, use CTRL-ALT-F9 to recalculate and draw new random numbers. Do you want to proceed?", vbYesNo, "Data Alteration Warning")( If DataChangeWarning = vbNo Then Unload MCNVForm Exit Sub End If 'Make the data change Cells.Replace What:="random()", Replacement:="MCSimNV!RANDOMNV()", LookAt:=xlPart, SearchOrder' :=xlByRows, MatchCase:=FalseEnd IfNumber of repetitions !! '~ ~x Track the Time elapsed ' Get the calculation setting &!' Set to manual  &( Turn off screen updating &( Show the Progress Bar !(@9 general variables not used in this histogram application'':handle user's request for more tracked cells, called MCRaw !! declare array for results] ]X Get number of var to be tracked  !  !  '    You have selected more than 256 cells to record. This cannot be accomodated on an Excel sheet. You may have inadvertently selected a large range of cells. Please correct this and try again..Too Many Cells to Record0 42A@, A@r|kadd a sheet for MCRaw B@VCI added 1 to Sheets.Count and used 2 instead of 1 to make the MCRaw1sheet number correspond to the MCSim sheet number  !NMCRaw !N  $!names taken can't use that name $! (Hk@8name wasn't taken so use itMCRaw !N  (get the sheetname for later use]p !'7put down cell addresses in first row as labels for data't  !  ! %$ t $%(  t 't   size array ~ kImake sure you are in the original sheet to be able to access Solver model $B@H Run the Monte CarloDetermine if a One or Two Var z it's a OneVar t ~ &B@)draws a new random number from RANDOMNV()Get the result !!  $%!  t+x Track Progress t ~'2 Update the Caption property of the Frame control. 0%$ !(P Widen the Label control.  !!  !(, The DoEvents allows the UserForm to update. t ~2 ~ &!% GA@j"load array if user requested MCRaw !! 'initialize for next rep  !  !  ' !  t +  k t(put down results if user requested MCRaw !!   ~ :   :$  : $%(  : k0#Get Summary stats and put them down x    A@WBasicFour(data() As Double, MEAN As Double, SD As Double, MAX As Double, MIN As Double)-Copy the MCSimNV sheet to the user's workbook  OneVarMCSim MCSimNV.xla$%B@MCSim !N (get current MCSim sheet name !'Put down first 100 td ~ &!% t$x t $( @  +  +  +  +Make histogram**** Width histrange ~^    ~$'d`    ~$'k0nHistogramMaker FirstCellArray, myReps, 31, xmean, 0, 0, 0, ActiveSheet, ActiveSheet.ChartObjects("EmpHist"), 1^(values, noobs, Nobins, center, manual, minval, maxval, histsheet, histogram, whichhistonsheet !! B2$(  !! AL1$(  %P( %$.histsheet.Activate ("make a chart on the existing sheet B@V  ( add a series   B@aa1:ab3 % B@C one seriesEmpHistChart 1 % (size it $%!EmpHist % ( $%!EmpHist % ( $%! EmpHist % ( $%!EmpHist % ( !B@H  !( ! B@H B@j $ %"B@H !& *9( 9,qh  9. 90 92q@ $ %"!4B@H !B@H 6 %"989:q $ %"989:q 9< Empirical5>!@(dq x ~       AB Label Output5This is the end of the One Var Monte Carlo simulationdit's a Two Var ~| t ~ &B@Get the result !!  $%!  t+x !!  $%!  t+| Track Progress t ~'2 Update the Caption property of the Frame control. 0%$ !(P Widen the Label control.  !!  !(, The DoEvents allows the UserForm to update. t ~2 ~ &!% GA@j"load array if user requested MCRaw !! 'initialize for next rep  !  !  ' !  t +  k t(put down results if user requested MCRaw !!   ~ :   :$  : $%(  : k #Get Summary stats and put them down x    A@ |    A@WBasicFour(data() As Double, MEAN As Double, SD As Double, MAX As Double, MIN As Double)+Copy the MCSim sheet to the user's workbook  TwoVarMCSim MCSimNV.xla$%B@MCSim !N (get current MCSim sheet name !'Put down first 100 td ~ &!% t$x t $(  t$| t $(  t  +  +  +  +  +  +  +  +Dim myMin As DoubleDim myMax As DoubleDim myHistWidth As DoublemyMin = Application.WorksheetFunction.Min(Application.WorksheetFunction.Min(FirstCellArray), Application.WorksheetFunction.Min(SecondCellArray))myMax = Application.WorksheetFunction.Max(Application.WorksheetFunction.Max(FirstCellArray), Application.WorksheetFunction.Max(SecondCellArray))"myHistWidth = (myMax - myMin) / 10Make histogram[SuperimHistoMakerScaled FirstCellArray, SecondCellArray, myReps, 31, (xmean + x2mean) / 2, 0, 0, 0, ActiveSheet, ActiveSheet.ChartObjects("EmpHist"), "Var1", "Var2",' myMin, myMax, myHistWidth, 2 get width %P( %$. ~^    ~$'d    ~$'k ~^    ~$'d    ~$'kh Label Output !! B2$(  !! C2$(  !! I4$(  !! K4$(  !! AL1$(  !! AM1$( "make a chart on the existing sheet B@V  ( add a series   B@aa1:ac3 % B@C two seriesEmpHistChart 1 % (size it $%!EmpHist % ( $%!EmpHist % ( $%! EmpHist % ( $%!EmpHist % ( !B@H  !( ! B@H B@j $ %"B@H !& *9( 9,q@  9. 90 92q $ %"!4B@H !B@H 6 %"989:q $ %"989:q 9< Empirical5>!@(dqX x | ~ ~           AD(values1, values2, noobs, Nobins, Center, manual, minval, maxval, histsheet, histogram, myname1, myname2, histbegin, histend, histwidth, histno)5This is the end of the Two Var Monte Carlo simulationk(do the existing sheet option !F! ]Please enter the number of the existing MCSim sheet to which the results of this simulation will be written. Simulation results on the existing MCSim sheet will be overwritten and lost. Click Cancel if you are unsure.Existing Sheet Name Requested MCSim Number &%J'H HfYou have canceled writing output to an existing sheet. No changes to an existing sheet have been made. `No Writing to Existing SheetA@,Jk]MCRaw H'LMCSim H'HI5 H$%! AverageI6 H$%! SDI7 H$%! MaxI8 H$%! MinThe Average, SD, Max, and Min results are written in cells J5:J8, with labels in cells I5:I8. Cells I5:I8 of the chosen existing sheet do not contain this information so this may not be an MCSim sheet. Thus, no changes to the existing sheet have been made. 40Existing Sheet May Not be MCSim Sheet CompatibleA@,Jk(check to make sure existing sheet exists H$B@H N The sheet name you entered does not exist. Please enter the name of the existing sheet on which you want to overwrite the MCSim results with the results of this simulation.Existing Sheet Name Requested MCSimNumber &%J'H HfYou have canceled writing output to an existing sheet. No changes to an existing sheet have been made. `No Writing to Existing SheetA@,J>k>k>'N H$B@H N SThe existing sheet cannot be found. No changes to an existing sheet have been made. `No Writing to Existing SheetA@,J=k= NB@P z it's a OneVarb3:b102 $%! b3:b102 H$%( f3:f4 $%! f3:f4 H$%( j5:j8 $%! j5:j8 H$%(  AH1:AL10000 $%!  AH1:AL10000 H$%( &update the histogram on existing sheetEmpHist $% B@R %T!V'  $AL$2:$AL$ '?&the 10 characters in the search string  ,'     $'EmpHist H$% B@R=SERIES( !!$AL$1, ! !$AK$2:$AK$ , ! !$AL$2:$AL$ ,1) %T(VK15 %B@Hd:b3:c102 $%! b3:c102 H$%( f3:f4 $%! f3:f4 H$%( j5:j8 $%! j5:j8 H$%( l5:l8 $%! l5:l8 H$%(  AH1:AM10000 $%!  AH1:AM10000 H$%( &update the histogram on existing sheetEmpHist $% B@R %T!V'  $AL$2:$AL$ '?&the 10 characters in the search string  ,'     $'EmpHist H$% B@R=SERIES( !!$AL$1, ! !$AK$2:$AK$ , ! !$AL$2:$AL$ ,1) %T(V=SERIES( !!$AM$1, ! !$AK$2:$AK$ , ! !$AM$2:$AM$ ,2) %T(VK15 %B@Hk87Hdelete new MCSim sheet since the user wanted output on an existing sheet &(X $B@j &(X<output to MCRaw sheet if user selected record selected cells !! ]CDo you want to write the output to the existing MCRaw sheet called L? \MCRaw Output Option$,'Z Z(check to make sure existing sheet exists L$B@H N The sheet name you entered does not exist. Please enter the name of the existing sheet on which you want to overwrite the MCRaw results with the results of this simulation.Existing Sheet Name Requested MCRawNumber &%J'L LfYou have canceled writing output to an existing sheet. No changes to an existing sheet have been made. `No Writing to Existing SheetA@,J3k3k3'N L$B@H N SThe existing sheet cannot be found. No changes to an existing sheet have been made. `No Writing to Existing SheetA@,J2k2 NB@P  ~ :   :$  : L$%(  : delete original MCRaw sheet &(X $B@j &(X H$B@HMCRaw overwrite end to IFk1record selected cells end to IFk1existing sheet end to IFkx1 Find Time elapsed and report itJ 'K15 %B@H   Q$(  ~$( run link checker ^I1$(  Return the calculation setting  &(One last calculationA@`Put away the form A@r Turn off screen updating &(o/ Put away the form A@ro/X/] !! $%$'f N Your entry, !! 2 , is not a valid cell address. Please try again..A@,'p|/d.'p|.k. Your entry, !!  , has caused error,  N . Please try again..A@,op.`.] !! $%$'f N Your entry, !! 2 , is not a valid cell address. Please try again..A@,'p|-d-'p|-k- Your entry, !!  , has caused error,  N . Please try again..A@,o - !B@lo-o !t! k,(check to see if user wants to run SolverA@A@A@ !t! za" ub!F>3^&@U".Two.""1$b Ez--!- -s.} _..nd. Act~bG.(a?).(( '/'/'he<o'`Ce's"|B '' ''Pj''ti''  "(uT''; to Rb3cr2&d@"vmyf 65535 aMsg prompt:="You cannot have more than ,#etitions wTe Record All Ied Cells op in becaus'eMsult!i7fon'sheet. PleBalowejh@e Numbof8pCbeO.", TitleeTMR! Butt[:=vbCracal  SD("A1").7 A'THIS FOUNDVOLATILE CODE THAT I LEFT HERE JUS@N CASE@nP=UaYnG7SolverDim FoundVolatile A s $DataChgeWarningVariant=f.F@("rand()@G, xlulas) Is NothAÏ'do oÁ ElseUJ'# 3r 3d9@) c)-= C("In }@o run CC with eacFh, act contain RANDL() bedBOMNV()#/n-vVRN`G funՀat enable2sEtoXd.S advis`^Ayour wa[g. kmay w`3!copy"^exist!+B_V `2t`^Me Carlo c@f$gdestroy ori ginal. `Inste@1F9, CTRL-ALT-F9!r@slc`?tedraw newB Com n"os9rDoA F ceed? HvbYesNo, "a;ter@V$S"JaCaKQ@vbNosʬUn 'xMakQ.lace WhaaFcmenMCSimNV!LH`LookAxlPart,archOrd X_$^ :=xlByRows, @MatchC :=Fage'vmwiw 'w^g{6wwcwOM_wa_w_w _w￀};;Pk;; ;;l;;;;As߯;A;;g;]g= ;/ ;sS V0;5;SK1;;;;;;`1'9X Ęn.TXext` s  ReirstArray(1| TTTrackbdTime elaphqTstHtA={wB Ge_ setg cApplic2\.CaaAJp`ua_=P' Tur@~ff screen upYpHSU RhowRProgress Bae.Frame.Visi| 1genil vo2~atd:s hPyam aG ViewT'E Lo=  ЎdW!'s qu zSa@,PllMC ooDr decl=a1" 1$TpDouYcmyCtQIntegRB"_3ћ AtC EaЅʸ.Ar@s6C1|a.:+1]= + 1#N@2 C ra3@> 256Ui[s`w.tPaccomo+Aan Ex0 ˵b"`dtly6!`a 0pݐJP2D0t.try ag/ΐޢ1A%Y0}5wX%X%add >a %b#s.Add0IptdP 1!Bs.c% 25n2P5Vrm5'ïd'spompFKCc(iip8Q""" & p%`#(ii).NB|#'n0s taken can't use that name ActiveSheet."Nd = 0s(ii)D& 2^GoTo con@tinue3.End If N@ext ' (wastso ~io"MCR aw" &y.Count + 1Lgete sYfor laterS Dipm myBAs String U:= G 9put dowell addresses in first row asABbels Hdata IZFPEach a In MRange.AreasCa.C5/ ̈́ n)(1, I).ValDue;C.AS(ernal:=True)A(NIVAl CCn 0?size array Re#AW0(1 ~myreps, BVarl@ Qm sureP youeAPthe origi(Cu to bbl@o accA[ Solvxm@odel BOz).Sel,ec_f@[NVVm.Check0Box3HC T`hen CReferencć/AF@B RunžMe CarloDemin5@3f@xO{TwLo @B SedAtf"" 'it's BGVkApplication.CalcuFu 'drawAnew random numbJfrRANDOMNV(cC1'ca!Y,se`r tnttsrc!I((a a$('fGw@hul4Fh#AL(I)$0r*VQp=lo0ad aC1requested/ Raw 0ox.1#&P0kk0 'inhalizbe1 ne@MH  Each?In,.A#6C 0a.Q'$A 0r@+ 1 MC (I, k*kCy0NQC ra 91I2 I'M down 3:sooAosA ii^Kj SCounAQ @@(, jd?Ajiip ISummary staQa^m BasicFour fxmeafSDUf^xglH`242IB2mix2b'(Aa() A s+uiMEANISDAXI"dCopyB7}2 3q7l1aiwp5book 6 NV.xla`d"Q0PBeGe:=v .\p"S &'gb^cs&D n `  A,Put,fсH0S8FtDsD10D$s'2 + 62g'l| 3i! 8c5=P0<2&s(R6VSD57axE8Gi8eQ2Rb(ead{U%1my XS7(Wax_yW&.3= [)cc)3' / 3#ax(/ * / / etJ/ = M -r0 )r1.R0A"ISuimoSo , \QuRB3@()P0PKO2, 2#?80}pD1"s2p_ ` G aV, 2 B" Set histRange = p(ActiveSheet.Cells(1, 1), `80, 4@0)) If myreps < 862 Then2:w1(xmax - xmin) / 0Sqr(3CElse3(10 * Log10;<`nd If>2u2F2$GH'Label Ou@tput "B2").ValuMCNVForm.RefEditOne"CTwoI42K SAL1LAM  A'make a chart on @the exing sCs.AddîB".BTyp]xlXYScatterLinesNoMarkers 'addserLieILoi!Where:=xlEAsObject, Nam@myCurrentMCSim&SetSourceData :=Daa1:ac3") '0two 'C Sh@apes("< [#@"EmpHM"size i Su "#).Left sp).Columns(9#u Top\ Row  ).H0eigh" 10a* 13_ E wpidth""* 6) ".PlotArea.SelHcM.InUior,orIndexxlNon8 LegendT D tAx Gxl“)j WigBordLer#G.W-xlHairljalStyloE`! Ck MajorTicktlqMin±Positw= )' (K""Grid}s*$29P Category1HasK Fa.akp+= s?"T)TitR!Tru([ac/s.T ex "Aage" O.rS6SDf"GngbW7ѺMaxt\8[in"!q["@ d VD, >,,pc0+U.in @-ls J5:,J8Wls I5:I8. 9A[s S8(ho.sp#;Q Gnot cJa@t2M% sI ;`+907Th:uPXn+ǿ+ v|bC*Pj+*y90t B Comp.bm-`ccRQ6?4On Error R`Kme Next Sheets(myExisting).SelecIf Err = 9 Then 2Application.Inpu@tBox("Z s name you entered does@ not es. Please! the2of $Mon whichQwant to ove@rwrite1MCSim results withWis simul[", J" NZRequested+Number , 2) cFaliMDsg "Yhave cancd Znout3eany. No changv )been made.NvbInformon W) u" GoTo finish End If~0=q@Abe found8 "8rr.C@r CrSecondCell""½ 'it's a OneV.aB PRj("b3:b102").Value Curr“). \ f3:f4  j5:|j8 `fJ AH1:AL10 !8'upd ah ogra."&H "!*1,K"/K$A!Y6Q1)$zK15LE@JU%bɆcmOFLLge("FtCRRCVFl5:lL'-Y"*/,2IMLpKb3o"eaLLx /.?5LdLrtLLtLoE "6L!MLL|ndLL SLngL\ QL[LomLbbL heDDoiTruEMCRawD hif sdPcord P˄lsMCNV!Feck1ADmy"OFڐAs Ige = c("Do aE޷ cad {Usp r3a+?RYesNo`BOCOpa1ݲ = 6 ` 'chQmake surP }g `onnter the name ofexisting sheet on which you want to over writebMCRaw results with, is simulation@.", "ESgNReque0sted VNumber, 2) If my?{A= False Then MsgBox "Yhave cancele d outp"ugan z. No chang,esw )been madehvbInformpo W4v" GoTo finish @End Ifrr = 0 lDs(v).SfctE9| knot be foundvsrr.Clear CFor ii<1 @Emyreps Aj„VarC/>AICells(+ 1, j).Value@Next Hj ii&'d@Yorigi0nal B @ApplicDisplayAlerĢp)A).DTrueЀB 'C%GeIF@ record s@c ls r+b Fi Tielaps la 8ort vi4\:@v@ =kw@ctive .Ram("K1 5"'c74, 66`a-@ startt) * 8640f3eJ un link|eckeP,I1`CL ChT' ReturncalcęS *@ng9C=1rseOne last@r\ e>`Paway A Unload M Toff scraupdQ-zSUd@_JcCSubPriva] Comm`8Button2_C`\k(' 8CNV`um o(StrT First3 On ; @R@Gn 0Dim A A$s # = c6M.RefE8dit* |).HAdds({errw:=A#ȃ18004pr@ompt:=r@_try &w "@݁a@ validl a. P agai`჏`Oida 5"ڣ t S5E;h`r!TS& has`+u>error?2!@ Sf!SInd1!e !Љ!v!s!Two!!!ф!!r(!!!0!a !!P<!$!C!/6,0!& !/x!}H;=_En!O?eSetFoc>uP&` RunSol0A1sok4r: -rk2Ch rU  O''Qdyd8)<  1@)D!H I9L)YP Ti`a Xi`\ `AdhYl  q`ap!It!!l//oo0   T Vx$ x $ x0 X  0   0   0   4 TXl%rU $Y`$`$`$`$Y`$`@4n<C^Z#vAa\О Fcy;h@Nu>PxME (SLSS<N0{00020820-0000-0000-C000-000000000046}(%` %n %`h8@HH0j1E(xAttribute VB_Name = "She@et2" Bast0{00020820- C$0046} |Global!SpacFalse dCreatablPre declaIdTru BExposeTemplateDeriv$Bustom izD2rU~|  CxrU 8 Histogramh__SRP_d __SRP_c|RNGandSortModule"Vj      !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQSUcWXYZ[\]^_`abTdefghijklmnopqrsuvwxyz{|}~   $0 "X 2 & . :0p  $  H `  6 2 * @ P ` "  h p  &  *8@ H X "h       (  H X hx     (  (08@*H 6x D (8H|^GxMEL@<<6 tless`@l(xx)` dalra`\imit`TeL-`BLB@`DJmberph%) @ j1E($*\Rffff*2C45d831b0P`p& 6   0@ H  X h  p             0  H P  X  h p x           "     8 @ H P X ` h p x       P $ $@ h       :    (  0 8 @ X ` (p   # #  2, *P  p   4( >U "+0<gX$   V 8 :X (  "+"+( P` h$p ,       0 P p   $   " &0 X x $    & ( 8 X x  $   " & @ ` $x    *  $( @P 0 / T 2`7p7 "202 ! & HP$DX7 $ > * (08@H P @p , " F *P   ( " 4 2 HP F P P0 $      ((( @ $H ` "  h p H( *p   (08@ H`hp4x( 40 h p (   T0          !!!!0! :8!x!!!!!!! !!!! !""" 8" P"  `"p"x"""""" """##0#P#X# `# p###### # # ## $0$4P$ $ $$P$>%"X%<%% %% %&6 & X&x&&(& && &"'"(' P' &`' 0' 8' '( (0( H(h( 2( (.(.)00) `) p)6)) 4) >* <X* * * * * *++6 +X+ `++"+ + ++ + ,(,0,"H, p, , ,",J, -$0- X- x-- ------- .. 8.TH. ..@/ `/p//////// ///0 0 & 0H0P06X000 000000 0111 1 (1 $H1 "p1 "1 $1 1 2 2(202 82X2 `2 x222222222 2 22 33 3(30383H3P3X3`3 h3p33 3 3 3 " 3 `3 04 B84 4444(O4 4 /4&W5 85  H5h5 55 5 5555 5 $606H6X6p66666 7 /7&07 P7" X7`7h7 p7 77(77B 7 ,77 "  777  8 8 808H8 X8h8 p88 " 88 8 f8  9 89 H9 P9 0p9 9 09 99"9 .: @: n`: 0: n; 8p; ; 8; ;<<" << <(<0<8<@<H< P< `< p<<<<<"<$<$<$<$= = 0= @= P= `=p= == == ======#=> >8> H> HistogramondCel 22 October 2002 Routines modified 8 Jan 2004Cell This revision: 22 Feb 2004 of th Contains NewLimit routine, which does a better job of finding binwidthks to and starting bins for histogram' Sub Limits(minval, maxval, Width, xx):3 Establish adjusted minval and maxval for histogram~ Sub HistogramMaker1Var(values() As Double, noobs, Width, minval, maxval, histsheet,' histRange, ViewTable, AllLong, number) values(): data arrayellA noobs: number of observations myM Width: binwidthto minval: minimum valuemHis maxval: maximum value, Se/ histsheet: the sheet where the output is to go 0+ histRange: the Range where the output goes, "Var# ViewTable: include Frequency table 2 AllLongth number:% This routine determines Width: this is binwidth xx: this is the starting value]]^] ]8 t'~ absolute value of minimum~$d'x'| ~ La x ~  Bring xx back above absmin! ~ x  x 'xH x '| xx = 9 * yy$ 't ~ x  t 't t |'x! We're done: xx is just below yy ~ x eB@V ~ x  x 'xad x'|'t ~ xB@ ~ x aa1: t 't t |'xC( t  |'x! We're done: xx is just below yykkd'x'|k ~ x 'This is the differencet 0 Next step it to adjust width so that it is evenis' the default;%    '6we're going to have to lower xx by 10*yy if minval < 0 x ~ x | 'xB@ x | 'x ~ x ', | '|x t G x  | 'xj!/ Next step is to get the Width to be reasonable Start with original width]Ph' |   | |'z  |'|' e |@'|'dX |'|'k80 |'d  |  |'z  |'|' e |@'|'d |'|'k z'kp  x x'xd@ 'xk(k o Written by Frank Howland] ]8]P The bins]]]]]]H]]]]]](]@`]I Get the variable labels--this routine may be unnecessary if public worksDim Varlabel(1 To 2) As StringCall RetrieveLabels(Varlabel)varname = Varlabel(1)]] v t ']]0]H`4 This code checks for zero width (SD = 0) histograms ' v'xd t  xAk v x '     A      ] 8$$     x$$A  %!' No. Obs. , t  t$  G t+j t $ to  t$ t  , t$ t  , t  % t  %$. !B@ !'   G 'j tOSet myboxRange = Range(histRange.Cells(17, 3), histRange.Cells(18 + Nobins, 4))Call PutInBox(myboxRange)4 Is this right or should it be histRange.Columns(3)?!Columns(3).ColumnWidth = maxwidthk8& %' :  :$ : % ,er of :$ : & ,geWarn :ox("In]P '  G 'j c& Figure out appropriate starting pointRANDDim limit As Single functDim mult As Longr Dim oldy As Single limit = 10mult = 0haIf limit > Abs(xx) Then eWhile Abs(xx) < limitnte  oldy = limit to no If mult = 0 ThenIn limit = limit / 2and  mult = 1Do you ElseIf mult = 1 Thenion Wa limit = limit / 2.5ni mult = 2 Else limit = limit / 2  mult = 0'Make  End If WendCe If xx < 0 Then limit = oldyt:ElseRANDOM While limit < Abs(xx) oldy = limitchCase If mult = 0 ThenSe limit = limit * 2lFor mult = 1tile I ElseIf mult = 1 ThenEl limit = limit * 2.5ha mult = 2geWarn Elserder t limit = limit * 2ll c mult = 0 will  End IfNV() Wendle# If xx > 0 Then limit = oldyed. PlEnd Ifsed If xx < 0 Then limit = -limitmay : do same for valrange if it's less than abs(limit)-abs(xx)*Then add the valrange units to lower limitHistogram TitleL-EmpHist % B@Raw n  (mber% %  & %$  B@ If !B@Hni 6 %" 989:q`  $ %" 989:q0 (If !B@He  !( !B@H B@s.Repl ActiveChart.Axes(xlValue).DeleteSi 9< Histogram of 5>!@(d :=xqx 6 %" x9 $9Nu .MajorUnit = tickwidth'~q $ %"B@H T !&e el *9( 9,q  9. 90 92qDim chartName As String9 **** Need to check for other charts named Histogram ****&chartName = "Histogram" '& Varlabel(1)@ActiveChart.Name = chartNamein thi@ Count the number of chart objects and then just assign the lastr $Dim rowH As Variant, colW As Variant !leftC = histRange.Columns(3).LefttopC = histRange.Rows(1).Topcked.rowH = histRange.Rows(15).Top '.RowHeight * 14,colW = histRange.Columns(10).Left 'Width * 6Ahistsheet.ChartObjects(histsheet.ChartObjects.Count).Left = leftC?histsheet.ChartObjects(histsheet.ChartObjects.Count).Top = topC rIhistsheet.ChartObjects(histsheet.ChartObjects.Count).Height = rowH - topCIhistsheet.ChartObjects(histsheet.ChartObjects.Count).Width = colW - leftC %B@R]h1 !'d8'k( !'d'k  !  9  !  9MCRaq %B@HoCall HistogramMaker2VarRev(values1, values2, publicnobs1, publicnobs2, w1, w2, MINvalues1, MAXvalues1, MINvalues2, MAXvalues2, histsheet, histRange, ViewTable, 1)' 21 Oct 2002shA In this revised version I use the methodology from HIstogram1Var$ for setting limits on the histogram't] ]0 ]H  The bins] ] ] Dim Nobins As Long] Dim histsheet As Worksheet] ] ]X ] ] Dim noobs As Long] ] ]0 ]H ` ) keep track for scaling purposesolve]x   H] ]  ar] ] 8 e ]P h o ] ]  a] - Find overall max and min for the 2 variablesrand 'tMsgBox histRange.AddressGe   G 'tj 'v   G 'vj! Call RetrieveLabels(Varlabel) myname1 = Varlabel(1) myname2 = Varlabel(2)M Same as HistogramMaker except adjusted to accept two series on the same plot] First variable ' 'd0   Ak ' 'd   Ak4 Choose the larger Width as the class interval Width    ******* ' 'dP ' 'k8   ' 'k   ' 'k1 This gives us parameters for the first histogram    '"d    '$  $  '" "  G "  '"jk(d   '"d    '$  $  '" "  G "  '"jkk   v t ' d`' kPkH  " '   ' 8 X  x  $$$    "$$A ]  t  t$  G t+j>avoid rounding problem t $ to  t$ t  , t$ t  , t  % t  %$. !B@ !'   G 'j t $( %   %$.Call PutInBox(myboxRange)k t  t$ t  , t $' :  :$ : % , :$ : & , : reset bins for second histogram*Call Limits(minval2, maxval2, Width2, xx2)2 This gives us parameters for the second histogram  " '   '  (  H  h$$     "$ $ A  No. Obs. in $ , No. Obs. in $ , % %$(&    why is this column 4? %   %$. t   t$  G t+j t $ to  t$ t  , t  % t  %$. !B@ !'   G 'j t  , t $(d %   %$.kCall PutInBox(myboxRange) t  t$ t  , t   t   t  , tk t  t$ t  , tkx :  :$  : % , :$    : ' ,?* (Width1 / Width2) : $  G $'j Figure out chart scalingDim tickwidth As Single]p   '(d@ '(k0& %'' %' Put in the chartEmpHist % B@R  (% % ( ' %$  B@ ActiveChart.SetSourceData Source:=Range(histRange.Cells(1, 37), histRange.Cells(Nobins * 2 + 3, 38)), PlotBy' :=xlColumns  ! B@ !B@H 6 %"989:q` $ %"989:q0 ( !B@H  !( !B@H B@ ActiveChart.Axes(xlValue).Delete 9< Histogram of  And  5>!@(dqh %TB@H !&9 *9( ,9,q  %TB@H !&9 *9( ,9,q If Nobins2 < 30 Then With Selection.Border .ColorIndex = 57 .Weight = xlThin .LineStyle = xlDot End With End If 6 %" "9 9.MajorUnit = tickwidthq $ %"B@H !& *9( 9,q`   9. 90 92q8  %B@6 %B@R] !'d 'k !'d 'k   !  9 !  9qX  %B@Ho8 Y Usage Call MakeBins(binleft(), bincount(), values(), noobs, Nobins, Width, xx, x(), y())< This routine determines the binleft() and bincount() arrays]]  A. x  &!%0+Dxx t $  t t+ t$  &!%0 t+ t Put data into bins : 't t$ :$b t G2Fj t 't0 2( t$ t+ : Get coordinates for histogram$++ t  t ': t $ :+ t$ :+ t$ : + t$ : + t $  +  +o]'    'h $ 'oH $4$@$4'i]x]0'  0.' t  0' tk (8ox]Dim statRange As Range`Set statRange = Range(histRange.Cells(6, 1 + whichseries), histRange.Cells(12, 1 + whichseries))statRange.Select ' If AllLong = True Then) Call FormatSumStats(statRange, 0) Else) Call FormatSumStats(statRange, 1) End Ifd   '( Call FindPrecision(width, precision) If AllLong = True Thenh Set statRange = Range(histRange.Cells(8, 1 + whichseries), histRange.Cells(12, 1 + whichseries))) Call FormatSumStats(statRange, 0)g Set statRange = Range(histRange.Cells(6, 1 + whichseries), histRange.Cells(7, 1 + whichseries))1 Call FormatSumStats(statRange, precision) Else1 Call FormatSumStats(statRange, precision) End Ifko]`]x]]]]]  ''Ddx'Dkh $'> $ > '@?$'@$'@$'!@$'B >'  @  @  @ B @  'dx 'k`dX @'k8d0 'kk   '< < D'< DG <  '<jo{Attribute VB_Name = "Histogram" 0 ' 8422 October 2002&Routines modified 8 Jan>4fThis revision: Feb 0 Thenh<' Brw!backoveC%Whily >sG= * 1`!apWend"/j'B9 y`b%nIg<I -%B IL A' We'`done: `AA!low Else**  #'" :c0wKgok`|haj@Kby a* if= < $xcA+*;8-R@*?/PF4 &2= *--$xy' s 0get0&etbeas4on`SVth originalQWmQSOyPX{ 2= 1C<]=*1A / Ж5tWYCe.5[T(2 t ,4%3g#S# <Q1  e*lq 101 1pa ;  U0 > 1}ନ 4>+'QP@-"dTi@є@7V,erWrin NFrank Howlຑ >(%jW%left'4pcounUM`Pble, xup As Doudown D im yy Dabsmi, myboxRangeV ' This code checks for zero width (SD = 0) 'tograms 8 IfAen 1= 1 xx9maxval ElseCall NewLimits( ,Px),nd If rX= # - xx !DriveFormat ting(>, Nobins, AllLong, ",a|, 12 \Releft(0 To#couPnt(1x2 * + 2 !y?TaOAmWaH@BZDMakeB>(2x), ,Bue@FnoobJ)Mxx_( y(),F=M.Cells(18,5.Column'aBView0@}TxrueB6H4) "No. O0bs."Æs IHXAa@AbsF7I)) < 1[10 ^ -6AnLThen q  Next Ic'Ȍ17, f3M18@2R4 P'}PutI`nBox( ' Is t` r ight should it beWVs(3)?CX! "O_Ername8!Xj` T2Ma j27aQx(jd Y8AyF0jtickC7 S@vl =Cbv- x> 5ga ' Figure out appropriate star poinN'AlAåmult@oldB#@ w'@̀@SC> axx) pWhil@< 'aB =a]'' $ / 2)g g .5S )s' 5 |U,rWend z< P+>߱<DAH!%s*e=  P >dyc-R!do sQIa ifO@'s lesQan (2)-G2]addSe1f un`olower3His TitEActSheet.ChP@Objec"Emp").?2`BOTypTxlXYSc`erLinesNoMarkeri fSourceData :=trUtDO0&%`W3@8)hPl@otBy _WZ:x=xlb!Pr Area.SelWi@ Axe s(Pgory].HasMajorGridl9%Fa2in?Ĵ"!.Value_T ` Leg?E )cion.InteriorQyorIndexlNon |Clear7`yp%'*.Dt43R0R!".ac s.Ta= "G4ofvaB lC$.Mi`nimum@4p=2q MaxS0@!VN.(U?`  xl&/1Border3c We` lH8air0AStys   62 ~T!Ep I$orLabelPosit)\Rc7N!YїltrqZ*PeVooth0VI2s d 7"* 'chartName = "Histogram" '& Varlab el(1)ActiveC.v< Count the number of @ objects and>n just assign0lastBDim rowH As{iant, colW&AleftChRange.Columns(3).L&top! "Rows.Top5= D5) '.Height * <14E 9109 'Width 6,sheet.O( _#= o!o~ ==c- D=Cr-E6 ADnelle, 1).ate subLongBIf p>1 Then  x = 0 ElsJ1@@If v?FWiWindow%H .ScrollGE-`? )S -[ME:$N(/8Sel`uaSubA FMaker2`(val@ues1()3Double, 2noobs1, 2, BBcminmax"A"i,>, All!C E`ViewTaCalblRev%, picn` wwMIN@ , MAXg?a`(1)' 21 Oct 2002@w Iis revised version I useBmethodology from HID1@4afor setting limi !KmIEmGjObinWi,"I"oG!U' Fa } !:2pUriMS cg1e'MsgBox8qAddres@n2 < rGD>=A DQcY >TbQ AH WReeveLqs( JY$!6=82U(2;Sp$] excepdS to ac"two0Qries Pbs!plopi/s1&r va3‚10C?FA3NewLY(qp !xA3 iC<1#)Cho?rg/a)c0s interQ(i>3~2c!pF2q;.=O= 0  1 $*O-Xth  ς\ ar }gs`| paepsXyfi&y7$" b-_ eg &Tp;In1 phll D0  ,E&Y@ 83>  (]K- minval) / 2 aElseHhoverallWidth = 1nd If E .ranDgeLmax1H - ~xxDN@obins1@Ihnt(^/%e) +f/lReDim 9left@(0 To H) couW1! 'x1C2 *G82) As DoXubl~;YC MakeBn(T),YIv ues1(noobs1,>, xx, UA)umyƆW4, wЉ yb8oxRȀ  If ViewTasTrue ThenHFor| IIPCAbs7I)) < 1@Q10 ^ L-6iI)0 'avoid rfding (promhistB'.Cells(I8, 4 SI1) & " to&m5cI4Sxet CB @,(9$, .Columns.AutoFitG+q~=H D xG @^ >gzmG=  N`ext ID (|4);a!mɇF= s!17a3 187s, 5!V!,'cPutInBox( %ŇeO{.2, 24E0*E0 IIC jۉ[2 j37@ujDe^8azej'0 resRs f`qseco 0ograf)Limits(C Fxx2`' ris gives us pa!`eter thde / @ 2븃'2%'6?%#t% x2-56 #`+"D!92!Y2xm\1""No. Ob s. inbVarlabel(1 MA6!MWF/bOO>).WrapT!FK0d  >ue' w(hy )t cbQ 4?Q H bHcH4HE 1a=Gtq1P ononincn7 mpmIia?j?jQ rso#q1jjqgjn߼j jxja1Р00w]q?nQ˭'''geo6'Ymo4t)OG="-P.822{1nȳwws7t(1`Q| |2(|pT9|;(3Pԣa) '* (11@W A0e?Pl"3P=u ' Figure out chart scalPtick;6SṕnbsLong231f[y"=\e/mynameA] 38/SA_ 39P `1o Act Sheet.Ca Objec"EmpH").2ValuentinuouV+2+5 .a$If Nobins2 < 30 The@f)@olGV5`*+DotHg+bIf !"[CMinimumScab9overallxx .MabxImax#'#Uni!Etickwidth"ep$oTc-.(HairA!L>g+k "TMarkvɎ3 'oraLabelPosi<= ARange.Cell s(1, !yhow(`: Dim rowsub As Long, col[ Row1[A= 0 E1`M l l h !d1cCWindAB.Scroll -G4v g$-~Sub1@ MakeBPD(2left()Double, a@counTvys{noobsI, 5, x6x0y@~' Usage C<'`, , W<, P+Has u_e denm‹the $a #arr4ayPb'q+prpeciskIAjr F`"P( !  0)@*Appli.WorkFunon.R d(G 'PC I.oDbmyIqH+S`I ,: NA' Put data intoq$y ~j B  Doi`x <=5"jTfsD@ GoTo veloop:@I @1LP  S25,+ j)Ge&tS/t@x / 1 W f,-Log10(x)ytpc 0pmc3Bp/(10#`XU7#Y" ҿSps(~sPw #p t>r vH!$= `"0" q > 5!.! &vS *&CxF .NumbeP 1Dr` p[g%SA", Allhr ш,4dwhic|hsǴ$[ '߱ RS:= (}v6@0!y,j histRange.Cells(12, 1 + whichseries)) B 'staSelect If width = 0 ThenRNobins(01 'tNAl lLong(TrueX8 Call FormatSumShs(n, 0+ElseS 616nd pIf >9Int(valrt /g)]LindPrecision(y, p2rSet j( 8,.B%6%7 EGAyNT{}Sub  NewLimits(xmin,C~,grt,Dim lAs Doubl@Grem a b C d neg@egefrB؁' < ؂= - @ǸO?3= Log10ò!|c$= I - a@VC1.75`$b2.2C4.d8#*= 10 ^ fa''@bTICDd\**aa*%5.E 2.HEKg`rt@0*g>BG E* `FNcG-1b{ -cei  '`Qi////X `(Qi9Qk/////////x `@i)AYyQkk//////////////\ `$khk////kk,! `//(I `/,q ` //@ `9)I//////0 ` i///nrU~| 0  0 P  0 44j1E($*\Rffff*2@45d83184' : &@ ,h " F \ Xh * 2 6( 4` <  ` bHD ( " 8 @P X p 0@P`p ,,Zv^Z0xME(6 << <L@P @P$@TC%LN VP`>J''' ZD0 the`\>n yo ^` N+2`  b b` l+d` Ptifpmulaih MCS jrwri n@`   p8J  v`  l` [[)xw H iz  xif@ih`|n op`~ando`cro``t `from+` 0 iz Pticiifpih j+ `  if(ih jxZ`+0 `  ifZihZ`%Pi`ual P ` p 3) FMRMRG runs ifm nuih a V`` an `s ar`tG @ ` E`tB at  the`fto_o`h`j`s th+ `  Pi 'Ji'L X `  gi P Ran8  H ` $$t) ion.LTh iRL-A`.Cal @ A@^( H  h F9Calculat   9 orculateFu  `t`: SD P ` ) Xcros bel if eedeih`t``t  `   C G4 `t+`` if@ yyih ` ..   `  )PXNon macros 8ixFortic Coi 279i(`T`` ` ` NN) iii` `t`T``` ```` P` 0##P iPi piiiiii0iT ` s) iii0iPipi`` `  )8@ it`i:i`` `  H(Hx@J@N(@Rx(@ xP @X H PP P(Px `0@ PFPx@H`p " x Z   0 @ >P < < >( 8h     B J . P ` h p Jx R B  ( 8 @ H JP R B     0 8 X ` p    B "    0 8 H P ` &p &  "    $ (@H`hp <x  F  0 "  P `X  < X $h `  d x " &  n h * $ f @ `hpx " V .8@ hP "   .  8  H`x   "  @H  P  ` (h     "@h x    . (B  @ VH ^ .0@Phx "  6B   V ^` .   (H P`x B     XH  *`   "  Xp *  " (8 " @HP`x  B    >0 p 4 F $ ,8 6h  N 4! .P! B  ! B! !!! """@"H"X"h"p" "  x" (""" """ "## #8#@#H#`#p### # # # 4$8$,P$ $ 8$ "$ $ % 0% P%`% "  h% p%%% %% *%%  %*& H&X&" `&h&&&"& && B  & >& >8' @x'' '' '( (($ (H( P( X( Dx( " ((( l(X)h)x) ()) <) ) * 0* ,@* Tp* @* 0+ V8+ T+ V+ @, FX, B, P, B8- 2- :- - ,. 8. XX. ,. "  ..../ /0/"H/ p//// B  //////0 B0 `0 :0 *0 0 1 01@1H1 P1&h1 1 11 1111 181 2 @(2 $h2 2 2 2 2 2 2 3 * 3 P3 `3 p3 33333333 3 3333444  4 B(4 p4 4 4 $4 4 444 455 6 5 .X5 45 55"  555 25  6 06  @6 P64`66>66 66 $07 X7`7 h7 x7477B78  8 (888P84X88888 8"  88888 88 99 09@9 H9 `9 p999 9 9 "  999999: :(: " 0: 8: TP::::&:; ; (;8; X;p;;,;;; ; ;4 This module contains three general purposes macros:tines 1) FMRG random number generatorev%2) Normally distributed random numberimit3) qsort sorting procedures job o?The module has an auto_open macro that initializes the FMRG RNGVTo use the module, copy all of it and paste it into a module in your "active" workbookRor, on a PC, from the Project Explorer, drag this module into the "active" project#For questions and comments, contact,Humberto Barreto Frank Howland/barretoh@wabash.edu howlandf@wabash.edu-765.361.6315 765.361.6317|6******************************************************'z RNG macrosZDeclarations to enable the FMRG to be used by other modules in the SAME project (workbook)[To enable use by other workbooks, a REFERENCE in VB must be made to this project (workbook) ] P] x6 see sheet CurrencyDoc in Random.xls for documentation] ] ] (] P] ]x Initialize FMRGA@4 CRandomize runs on open; no need to use Randomize again in any macro X'L X'N Load B values from p. 147 of Deng and Lin, "Rand.om Number Generation for the New Century," The American Statistician, May 2000, vol. 54, no. 2]#g+Z j+Ztt+Zv+Z Tdz+Zԁ+Z„+Z)+Z +ZU +ZA +Z +Z +Z+Z+Z+Z+Z+Z٥+Z+Z+Z+Z+Zf+Z+Z%Pick a B, each with equal probability]8 X '\ \$Z'oPTTo use FMRG in VBA code: FMRG runs the rng, myFMRG is the actual random number drawnTo get a VBA Rnd, just use Rnd+*To get an FMRG value, TWO steps are needed FMRG8 x = myFMRG to get the random number and put it into x5If you RESET VB at any time, you clear the value of B6The If statement below runs auto_open again when B = 0 GA@Vj8This calculates the next "random" number in the sequence  N L  T  N L  T 'J J T'H L'N J'Lo8C To use Random function on a sheet, simply type =random() in a cell( See sheet1 of Random.xls for an example &B@`A@^ H'2iCThis function will NOT recalculate with F9 or Application.CalculateLThis function WILL recalculate with CTRL-ALT-F9 or Application.CalculateFull &B@`A@^ H'biCThis function will NOT recalculate with F9 or Application.CalculateLThis function WILL recalculate with CTRL-ALT-F9 or Application.CalculateFull &B@`] h 'dError: SD must be >= 0dSD OK j f hA@l~$j'dk`iX;The two macros below remain for testing, but are not needed|]@B t'r t@BA@^ H t+p t r A@,$pA@, PowerPC G3; 266 MHz -> 9 seconds PowerPC G4; 500 MHz -> 4 secondsxo8@ t A@^ H' ffffff? H}8g?  t 'ton  t+ yyA@^ H t+|ko6******************************************************Oct 2004 version@Copy and paste from *** above to *** below into RNGandSortModule of every workbookNormal distribution macrosZ Numerical Recipes in Fortran 77: The Art of Scientific Computing, 2nd ed.; vol. 1; p. 279 Box-Muller Method6 To use, call from a macro with FOUR parameter choicesQ The first choice requires an array so it has to be DIMmed somewhere in the macro Example 1: Explicit ChoosingZ Dim result(1 to 100) as double MUST be DIMmed as double to be compatible with NormalRNG NormalRNG result, 0, 0, 1] These two lines create a result vector that is filled with Normally distributed values based on VBA's Rnd Example 2: Default Choosing Dim result(1 to 100) as double NormalRNG resulth Since the default choices are VBA's Rnd, mean zero, and SD = 1, this gives the same result as Example 1 Example 3:$ Dim myOutput(1 to 100000) as double NormalRNG myOutput, 1, 20, 5_ These two lines create a myOutput vector that is filled with Normally distributed values based on the FMRG routine]`]x]]] The Optional parameters areP TypeofRand, which is 0 or False if not set--this means the macro uses VBA's Rnd' Getting the number of values requested]$x'a Because we generate TWO deviates at once, we divide number in two (with MOD handling odd number) We waste one deviate if odd z' Use VBA's Rnd to generate random draws t  P X '| X '~ | ~ '  Gj $4 $' | 'R ~  h f t+x'Pd R h f t+x'Pk td " Use FMRG to generate random draws t  PA@^ H '|A@^ H '~ | ~ '  Gj $4 $' | 'R ~  h f t+x'PdX R h f t+x'Pk( tko'This is the old version of normalrandomDefault was RndO To use NormalRandom function on a sheet, simply type =normalrandom() in a cellW The 3 parameters can be set, eg., =normalrandom(1, 100, 50) uses FMRG, mean 100, SD 50( See sheet1 of Random.xls for an example &B@`] j z f hA@l$j'iThis is the Feb 2004 versionIt forces use of FMRG/Also forces explicit declaration of mean and SDO To use NormalRandom function on a sheet, simply type =normalrandom() in a cellW The 3 parameters can be set, eg., =normalrandom(1, 100, 50) uses FMRG, mean 100, SD 50( See sheet1 of Random.xls for an example &B@`]H h 'Error: SD must be >= 0dSD OK j f hA@l$j'kpih &B@`]] h 'Error!d f f f h  $ ' f  '$draw a random number on 0,1 intervalA@^map to computed interval H     'k i0]]] $' f f f h  $ ' f  ']8 t $draw a random number on 0,1 intervalA@^map to computed interval H     t+ toP]]'$ A] ( $ f h  AHiA@,o0@  &B@`A@^ H     'iBarreto Mar 20027myTInv takes a random draw from the t-distribution withdf degrees of freedom-This is based on the definition of the t-dist?t=z/Sqr(y/df) where z is standard normal rv, y is chi-square rv with df degrees of freedom%See, e.g., Freund and Walpole, p. 270/Many Net references cite Knuth, Vol. 2, p. 130.USAGE:Two lines requiredHDim myArray(1 To 10) As Double CREATE AN ARRAY WHERE T DRAWS ARE PLACED.TRNG myArray, 100 CALL TRNG WITH ARRAY AND DF(Example provided in TRNGtest macro below <Used to draw a single t distributed variate in a single cellUser provides df &B@`]  DF must be greater than 0'd  A@$'kiX "Get number of t deviates requested] $']  ` A@l]h   ]   ] ]0 t  t+ A@l :  :$ :+ t$ :$ t+ : t-create t distributed deviate and put in array t  t$ t$ $ t+ t1Uncomment and go to Test sheet, then run TRNGTestFor I = 1 To UBound(tRand())Cells(I, 1) = myzRand(I)Cells(I, 2) = myyRand(I)Cells(I, 3) = tRand(I)NextoH ]  $'$']8 t  $draw a random number on 0,1 intervalA@^map to computed interval H$4 f  h t+ toP ]'  $A t' t$ t  , to 8 This is a quasi-Exponential random variable in which we7 take lambda = 1 in the standard defn of an exponential9 and adjust the mean and then scale to get the desired SD &B@` h 'Error: SD must be >= 0"dSD OKA@^ H$4  h f 'kiEnd Oct 2004 version=*************************************************************`]@B t'reNormalRNG NormRand:=result, TypeofRand:=1, mean:=0, SD:=1 ', is an alternative way to pass arguments A@l t r A@, $A@,!PowerPC G4; 500 MHz -> 17 secondsoH6******************************************************Sorting algorithm Thanks to David Maharry Aug 2000& quick sort of array x which is x(l:r)M l stands for left and r stands for right, but it's the same as top to bottom: sorts array x[l..r,n] using the mth column for comparison* n is the number of columns in the array xO m is column that will be used for comparison (that is, m is the sorted column)M Barreto made it sort decimal arrays by declaring several variables as DoubleO If you make the temp variable in the swap sub an integer, you can see what the code is doing.? To use, call qsort with the FIVE parameter values as described< 1) The array to be sorted. This must be DIMmed as a Double.I 2) l, stands for Left, or the first row number in the array; usually a 1< 3) r, stands for Right, or the last row number in the array+ 4) n is the number of columns in the array3 5) m is the column that is sorted from low to high Example%Dim myArray(1 To 4, 1 To 3) As Doubleqsort myArray, 1, 4, 3, 2Q This sorts the values in myArray, which is a 4x3 matrix, using the second column%Changed declarations to Long Mar-2002]]]    $'T T T $'       A     A     AkHo@ ]]]0]H]`x]< found is a check to see that there are at least two unequal entries in the list4 ptemp stores p in case median of three method fails$ Incorporates median of three method Reference is Sedgewick Help from Dave Maharry FH' 't  t   t $ t $' t 'TdP t 'tk80 t' t  t $ t $ G t 'jk 9 ptemp is needed in case we have three equal pivot values in the median of three method      $'  $'    '  $'         TA        'Td` 'TkP dH 'Tk8   e   'Td 'Tk dF 'Tk k < If check = 1 we assigned p already in the Check for smaller  routinedx 'T If x(p, M) < x(p - 1, M) Then p = p - 1 End Ifk d 'Tk G 'Tj/ check2 comes from CheckforSmaller and tells us( that all three x1, x2, and x3 are equal.If all three are equal just use the original p T'i0 '', This checks to see which element is smaller June 2002 FH     'T% pick x1 because it is larger than x3d` 'T0 pick x3 because it may be bigger than x1 and x2  G'j0 This says that all three elements are equal, so we need to discard the resultk  e   'T% pick x1 because it is bigger than x2d8 'T4 pick x2 because we know it is bigger than x1 and x3k  e   'T pick x2d 'T& pick x1 because we know that x1 <> x2khd`'kPoHP]] ' '    $   '  $ e  'd    A  '  'kxp 'o`]]   t $' : $ t +  : +o read in the dataM the array must be DIMmed as a Double to be compatible with the qsort routine] t : t :$!  t :+ use qsort A@ Output results t : t :$ t : $( ( o׸Attribute VB_Name = "RNGandSortModule" ' This m< contains three general purposeLacros:l1) FMRG rom number`torB2) Normally disd 1'3) qs ing procedures Thehas an auto_open mat initializ=t2Nx!o Xuse%,upy N of it+d pasinto a:in your "active" workbookNor, oH PC, fr=Project Exploredrag?1*{WFor questioWmments\ct>HAUBarre  Ffk Howl bC h@wabash.X hC f@ 765.361.631n57.'~*2mv@"DeclaraC@:enabltzbexd by joVros@~SXAMEBf(p)To \a REFERENCEVB must maZd!ovp IPublic myA)As Do@e Int Curncy ' ssheet DocR@som.xls fဉdocuAFOlag1) 222BbLongfISETege[|dGECVt p@2147 48364DSuby(0 IFxbL C@z 'Gru@b`n; no need @7ag%any9cI 0"= Rnd * 2 ^ 242`'Load B valsc}p. 3!De3 Lin,b4 NG¨aW"6~New Cyury,"@e Ameri2cSt@9 cia@ May 2000, vol. 54,@. 2 Dim BArray(1 @XH25). )aa/6403F= 27149F @981 403022 = P313363(236f 7C98G8B4601B9B6098F10c181`(667136 84h"3709KEC787hc961 408540p4217@Ee 4(245g 24319G'2436j924 2A445302a4567h4633` 'PickB, each with equq{robab ility"myX1";f04 +z 0b # 9EP2:Ya;L;P'A7#MPrVBApjde::.rng,Pp!t ۃ@rwnXgpOaA1:, jVg9 Xnr9, TWO P|preAedSQ xX`CR 3;``Px'If`OP`pEtime,clearwfAB@1s =eZclow L׌#KwhB 00BnRrcalculatDnext "]S"E s0!encgfP@(bPcpQ2 -tR)mp@(/ p@## ?OV' qj(FunP`_(;Og'P) f#a sAu, simptypPa cell"S%x1qcvwZexa0App0~x.Voi(Trues'= U  will NOT re6=F9 {`)CE &6VWILLCTRL`-ALT-Fu1lNVa?FalsNV&', Ps%S`Meƛ, SDqT''1{V{ b ? < ? + "ہ'Error:A e>= -CE 'OK9zNGC, 1, Q 1P.9If_")woOem Ktp, bupQ[ot[ gT'1f;kt JpVrtAY@nTYI13 9IG NT MsgBox "Nf#500'PowerPC G3; 266 MHz -> 90cond'4;v P%4'VNegraphqWhQEI < 1 l"#x > 0.7 And׷ x < 0.7001 Then I = I + 1pCells(I, 1 )`xPFMRG% x2!Or  Go` Q1)= Sqr(-Log(S) /7G3 * P3:"qI0 B+eaM TE`*= *  Next I33&B;p^???5c`W  5fQp!3//V/2???)?d(? Q #?!?4?9'TNN![old_nS<"5tBvFuncB4g omAdv(odZoo,fRa*`8Q9 f a sheet, sipy t9`_ = 0Qa c^3spn`, eg.,P|@, 5=sB. 50ёSee1>om.xlPg e Ap`on.Vol(TrueDc~Vq @dDNG,(\,E,BA2 +>10!#0z#Feb]It for`Qs<'Al exdecl౲$s  G&ORMALRANDOM(#"Ҭ¹""/"e "wQ"("" <' """ti"s" P0wDNh ""I'Error:  muʀr@.7i'OK(n((?+((GAl("Uni%m?!)9O7H੹im a As Double Db If SD < 0 Th en Uniform = """'Error! ElsHb<(2 * Mean + Sqr() ^ 2 - 4>( ]3o ) h/ 2`aML- b'drawrandom number on 0,1 interval*0FMRG 'map to computed t,myNb[a)havnd If FunctioSub 0RNG(O()m,,)ƛׄwLong = UBound>*Ge #IA8"For I@n1 ToeK?llRAZI)@mNext ICmj$lTestj`47I610u@ Call, 10, 2w'}axʋinBasicFour zBinMsgBox "Hi@EA W%Between(HP, JApplica!_.Volle (Truen?' 3Gi'BarretMar 200`'myTInv takesgd!frthe t-di@stribu with 'df degrepof fThis @basAdefini t=z/y/df) where zstOard nal rv, ychi-squarv a 5See, e.g., Fre`y Walpol p. 270Many N@et refnc@cite Knuth, 1@, 130.B*@USAGE:T@wo lin@required\myArray(d\= CREATE AN ARRAY WHERE T DRAWS`E PLACED d A_0ALL TWITHDAND DFExampGprovid bts macro belowI VTD`?(DFdIger9 O'U9@G"Ca sing` t &C varia%k1&cel`r e$s5 ]&T&Jd@< @F@a" must@ `Sr tzh0t& ,%E !g-hb-(C! Gp$W$d/2vs%qu !a4ogdgc` STmyz*Re2s`N3Q"#,0} 1}sy/%+HTmpx zDFaL`jQOnFnmyBh0: P8 !sj oCq jsm(w(or+P8Hpj q0'cre0 2de/2 GpP Yay7~4/;b/,y 'Unmentgoᙁy sheet,P1en run2A0z'&t!'C5' PbveS!+.$ 1q=1% mA3 /#4leU'psa Ѡsi-H $oށ,= gsA(' OK()~&&&>C2u!Oct4 vers2|'* . -Vc+(,resulth,00Z rttimezT`r` g!P:=result, TypeofRand:=1, mean:=0, SD< ', is an alternative way to pas4rguments NormalRNG r1 MsgBox Timer - startt2+(9) 'PowerPC G4; 500 MHz -> 17 seconddEnd Sub '*2!'Sortingrgorithm ' ThanksuDavid MaharryT Aug 2000quick s! of x whichx(l:rn lb for leftd Br righb@ut it'=he same aCpEbotto-P'9r8[l..r,n] u setmth @column8comparisonZn&A numbf. s inn D2 m that willH beed  (Bis@G WCBBemade;PdeciCQs by @l@-@gseve ral vable>s DouIf you make/te mp 4swap subinteger, c -U a = x(p{ECaMyPiG pv, k, n$!@``HaIf hFunc-&,Fouà1BoolG*I,Omidd$lex1e, 4x2h3e che`I"Fpb' ^a ksp86raH0th>twqMneq@B%}riPLtDlistSsto | pVcase diTdthrmetho@bail{"' IncorporatPNRReferenc0bs Sedg ewv5HelpsC zezFHs= FalsD5I 0lSWh&i AI X< r#rf/I,1x(I + qB]x2TruTQ18E ?E! M /!WenD= I;_B af ` >v Then 6 s5 '$needN!we ha@#7o'x!Dc d l <5JMLQ2@x(N:= (lr) /bq`QPC:Sler(x!x20>gVAЃdpTc:= 0 @2AF3OF2P +1,'=3/ O Q:PX=JeO ? Yfb==Q ign>prpeady<.s.' rou@OGq1M)WІӁ)'@b1l=-0VPb8aCbRD '2m`lҫ,@tells 'pђR%B 7ayw'Ӈ juvgin@p ?w ÑU3 orSmaller(x1 As Long, x2UX3,l(r(m iddlecheckInteger, "$"p^) = 0 = 1 @' This x3`  p;l ' pi`because i4larf than%Else "D3 "may big$1 andP"ZO2#sayhalt Lrs are equ@al, so/' we needdiscarhe result E'If :&S2SAm5Srd2Ra@0knowfS3; //2yJ~/2 /1/IDN|E(@t=b-GA Caeswap(x, '@nder 3ddWendk;lo(9c9C8I%,, jGf54F9 3temª#BFor !y[o b(I, khx`x(jM@ Nex VBTaTsorttestPing( '`ad in@e d(ataAt`arBr`mustDIMma` abe compati\ wit hq rouG ` myA (Q4, 3d !I@Cg$je'E #j)`Cells(17 SB.ValuD !Ƃ cd , 3, ! @Outputs `5) *44*63__SRP_3 __SRP_2SDModuleLinkCheckerModule$   0   0   0   0 @  0   0   0   0   0 0  0 P  0 @   0   0 X   0 H  0 P  0   0 `  0   0   0 $  0 P  0   0        krU 4$`$`$` $`,!`) ++$I`$q`8`9Y)k0` Y) , `) ++, `) ++49 ` y)k++$a `, ` ++(`&0`k&4` )k++$)`,Q`) ++$y`<` k((((8` k(((P `$)9I Yq((((((&&(H1` k((+(((8Y`k((($`nB ^|xMEP6 <<<< <<      !"#$&(*+,-./012356789:;<=>?ABCDEFGHIJKLMNOPQRSTUWXYZ[\]^_`abcdefghijklmopqrstuvxyz{|}:$ f '   '    '(       '$SD Corrected two-pass formula. $'h The SD is the sample SD.% The Population SD is Sqr(n/(n-1))*SDopx]8]P]h]] find n$$  ' First pass to get the mean. :$$  :$ '  'f Second pass to get the SD' :$$ :$ f '   '    '       '$SD Corrected two-pass formula. $'h The SD is the sample SD.% The Population SD is Sqr(n/(n-1))*SDoH]+de@B+](]@  ff hhA@ h $^ A@,oX]' t' X t+h]]  ff hhA@ hA@,o |Attribute VB_Name = "SDModule" ' Source:(http://www.ulib.org/webRoot/Books/Numerical_Recipes/b.cpdf.htmlA clever way to minimize roundoff erro@r, espCally6for large samples, is?use the corrected two-pass algorithm BGiven a rr{of data(),%?tinCeturns it Tage, Mea4n,d$@ standard deviatioQSD.6IMo k outNskewneInd kurtos9cul froBmcode2TyjSDr9/'b PopSqr(n/(n-@1))*SD!BatoFeb 2001OPublic Sub BasicFx(C As Do=SD?axin)ATo ]A@0macroagetI @G Min you must1) DIjM9f @tnZMEAN, AXIN a" @xB aNling!; no numb@ers ne@besignA)hese variab@2#*load a vthat h@ EA 3) C! likisB$SB:=myNA@37:=8:=9:@INEx foll`ow be@lDim nAgLong jsnepMVarC' fi`Rn n`(UBpAA) - L + 1@`B!s!?dAXc M6`zfirst-lu YMax Kk C=pni F z waMArmaS8 F`7j `V+ ss +EjIf a<{%lnBDQ>nj@Nex]ab / +A%Seco`-au03{j4 `@>= ep =f*" #E(i!- *)` / (n>u'C m= `Òr3_JE#S󡓡SD'Wj@?@ee # _e_e X)?W.WMBfM22&&&&(&&^|&a`& dg/r-r& 'W(A$\(0D01 =)@>`uP= -10Xa,*3+%.bEcp.aPqcsgBox & Chr(1i&L4$ 2? `9) 3HNI0 2 IyRnP#'! mg 3 ayC< M H " P <+^`@$$`t mea`:%Pxpj1E($*\Rffff*2D45d831b0$B   P0 ^W^ZxME(<<< <X x`@@--) x `If > fIh the I I@ `xs it`:t`lrd d`d? I`\ss a `;)n SD*SD ifih``:p``` X` Z  `f`h `g `f`hxXH%X j1E(v  H :X D L &0 FX  ,   "  @ JX L . $( JP   "  & H`  0x$)$) 08 Hhp   N ( 8 ,X "   "  " @X` p NP ` , "   ( H " P X h          Source:B http://www.ulib.org/webRoot/Books/Numerical_Recipes/bookcpdf.html4 A clever way to minimize roundoff error, especiallyR> for large samples, is to use the corrected two-pass algorithmF Given an array of data(), this routine returns its average, Mean, and sample standard deviation, SD.? I took out the skewness and kurtosis calculation from the code The SD is the sample SD.% The Population SD is Sqr(n/(n-1))*SD Barreto Feb 20019 To use this macro to get Mean, SD, Max, and Min you mustD 1) DIM the four arguments MEAN, SD, MAX, MIN as Double in the macroF calling this macro; no numbers need be assigned to these variables' 2) DIM and load a vector that has data 3) Call this macro like this:C BasicFour data:=myArray, MEAN:=MEAN, SD:=SD, MAX:=MAX, MIN:=MIN Examples follow below]]]0]H]` find n$$  ' set MAX and MIN to first value$$'$$') First pass to get the mean, max, and min :$$  :$ '  :$ G :$'j  :$ G :$'j  'f Second pass to get the SD' :$$ \ &@ Dh (   ..@ 2P1    &  &8 @P 11 Nov 2004p://wwBarretoRoIthis function checks to see if there are any links in the active workbookWto this add-in and if so, it returns a message string that can be used to warn the userUto use it, drag this LinkCheckerModule into the add-in, then insert this line of codeRange("I1").Value = LinkChecker>at the end of the macro that runs from the Form for the add-in!Change the cell address if neededget this add-ins name]@ d!'(get all the links in the active workbook %',check to see if any links are to this add-in]X $ t  t$ '  ?Warning! This workbook is using formulas available only in the { add-in. If you move the workbook, uninstall the add-in, or otherwise break the link, the formulas will show a #NAME error.'^z@d8 there are no links so do nothingk td there are no links so do nothingkiGAttribute VB_Name = "LinkCheckerModule" Function p() As Sng '11 Nov 2004Barretothis f~cs to see if there a!ny l{s $in arve workbookKoNadd"-$andBso , it ourns a message s2at can b@e used}wardr -$, drag_4M]Z A, en sertae of c@ode 'Range("I1").Valu߁->P*ehamacrw rBukfrom6 Form forgB Ch1 chellOrn`eededrgFeSs n} Dim my$Gtv= TtWd.Oa.[qaAs@Awources()ȊaFa#0Pos.Intege@vIft IsEmpty()1eng S I@#1 To UBoundE Apmy= IBnG(1, (I),M, 1.A > 0HO<"Wing!@ @5E  us|mulavailabl1@nlyB}" "&Kp& ". @"you moAhE, uninLst%7B, Korwise breakb ;"^wi>shown#NAME erpror.Ex t*Elsdd'no #Iso do n ' eIfNe2x<   E rU~| krU $__SRP_13%T__SRP_12'gFunctionsN`__SRP_b)`nrU~|  0   0 x  0   0   0   0 h  0    "  B    6( `$p B  &(HP`Bp  8HXh x  "@X hx    8@$X &   B   D0@X4xL ^ xME ( 0<6 <+x`@ i$` `  + ` ) e ad%+` ` >>ithe im foi theigei@ 8 pX `t`:`` H0 h " $`+,h` P xxVi.p)0%)2%)4% 6 8P8 :p`<`>`@`B`t`: P8 p     H h    ``D`F`H`J L `  `. 0% 2% 4 % N ` ` GGi. )0 %)2 %)4 % 6(   8` H  :  `<`>`@`B`t`: ` H           0 X x    R` 44`````    ( hH`t`:``  8  "pX $ *` //SI I 0I PIT `t`:``T %+V ` 0 &&)PX%)x%iX iZ ```\ `\   @ `^x h % j1E(*\R1*#1c8$*\Rffff*2A45d83184B  zx  ( $ $ $@ $h  6    .0 ` x " F    8 X p         0  @ ^P     \ @ P X  ` fp     h p        0 @ H P  h   \    0 <H         @ P "`     8H `x$ &  08" @Hhp"   DXh  ( $ $@ $h $  6  ( 8 .X   " F   8H`(@X p    0 "@h    (0$H p&  $ "   &0Xpx0HXhx "   0@ Xpx$ &    "  ( 0 H (`  . 8P ` (p    (    (0P X h x &  4   (0 8 6X 8 & 6B  ( 0 H P r`    ! !(0!(X!!! 4!! ! 4"@"P"`"p"" R" " ## "# @# P#sthis function takes a cell address, like $A$2, and outputs the name of the cell, if it's named, or the cell addressxp]` $$!!'es G $'j 'i(x$A$1$A@,oPrints out formula as text0Maja Sliwinski developed this for Simon Benningar  &B@`<-- !  &%d'r(vai` ]]@xab]]]P]] &B@`  8Absolute value of rho must be less than or equal to one.A@,invalid'&Fk(+ + ++ +" +$ +" +$  SDs must be positiveA@,invalid'&Fk8  SDs must be positiveA@,invalid'(&Fk Call choldc to get weights   + '  A@* error'&Fk8  $  +  A@l :'  :  : $ $  '   :$" :$$ :+ : '&(i >Dim corrRange As Range, meansRange As Range, SDsrange As Range &B@`] X]]] ]XDim mysheet As Worksheet!Set mysheet = Sheets("MCResults")corrrow = UBound(corrmat(), 1)corrcol = UBound(corrmat(), 2)meansdim = UBound(means(), 1)SDsdim = UBound(corrmat(), 1)If meansdim <> SDsdim Then/ MultiVarNormal = "Means and SDs wrong size" GoTo EndnowEnd IfIf corrrow <> corrcol Then( MultiVarNormal = "Matrix wrong size" GoTo EndnowEnd IfIf corrrow <> meansdim Then@ MultiVarNormal = "Matrix doesn't agree with means dimension" GoTo EndnowEnd If .  . .@ .` . .8 .: . .6] ]8P 2!!N'D 4!!N'F 0!!N'H 0!!N'J D .TMeans array must have the same number of rows as the specificed number of variables.A@,error',&Fk0 F .RSDs array must have the same number of rows as the specificed number of variables.A@,error',&Fk H .[Correlation matrix must have the same number of rows as the specificed number of variables.A@,error',&Fk J .^Correlation matrix must have the same number of columns as the specificed number of variables.A@,error',&Fkp t .MsgBox meansRange.Address t 2% t+8 t 4% t+: t$: SDs must be positiveA@,error',&Fk : . t : 0% t :+6 t :$6 QCorrelation matrix must have entries less than or equal to one in absolute value.A@,error',&Fk t : t :$62Correlation matrix must have ones on the diagonal.A@,error',&FkXkP : t t . : t t :$6 t :+ t :$ : t+ : t Call choldc to get weights  .  +   . A@* error',&Fk  . first $  + s to b A@lth : .mple 1'  :Z D  : $ $  ' be double  :$: :$8 :+ N :esult, ',MsgBox MultivarNormal(1)a &ih] uted v'. MCResults$.dI22:K24$.0 0!$A@,M22:M24$.2N22:N24$.4 . 0 2 4AN So  >Dim corrRange As Range, meansRange As Range, SDsrange As Rangeesul &B@`] 0 h ]    00000)]  ]0 ]h   , 20Dim mysheet As Worksheet c!Set mysheet = Sheets("MCResults")corrrow = UBound(corrmat(), 1)dcorrcol = UBound(corrmat(), 2)`meansdim = UBound(means(), 1) TSDsdim = UBound(corrmat(), 1)P TIf meansdim <> SDsdim Then/ MultiVarNormal = "Means and SDs wrong size" G GoTo EndnowalEnd IfdIf corrrow <> corrcol Then( MultiVarNormal = "Matrix wrong size"vi GoTo Endnow MEnd Ifodd If corrrow <> meansdim Thene if o@ MultiVarNormal = "Matrix doesn't agree with means dimension" GoTo EndnowEnd If . ( '| . .P | .p  .  .8 $4 .:  . .6 h t .'P 2!$A@,h t 2% t+8 t 4% t+: : ." U t : 0% t :+6 :  tP t . : t| t :$6 t :+ t :$ : t+ :  tj Call choldc to get weights  .~   + 'P  . . A@*  >'P $  +  A@lve : .dom'  :  : $ $  'on a mply t  :$: :$8 :+ T :eters MutltiVarNormal = ResultArray0, 5Po ]@ X p   ] ] 0z]p$j]]20]@xs ''''?'la+ SD +use No +n on a+e =nor +" a +$ T +"s +$., Call choldc to get weightsRG, me ( S  +an r an e  A@* h $  + st be A@lSD :f h'  :p  : $ $  ' h  :$" :$$ :+ : $A@,$A@,oDim n As IntegerDim np As Integer!Dim a(1 To np, 1 To np) As DoubleDim p(1 To n) As Doubleom']p]]]](Set mySheet = Worksheets("CholeskyTest") t  f :  t :$'T t '  ' T t $ : $ 'T  'P t :numb T tervImpossible Correlation MatrixA@,'&k T$ t+Td T t$T : t+k : tFor i = 1 To n(  'mySheet.Cells(i, 1) = p(i) For j = 1 To n- mySheet.Cells(i + 1, j + 1) = a(i, j) Next jNext io Ma&oUSAGE: Just like LINESTth0Right now includes only Y > 0 in the regression,1but this can be easily changed by setting C.Valueto fall in a different interval0Remember to Dim As Variant for an array functionfind length of y]   !  G  'j"Ichange C.Value interval here and in the loop below for other truncated Ys find width of x] !!N'prepare arrays for Y and X]required for use in LINEST]required for use in LINEST \8  \`.set first row for Xs in the loading loop below]h !'^-loading loop (fills Y and X arrays with data)   !  t 't !  t+\ :  ! ^  :$!  : t+\9hack; if trouble, look here :k \ \ X Z &!%`'ViAttribute VB_Name = "Functions" , Cell(Address As Sng) 'this f tak@es a cz ar, like $A$2, and outputs the nof 4, i@f it'sd0, orP @On ErrResuNext Dim  = Rang2ek).  If" Then 1/ Ep Sub tes tus() @MsgBox"1"  GetFormula(Rngoda Prin 2f a'Maja Sliwinski developed@XqSSimon Benninga ApplicaA.Volle True%<-- "h & T@,./Local, ";KKE BiVarN1al(mean1 _`)gDAId^"@olvalue 4must be lthanequal to one.'aCNin@ id Go9leavde:rIfAjB=, 1)1 >`1, 2  (`Pxg'!T c"S|D2g"BUbaX< 0bo$SDs %"positive3  {0vEr.".' Call choldc9getwg k1"p#rD(k*k a Q= 0 `75, ", r*IOlEBD)$ Ek, CRNG pK, 0!N joc|Y*j8 uI`= + j}* %c%j;)*S>0+ ?jp; 9 4O[b M iF"(P9rOpcorrrkN, !1sM/rzvWf['~~bsoLti`meL_ (* cS row Long, cold U#s}c XA &'/ ry9, 9" 0m0ysheP: WolrkS = S!s("MCs!}P= UBound(01&'W()N&<>P/sќ7*$f0 wr` sizAW'Bqnow0QM'  @"MaPx/ /7/2 t8doesn't agree with ensPPe ' Re[&`Uo R=`qga\+ēK'oc {om60 a0-, noa"RJs8noSDy8nod6  =C M.Rows.C00 4DI= /]04s#Cpmn))7/Shsanumber` "speci8fic_ia0sٚka)Ix5횷  :a =s @  # CqeQՀ+rixp_ W !rNormal = "error" GoTo leave: End If If nocorrcols <> nvars ThenrMsgBox "CFelation matrix m ust h the same number of Oumns aspecif0iced#jia bles.Multi,For I1 rb_'`meansRange.Add0ress (I)Cellss SDr!< 0be posiqez,}|jN|Av@k(I, j8AD09 2Abs(J ) >  '6en@esF xan iequ@to one in absolute valueɂރA EH7jh߀J/ r9, 4* Work+@7&,,Pq= UBound|)pHx, 2(0t&P=c'L@f.O"MH a wr` sizQ"ZqP.now .'z M///G"??doesn't agree with ensг@ ' Re,W&posRPq&g&r('c mcZŷ1m tU"2D( ##P.owCu#q /4'qr` {xjc2 lsVAvQ pC o *alooT= oo vok`'1o=o'Mut J[ I4x Bwy(@azC- }X* BH k endIf j The(qa < 0  DF"Impossi@ Correlation Matrix"6hA Go`leav'baOIfp(Ia]Sqr($&Elsa( / A# haI;!4i6''$>.Cells(i`Tp(i@" ;A, + 퀫j!aa r'i !: $p'USAGE: Just like LINESTR now includes on`ly Y @F` the regs3,@butis can b@e easi changed by setting C.Valu,'fin a different tervalemember!!siVariaf`@ aa funcD$QA(yrR,4 xhcJ Boo`#`stats$ J'fiKlength 0of ys@ILongaEach C In0/S1 n!n N'5 h aloopm`l o0@ratYsCY (wids xJ ;x.ColumDnspuntprepare RTs YbXSm yY!5'requiruse V$myX)R'ReYZ %X#Pun3!% fir+rdXsad&w FRaE= x.lo(fi@;/!Xwi data;f3PP IQ 3:>YCI62Q1A0uq@vJxx(C YB)S7 'hack;@ if tr, 0k'UDNQsBA ApplicZ.vlF$8.LinEst(I@#, @ , 07,> 6qq0  0   0    >rU ( `a0$ `, `=y< `__SRP_a4_VBA_PROJECT/dir@s__SRP_014+++++H! ` !Aa&=y=y=y$I `Hq ` !Aa&=y=y=y$ `8 `////D `=y=y##nN"׭D#5JM^#i!\ dXH"52}G>o)ktLm 8bLerrorUformat_If(RandomSubwidth Excel+ VBAWin16~Win32MacVBA6# MCSimNVPstdole`MSFormsC RefEditOfficeu MainModule  _Evaluate (ShowMCNVForm MCNVForm5RefEditOneCell;ValueK ActiveCellAddress Application*ReferenceStyle9xlR1C1pMsgBoxRpromptmTitle~Buttons] vbCritical+}Show(AddMCSimNVMenuItem$ja MCSimNVIsHere CommandBars VisiblemyTools='OperatingSystem[ InternationalxlNonEnglishFunctionsInputBox ControlsKCount0vCaptionxnewItemICommandBarControlPAddrmsoControlButtonnOnActionresponseChrK~ vbInformationn(test4 ThisWorkbook|Workbook_AddinInstallWorkbook_AddinUninstallDeletex Sheet1 Sheet3CellValidStringTestCommandButton1_ClickI` FirstCelluFirstCellArray SecondCell$SecondCellArray>myrepsPctdone mystarttime yoursettingFxmeanxSD!xmaxxmin;x2mean-x2SD@\x2maxHx2minf ViewTableAllLong histRangeO&Range  myendTimew1t_w2u_ii]jj]kk]aXCZ MCRawRangeExmyOriginalSheetName$myCurrentMCSimSheetmyCurrentStringmyStartPosition* myEndPositionSParentexternalwRefEditTwoCell~ ActiveSheet%N CheckBox1 ActiveWindow+RangeSelection7~ TextBoxRepsKoNow% CalculationhxlCalculationManualScreenUpdating!  FrameProgressMCRawhmyMCRawVarCounteAreasKCellsSheets  continue3rmyMCRawSheetName\ CalculateFull| LabelProgress}WorksheetFunction&Min (BasicFour` WorkbooksBCopyƿBeforeSqr((Log107HDisplayGridlinesCharts ActiveChart  ChartType?xlXYScatterLinesNoMarkersvLocation-Where֍xlLocationAsObject SetSourceDataaSourceGShapes< ChartObjectsHColumnsp9Top5RowsUHeight|PlotArea SelectionZInterior; ColorIndexxlNoneLegendPAxesbxlValue@Aay  *\G{000204EF-0000-0000-C000-000000000046}#4.0#9#C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\VBE6.DLL#Visual Basic For Applications*\G{00020813-0000-0000-C000-000000000046}#1.5#0#C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXE#Microsoft Excel 11.0 Object LibraryH*\G{00020430-0000-0000-C000-000000000046}#2.0#0#\\CALEB\WWW\depart\economic\Metrics\EconometricsBook\Chapters\Ch01Introduction\ExcelFiles\StdOle2.tlb#OLE Automation*\G{0D452EE1-E08F-101A-852E-02608C4D0BB4}#2.0#0#C:\WINDOWS\System32\FM20.DLL#Microsoft Forms 2.0 Object Library*\G{AD148C92-33D8-4C6B-8047-F6F2D8564F50}#2.0#0#C:\DOCUME~1\user\LOCALS~1\Temp\VBE\MSForms.exd#Microsoft Forms 2.0 Object Library.E .`M *\G{00024517-0000-0000-C000-000000000046}#1.0#0#C:\Program Files\Microsoft Office\OFFICE11\REFEDIT.DLL#Ref Edit Control*\G{4BBE40E1-E3AC-470C-9126-F5DF4C88A89F}#1.0#0#C:\DOCUME~1\user\LOCALS~1\Temp\VBE\RefEdit.exd#Ref Edit ControlEF *\G{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}#2.3#0#C:\Program Files\Microsoft Office\Office\MSO9.DLL#Microsoft Office 9.0 Object Library   j1E(  ^ MainModule2B45d83184MainModuleH`9ThisWorkbook2F45d83611eThisWorkbook^ Sheet12245d83088m Sheet1^0- Sheet32345d83088o Sheet3ZH-MCNVForm2E45d831c4MCNVFormh]` Sheet22645d83088o Sheet2ZxHistogram2C45d831b0pHistogramG| RNGandSortModule2@45d83184F RNGandSortModuleZvSDModule2<45d83088SDModuleZ]"LinkCheckerModule2D45d831b0"LinkCheckerModule| Functions2A45d83184Functions L`0HxN(L{g$5E DDF?RD@s14APDiZ\sNgbi @;gSF8c`)RefEditOneCell_EnterSetFocus Sheet2 Histogram''(LimitsPminvalpmaxvalxx_oldy$yys_absminxrem$adjustymultoldwidthvaluesnoobsr histsheetnumber-binleft;~bincount!MaxȲmysheetb Worksheetxoylchoice{colHvalrangecvarnameYcNobinsjmincompgmaxcomptLargeyRangexRangeWleftCVtopC. precision statRangeAxupwxdown myboxRange (NewLimitsl(DriveFormatting TableRange7myWidthoomaxwidth(MakeBins ColumnWidthAutoFit&  tickwidthPlotBy$ xlColumnsh ChartArea HasLegendB ClearFormats MinimumScale MaximumScale=rowsubmcolsub `RowColumni ScrollRow" ScrollColumnLHvalues1uvalues2unoobs1rOnoobs2sOWidth1yWidth2yminval1rmaxval1minval2smaxval2 AllLongs1 AllLongs2binwidthYx1_Y1O_x2_Y2P_Nobins2Nobins1xx1xx2xxmin5maxbin/myname1myname2Varlabel<@ overallWidth overallxxSRisubwWrapText}nbsAxlThinw xlContinuous[ (FindPrecisionRound leaveloopcLogd(FormatSumStatsv NumberFormatR whichseriesIstartlwidth` lwidthrem_d[neg RNGandSortModule(myFMRG (myFMRGInt6& (myFMRGIntlag1  (myFMRGIntlag2!(ISETu(GSETIpg (auto_openV RndRBArraymyX(FMRG*Volatile(RandomNV"_(NormalRandomNVMean@SD_NormalRandomValueh (NormalRNGU(FMRGTestfrank starttimeLTimerX (FMRGgraphFNormRandYV TypeofRand47V1t_V2u_RadiusVFACDrepeattrepeat27(NormalRandomAdvb (NORMALRANDOM(Uniform?O (UniformRNG  UniformRand76(TestUniformRandv(UniformBetweenT(TDraweDF\TRand (TRNGmyzRandDmyyRand myyRandTmpt(ExponentialRNGOExponentialRand0lowert(TestExponentialRNG3? myTestVectorB(Expo{ (NormalRNGTestresultur(qsort#hlcrinemdkbpv^ (pivotValue (MyPartition/.Found+middleXx3_checkocheck2ptempe(CheckforSmallerlo ^hi](swap=tempR (sorttestingxNmyArray SDModuledata;sjep]Var(MeanSD(Exampleh"SDPop(Example2i LinkCheckerModule# myLinkStringaLinksActiveWorkbook LinkSourceskmyPosѰIsEmpty  Functions( CellNameο NameString( testnamestring ( GetFormula|fRngU FormulaLocalD ( BiVarNormal_3mean1wSD1&mean2xSD2'rhomatIJweights[ ResultArraycK NormArraySDvec=MeanvecFleavet BvarNormal( choldc( MultivarNormal/;nvars corrRangemb meansRange"SDsrangelcorrmatRmeansSDsHcorrrowncorrcolmeansdimSDsdim nomeanrowsnoSDrows nocorrrowsR  nocorrcolsu( CallTestMultivarNormal( TestMultiVarNormalѾEndnow( BivarNormalTest<sum( LINESTAbconJstatsmyYFirstRow3LinEstWorkbookkUserFormNCommandButton1|CommandButton2}Label3,Label4,Label5,Frame1Frame2 CheckBox3UserForm_ClickClickEnter(CheckSolverReference/Missing| VBProjectOh ReferencesIsBrokenԣRemoveϱ SOLVER vbTextCompare Q(AddSolverReference ErrorHandlerO FileSearchD NewSearchfLookInPPath2SearchSubFoldersFilenamejMatchTextExactlywFileTypemsoFileTypeOfficeFilesvExecuteY AddFromFileu FoundFilesSave _B_var_EndFsolverokC solversolveQ userfinishs_Defaultj_B_var_solverokr _B_var_prompt RunSolver7j _B_var_Format _B_var_Midp AddinInstallAddinUninstallItemz _B_var_LeftQ _B_var_aLinksg_B_var_I  (auto_closewGo q e mo $@`@ @`!H@P$HA @o0* pHdMCSimNVQ(@= l j1E( J< rstdole>stdoleP %*\G{00020430-C 0046}#2.0#0#\\CALEB\WWW\depart\economic\Metrics\E Book\Chapters01Introduction\ExcelFiles\StdOle2.tlb#OLE AutomaSSForms> MSFirms3hD452EE1-E08F-@101A-8-02608C4D0BB4hC:\WINDOWS\System32\FM20.DLL#MicrosPoft : Object Library/;D1AHR00}#0BR#G 50 AAD148C92-33D8-4C6B-8047-F6F2D8564F5@j6DOCUME~1\user\LOoS@Temp\ VBE\MS.exd ; .E .``M C1R@efEditgRQfEi@3 wH245167HP1H@0Program FdOffice\OFFICE11\REFEDITm! " Col?iž707o84BBE403AC-470C-9126-F5DF4C88A89F}#F44`J @EFC2!$#Of ic5"``a22DF8D04C-5BFAfB-BDE5@4AA4`2f}2C3MSO9ìh 9.0hE@ ^ MainM leGainM}du,26 H19"E,bH!"+ThisWorkb@G Th sWA rkbo52oqo^"e;S@heet1G Se@;t1¿2 - 3 3 3 3 WZ@ MCNVAG`MCNVfi2ro^=h]c(3,2-2)2-20R HistOGE&t%gr/m;j2ob|?G{.QRNGandSortU7 RGn]zSr@]8|5o t; v Z SD5 SD\A9!=]LinkChecXkerEA$Ln@@C8ce]ue%f!-|A#FTunbs%FSn`t oUs%f'%o?L? rU~~~~~~~~~~~i ringTestFirstCellCellStringTestSecondCellRefEditOneCell_EnterUserForm_Click FM ,This add-in will not work with columns as numbers. Please execute Tools: Options, click the General tab, and uncheck the R1C1 reference style option. 8Unacceptable Reference Style  S Name  RangeexternalAddressCҋB ``FF MCRaw Cells Valuecܥ *N(ONVefOj+CsHp\j&aM՗E{u Cu&7( I h}\3OTzzb You cannot have more than 65,535 repetitions when the Record All Selected Cells option is on because the results will not fit on the sheet. Please lower the Number of Repetitions below 65,535. Too Many Reps A1 SelectҋB `ƏYl|a |You have selected more than 256 cells to record. This cannot be accomodated on an Excel sheet. You may have inadvertently selected a large range of cells. Please correct this and try again. 0Too Many Cells to Record#=h8+3qG?,2qL'' 0%pj).` BasicFourMeanSDExampleExample2 OneVarMCSim MCSimNV.xlaFF BeforeCopy MCSimLimitsHistogramMaker1VarHistogramMaker2VarMakeBins FindPrecisionLog10FormatSumStatsDriveFormatting NewLimits  B2 AL1F aa1:ab3 EmpHist Chart 1 ShapesColumnsLeftChartObjectsRowsTop Height widthInteriorColorIndex Delete Border WeightLineStyleMajorTickMarkMinorTickMark "TickLabelPositionMajorGridlinesWorkbook_AddinUninstall "HasMajorGridlines "HasMinorGridlines EmK*y rU ~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~D .EJ     ) AY y1 yY MCSimNV MainModule ThisWorkbookSheet1Sheet3MCNVFormSheet2 HistogramRNGandSortModuleSDModuleLinkCheckerModule FunctionsF /C:\PROGRA~1\COMMON~1\MICROS~1\VBA\VBA6\VBE6.DLLVBA Q y0F4C:\Program Files\Microsoft Office\OFFICE11\EXCEL.EXEExcel Iq@0Fe\\CALEB\WWW\depart\economic\Metrics\EconometricsBook\Chapters\Ch01Introduction\ExcelFiles\StdOle2.tlbstdole aaP.E .`M C:\WINDOWS\System32\FM20.DLLMSForms A`3kLGVOP .C:\DOCUME~1\user\LOCALS~1\Temp\VBE\MSForms.exd ApEFTestMultiVarNormal6C:\Program Files\Microsoft Office\OFFICE11\REFEDIT.DLLRefEdit  @K G&L .C:\DOCUME~1\user\LOCALS~1\Temp\VBE\RefEdit.exd  L-[DR 1C:\Program Files\Microsoft Office\Office\MSO9.DLLOffice  q CellNametestnamestring GetFormulaFF BiVarNormalMultivarNormalCallTestMultivarNormalBivarNormalTestcholdcLINESTA  auto_openFMRGRandomRandomNVNormalRandomNVFMRGTest FMRGgraph NormalRNGNormalRandomAdv NORMALRANDOMUniform UniformRNGTestUniformRandUniformBetweenTDrawTRNGExponentialRNGTestExponentialRNGExpo NormalRNGTestqsort pivotValueCheckforSmaller MyPartitionswap sorttesting 4VBE6.DLL  R 8  Q X ShowMCNVFormAddMCSimNVMenuItemtestCheckSolverReferenceAddSolverReferenceFF HT>|B5).?Dݶt2N:S KgX%%@_E˽BҋB ` CheckBox3!-6S` iA RunSolverҋB `RefEditTwoCellN!mmJ|YBc Y?F`Frame1 CheckBox1 TextBoxReps FrameProgressȏ[qJ eUserFormuϿKK$8xr F3Dynamic  {lFWJOCommandButton1CommandButton2"-?@Label3 CheckBox2Label4Label5RefEditOneCell LabelProgressFrame2=[!J;lCommandButton1_ClickCommandButton2_ClickCellStpiricalxF TwoVarMCSim C2 I4 K4 AM1 aa1:ac3 :Existing Sheet Name Requested MCSim Number `Existing Sheet May Not be MCSim Sheet CompatiblefĤIx8< !$AM$2:$AM$o Please enter the number of the existing MCSim sheet to which the results of this simulation will be written. Simulation results on the existing MCSim sheet will be overwritten and lost. Click Cancel if you are unsure.DFWorkbookWorkbook_AddinInstall5 You have canceled writing output to an existing sheet. No changes to an existing sheet have been made. 8No Writing to Existing Sheet I5 Average I6 SD I7 Max I8 Min  K לP@Z:S @ The Average, SD, Max, and Min results are written in cells J5:J8, with labels in cells I5:I8. Cells I5:I8 of the chosen existing sheet do not contain this information so this may not be an MCSim sheet. Thus, no changes to the existing sheet have been made.X ZThe sheet name you entered does not exist. Please enter the name of the existing sheet on which you want to overwrite the MCSim results with the results of this simulation. MCSimNumber b3:c102 l5:l8 AH1:AM10000 !$AM$1, ,2)+ The existing sheet cannot be found. No changes to an existing sheet have been made. b3:b102 f3:f4 j5:j8 AH1:AL10000ActivateFormula $AL$2:$AL$ , =SERIES( !$AL$1, !$AK$2:$AK$ !$AL$2:$AL$ ,1) K15# Do you want to write the output to the existing MCRaw sheet called  ? &MCRaw Output Option   @  V `  f      x FX ZThe sheet name you entered does not exist. Please enter the name of the existing sheet on which you want to overwrite the MCRaw results with the results of this simulation. MCRawNumber LinkChecker I1  " '0c#zKxN=@`FzF~F Solver W2 F Solver*.*1 F C:\B Solver cannot be found. It must be installed from the Office CD in order for the Comparative Statics Wizard to function properly. _j9ɗXJr+aˌh=H2:0/ :Fatal Error: Solver Not Found M 8:Office:Excel Add-ins:Solver @:Office:Excel Add-ins:Solver.xla 4:Office:Add-ins:Solver.xla ,:Office:Add-ins:Solver Histogram of MinimumScaleMaximumScaleRow Column solver9 This machine seems to be neither Windows nor Mac. Macros that rely on Solver may have to be manually adjusted.  i 8FvAa\О Fcy;h@Nu>PDF WorksheetColumnWidth No. Obs.  to ClearFormats >The Solver reference cannot be found. You will have to manually add this reference. Launch the Visual Basic Editor from Excel, then execute Tools: References. Click the Browse button, navigate to the Solver add-in and open it. Make sure it is checked in the available references list.! ~Warning! This workbook is using formulas available only in the ? add-in. If you move the workbook, uninstall the add-in, or otherwise break the link, the formulas will show a #NAME error.  .  auto_closeFnJP'LKi8cS:__SRP_1VMCNVForm2#,2x2xfn<o"(wAddress I Rngmean1SD1mean2SD2rhonvars corrRange meansRangeSDsrangeanerrorpyxconstatsMeanSDNormRand TypeofRand UniformRandbDFTRandExponentialRandlrmx1x2x3middlecheckcheck2pvkij !  ( 8  H X ih xCellValidStringTest qdataMaxMinminvalmaxvalwidthxxvaluesnoobs histsheet histRange ViewTableAllLongnumbervalues1values2noobs1noobs2Width1Width2minval1maxval1minval2maxval2 AllLongs1 AllLongs2binleftbincountNobins precision statRangevalrange whichseriesxminstart 7 [Ic$  3}&R KQDBTimes New Roman8KEFҋB `EF, #FrameProgress!074CommandButton1O0 ;4CommandButton2; $#Frame1$#Frame2; ( 0pCheckBox2tto{S\ 1t0CheckBox1tto{Select cells on sheet, then execute Tools: MCSim( 3pCheckBox3tto{(ProceedqNqTahomad(CanceldENqTahomadHF{0y Output to Existing MCSim Sheet 5Times New RomanLF#{0y Record All Selected Cells (256 Max) 5Times New RomanHF{0y Run Solver with each Repetition 5Times New Roman<H   }&"Progress BarR KQDBTimes New Roman8 0 i02& n``,2x,2xf~o%'0CompObjp 20LabelProgress "1Tahoma n``Microsoft Forms 2.0 FrameEmbedded ObjectForms.Frame.19q8H  .}& RequiredR KQDBTimes New Roman (2HLabel3 L DTextBoxRepsoqEnter the number of repetitions(2i26$0* n``,2x,2xfJo)+$CompObjpXLabel5 ,.@RefEditOneCelln{ ( Select a cellq 5Times New Roman@H,{1000 5Times New Roman0(Enter the Number of Repetitions 5Times New RomanH${ 5Times New Roman n``Microsoft Forms 2.0 FrameEmbedded ObjectForms.Frame.19q8H  /}&OptionalR KQDBTimes New Roman` (2LLabel4 ,/@RefEditTwoCelln{$(Select a second cell  5Times New RomanH${ 5Times New Romani27. n``,2x,2xfo-/CompObjp n``Microsoft Forms 2.0 FrameEmbedded ObjectForms.Frame.19q Microsoft Forms 2.0 FormEmbedded Object9qVERSION 5.00 Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} MCNVForm Caption = "Monte Carlo Sim with Non Volatile RNG" ClientHeight = 5595 ClientLeft = 45 ClientTop = 345 ClientWidth = 3735 StartUCompObj,1aVBFrame@PROJECTwm4dPROJECTpPosition = 1 'CenterOwner TypeInfoVer = 131 End MainModuleMainModuleThisWorkbookThisWorkbookSheet1Sheet1MCNVFormMCNVFormSheet2Sheet2Sheet3Sheet3RNGandSortModuleRNGandSortModuleSDModuleSDModuleHistogramHistogramLinkCheckerModuleLinkCheckerModuleFunctionsFunctionsID="{AB8AEE19-FD9C-11D2-9CEA-20F807C10627}" Module=MainModule Document=ThisWorkbook/&H00000000 Document=Sheet1/&H00000000 Package={AC9F2F90-E877-11CE-9F68-00AA00574A4F} BaseClass=MCNVForm Document=Sheet2/&H00000000 Document=Sheet3/&H00000000 Module=RNGandSortModule Module=SDModule Module=Histogram Module=LinkCheckerModule Module=Functions HelpFile="" Name="MCSimNV" HelpContextID="0" VersionCompatible32="393222000" CMG="4D4F4B574F5D535D535D535D53" DPB="4F4D495149514A524A524A" GC="51535758585858A7" [Host Extender Info] &H00000001={3832D640-CF90-11CF-8E43-00A0C911005A};VBE;&H00000000 [Workspace] MainModule=88, 116, 900, 736, Z ThisWorkbook=0, 0, 0, 0, C Sheet1=0, 0, 0, 0, C MCNVForm=110, 145, 922, 765, C, 22, 29, 609, 691, C Sheet2=0, 0, 0, 0, C Sheet3=0, 0, 0, 0, C RNGandSortModule=0, 0, 0, 0, C SDModule=0, 0, 0, 0, C Histogram=0, 0, 0, 0, C LinkCheckerModule=0, 0, 0, 0, C Functions=0, 0, 0, 0, C EFOh+'0` hPROJECTlkSummaryInformation(6DocumentSummaryInformation8CompObjmp ( 4 @ LX(Monte Carlo Sim with Non Volatile RNGBarreto/Howland4Monte Carlo Sim with Non Volatile RNG 15 May 2006Humberto Barreto@b%13@AL@ "2x՜.+,0HP X`hp x G Doc OneVarMCSim TwoVarMCSim  Worksheets F!Microsoft Office Excel WorksheetBiff8Excel.Sheet.89q