ࡱ> wlv  !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuyz{|}~Root Entry FhN`NA}Workbook?Z_VBA_PROJECT_CUR"=%>NAVBA:>@A  Worksheet"&Tools&WindowChart"&Tools&Window(Visual Basic Module"&Tools&Window\pHumberto Barreto Ba= ThisWorkbook=+<n@"1.Times New Roman1.Times New Roman1.Times New Roman1.Times New Roman1.Times New Roman1. Times New Roman1.8Times New Roman1.Times New Roman1Arial1$Arial1. Times New Roman1.Times New Roman1 Arial1.Times New Roman1. Times New Roman1.Times New Roman1. Times New Roman1.Times New Roman1. Times New Roman1. Times New Roman1Calibri1BCalibri1 BCalibri1BCalibri1 BCalibri1 BCalibri1BCalibri1BCalibri1,>BCalibri1>BCalibri1>BCalibri1>BCalibri1 BCalibri1BCalibri1?BCalibri1h>BCambria1BCalibri1 BCalibri1. BTimes New Roman1hBCalibri"$"#,##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                                                                      ff + ) , *      P  P       ! ` "        #   $ % a8 & " !    *0  *8 (  ( " "  "   (     ! "    *8  *8 "        $@ @  ,@ @  @ @  (@ @  (!@ @  !@ @  $!@ @  !!@ @ (0   ,!@ @  (!!@ @  (  @  $@  (@ @  ,!@ @  ,!@ @  (!@ @  ,!!@ @  (@ @  ,!@ @  ,!@ @  (!@ @  ,!!@ @  (   (   (  (@  (  @  (   (   (  (@@   (  @   (@   (   (@   ( @       ,@ @   ,          '  "8"@@ "8"@ "8 " @ "8"@ "8 " @ *8@ @ *0!@ @ "8@@ "8  @ "8@@ "8  @ *8!@ @ ||7Nw}A}  \(#,##0ef-"??_);_}A}  \(#,##0ef-"??_);_}A}  \(#,##0ef-"??_);_}A}  \(#,##0ef-"??_);_}A}  \(#,##0ef-"??_);_}A}  \(#,##0ef -"??_);_}A}  \(#,##0L-"??_);_}A}  \(#,##0L-"??_);_}A}  \(#,##0L-"??_);_}A}  \(#,##0L-"??_);_}A}  \(#,##0L-"??_);_}A}  \(#,##0L -"??_);_}A}  \(#,##023-"??_);_}A}  \(#,##023-"??_);_}A}  \(#,##023-"??_);_}A}  \(#,##023-"??_);_}A}   \(#,##023-"??_);_}A}!  \(#,##023 -"??_);_}A}"  \(#,##0-"??_);_}A}#  \(#,##0-"??_);_}A}$  \(#,##0-"??_);_}A}%  \(#,##0-"??_);_}A}&  \(#,##0-"??_);_}A}'  \(#,##0 -"??_);_}A}(  \(#,##0-"??_);_}}) } \(#,##0-"??_);_   }}*  \(#,##0-"??_);_??? ??? ??? ???}-}/  \(#,##0}A}1 a \(#,##0-"??_);_}A}2  \(#,##0-"??_);_}A}3  \(#,##0?-"??_);_}A}4  \(#,##023-"??_);_}-}5  \(#,##0}}7 ??v \(#,##0̙-"??_);_   }A}8 } \(#,##0-"??_);_}A}9 e \(#,##0-"??_);_}x}< \(#,##0-"?  }}= ??? \(#,##0-"???? ???  ??? ???}-}?  \(#,##0}U}@  \(#,##0-"? }-}A  \(#,##0}(}  \(#,##0 20% - Accent1M 20% - Accent1 ef % 20% - Accent2M" 20% - Accent2 ef % 20% - Accent3M& 20% - Accent3 ef % 20% - Accent4M* 20% - Accent4 ef % 20% - Accent5M. 20% - Accent5 ef % 20% - Accent6M2 20% - Accent6  ef % 40% - Accent1M 40% - Accent1 L % 40% - Accent2M# 40% - Accent2 L湸 % 40% - Accent3M' 40% - Accent3 L % 40% - Accent4M+ 40% - Accent4 L % 40% - Accent5M/ 40% - Accent5 L % 40% - Accent6M3 40% - Accent6  Lմ % 60% - Accent1M 60% - Accent1 23 % 60% - Accent2M$ 60% - Accent2 23ٗ % 60% - Accent3M( 60% - Accent3 23֚ % 60% - Accent4M, 60% - Accent4 23 % 60% - Accent5M0 60% - Accent5 23 %! 60% - Accent6M4 60% - Accent6  23 % "Accent1AAccent1 O % #Accent2A!Accent2 PM % $Accent3A%Accent3 Y % %Accent4A)Accent4 d % &Accent5A-Accent5 K % 'Accent6A1Accent6  F %(Bad9Bad  %) Calculation Calculation  }% * Check Cell Check Cell  %????????? ???+ Comma,( Comma [0]-&Currency.. Currency [0]/Explanatory TextG5Explanatory Text %0 : Followed Hyperlink 1Good;Good  a%2 Heading 1G Heading 1 I}%O3 Heading 2G Heading 2 I}%?4 Heading 3G Heading 3 I}%235 Heading 49 Heading 4 I}%6( Hyperlink 7InputuInput ̙ ??v% 8 Linked CellK Linked Cell }% 9NeutralANeutral  e%"Normal:Normal_AutoCorr;Normal_MonteCarlo <Noteb Note   =OutputwOutput  ???%????????? ???>$Percent ?Title1Title I}% @TotalMTotal %OOA Warning Text? Warning Text %XTableStyleMedium9PivotStyleLight168 ``i̜̙3f3333f3ffff333ff333f33f33BBB\`NMCSim4s OneVarMCSim TwoVarMCSim MainModuleh,  3 A@@  WG Sample NumbersecsAverageSDMaxMin?Only the first 100 repetitions are displayed on this worksheet.Summary Statistics!Results of Monte Carlo SimulationSimulation Stats repetitionsWThis add-in permits Monte Carlo simulation of one or two cells from any Excel workbook.Var1Var2fIt also allows the user to easily switch from RAND to random(FMRG), the Barreto/Howland implementationof Deng and Lin's FMRG.secondsNotesCorrected Mac LanguageID bug.#Used latest Howland Histogram code.Revised Histogram code--FHOBeginning value for Histogram display still need some work to make scaling nice,2Var Histogram is same scale both variables.mI dragged the Histogram module from MCSimRevised.xla.xls (that Howland was using for testing) into MCSim.xla.'Resized form for better display on Mac.UI'm still not able to get a nice display on a Mac without making it look bad on a PC. Frustrating.&Recovered file from Howland's machine.This should be it!*I'll never say "This should be it!" again.Added error checking code.=Added Howland's latest tweak on the binwidth and starting binvFollowed up on Howland suggestion to trap and report an error when the user selects a cell with a formula, but it does&not vary as the sheet is recalculated.6Added ability to track up to 256 cells and 65000 reps.CCorrected problem with sheet recalculation for tracking many cells.4Added option to output results to an existing sheet.1Based on a request from user Andrew Biggs at SSA.%Corrected Histogram updating mistake.3Added option to out results to MCRaw existing sheetCorrected Symbol font formatted cells, added updated (Oct-2004) RNGandSortModule from Random.xls, and added warning message in cell I1 on use of functions available only from the add-in.ZFixed problem with simply copying sheet with a chart from the add-in to the the user book.Learned that =random() looks first in the workbook, then add-ins. Because chart uses the name of the sheet in the Series formula, that somehow triggers "full naming."XThe only way to access the random() function in the add-in is to use MCSim.xla!random().With this version, the MCSim sheets in the add-in do NOT have charts. The chart is added AFTER the sheet is copied to the user's book.fAlso changed code to check for no change in a cell up front instead of waiting after the code has run.CFixed problem with wrong color coding of chart in a 2 var histogram>Fixed bug with non US English versions. Thanks to John Talyor.(Added recalculate entire workbook option8Fixed sheetname problem when user deleted an MCSim sheetOAdded Functions module from Random.xls to include functions with correlated Xs.Added a feature that uses the cell name, if it is named, instead of the address, in the Monte Carlo output using the Function CellName(Address as String) eFixed bug with recalculate entire workbook option. You cannot put a checkbox control inside a frame. |Put in latest version of RNGandSortModule and cleaned up Log10 function (putting it in the Histogram code where it belongs)."Foreign language uninstall supportYAdded error trapping and reporting the #Errors in a cell during a Monte Carlo simulation.%Fixed small error in naming of sheets,Changed max rows and columns for Excel 2007.db Cells.Replace What:="RAND()", Replacement:="MCSIM.XLA!RANDOM()", LookAt:=xlPart, SearchOrder _$ :=xlByRows, MatchCase:=False$A$69#Errors%corrected msgbox on use of R1C1 style8added ability for user to give seed to replicate resultsused Application.Version < 12 to trap tracking of more than 256 cells for old Excel users and removed unnecessary warning for Excel 2007 and greater users.reorganized MCForm as suggested by user (Mark)Fadded a Delete Previous MCSim Sheet option as usggested by user (Mark)Sadopted Hide (versus Unload) to enable user to not have to input information agaainFixes and Upgrades History:&updated install message for Excel 2007J#> >?BA+BDnH J K ccB{mU7hgY H   e.[_*cfrg  dMbP?_*+%;&A Page &P&?'?(?)?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@"dXX??&U} @(Z}  e&&;&;&;&;&;&;&;&;&; &; &; &; &; &;&;;;;;;;;;;;;;; E~ Z@ %= Z $%~ @@$@@% Z)@$?@@% Z >~ @~  $?%~  ~ Z@@~   Z~   Z~ Z@ Z Z Z Z~ Z@ Z Z Z~ Z@ Z Z~ Z@ Z Z Z~ Z`@ Z <v$$6N ;";#;%;&;(;);*;,;-;/;0;2;3;5;6;7;8;9;:;<;=;?; Z!~ "Z@ #Z"~ %Z@ &Z#~ (Z@ )Z$ *Z%~ ,Z@ -Z&~ /Z@ 0Z'~ 2Z@ 3Z(~ 5Z@ 6Z) 7Z* 8Z+ 9Z, :Z-~ <Z@ =Z.~ ?Z@2@;A;B;C;D;E;F;H;I;K;L;N;O;Q;R;T;U;W;X;Z;[;];^;_; @Z/ AZ0 BZ1 CZ2 D: EZ; FZ<~ HZ@ IZ3~ KZ @ LZ4~ NZ @ OZ5~ QZ@ RZ6~ TZ@ UZ7~ WZ ;@ XZ8~ ZZQ@ [Z9~ ]Z @ ^? _A40`;a;b;c;d; `C a@ bB cD dFP (  n   c 4NEmpHist ] @ 4  PP PZaajk&ffffff?'ffffff?(?)?" 4333333?333333?3&<3PH@,0(  ,3d23   MR R R RI3~S S4 3Q:% $A$69Q ;%%Q ;$$Q3_ ff6 MM   dR R R R\ K"#S S4ES S 4D$% 073O&QR R40S S4$% 73O&Q4FAd 3OJ 3*?&! R RR R0u,S S43* &!  MR R08 bS S4523 M  MR Rbn]S S43d" 4S S4DJ % -$ 3O+&(Q (Histogram of $A$6923 M NMR RR Rt]S S4'<m"  xxxxxxxxxxxxxxxxxxKxS S4b K[S S 4eeeK >@bbA Xeee 7 Sheet1ggD   g3vBζ  dMbP?_*+%;&?'?(?)?MSymantec Fax Starter Edition@f@MSUDOLFAXDRVd"d??&U} J}  B}  D} D} E}  }  E} E}  @ E}  E}  D} @D} @ D}  D}  D} !* E} +1 D} 2 EEstimated rho in SUsgBBv BHH 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; CDDDDDDDD !DDD#$DD%&DDDDD*&2DDDDDDDDDDDDDDDDA FGDDd  IIIIIII(IDDDDDIIIIIIIII ~ !I "II#$II%>&IIIIIDDDDDDDIIIIIIIIIIIIIIIIA~ J?K O ` !D!?~ "D#D$D!?~ %D&DDDDD*&2DDDDDDDDDDDDDDDDA~ J@K Oe f      M!D!?~ "D@#D$D!?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J@K LL h i|u? N|w} M!D1?~ "D@#D$D!?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J@K _ ] 0n? g~u !D!?~ "D @#D$D!?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J@K ^ \>i@ Nvu !D@)?~ "D@#D$D1?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J@K a blSQ? [z{ !D1?~ "D"@#D$D1?~ %D @&DDDDD*&2DDDDDDDDDDDDDDDDA~ J@KLYLLLL !D9?~ "D"@#D$D!?~ %D @&DDDDD*&2DDDDDDDDDDDDDDDDA~ J @ K  OYLLLL  M !D!?~ "D(@ #D $D!?~ %D@ &DDDDD*& 2DDDDDDDDDDDDDDDDA~ J"@ K LYLLLL  M !D ?~ "D @ #D $D@)?~ %D@ &DDDDD*& 2DDDDDDDDDDDDDDDDA~ J$@ K LLLLLL  M !D@)?~ "D@ #D $D@)?~ %D"@ &DDDDD*& 2DDDDDDDDDDDDDDDDA~ J&@ K LLLLLL  !D`?~ "D@ #D $D1?~ %D"@ &DDDDD*& 2DDDDDDDDDDDDDDDDA~ J(@ K Q LLLLLL  !D1?~ "D@ #D $D1?~ %D"@ &DDDDD*& 2DDDDDDDDDDDDDDDDA~ J*@KQLLLLLL !D?~ "D@#D$D9?~ %D"@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J,@KQLLLLLL !D9?~ "D@#D$D9?~ %D(@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J.@KLLLLLL !D?~ "D@#D$D!?~ %D(@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J0@KLLLLLL !D!@~ "D@#D$D!?~ %D @&DDDDD*&2DDDDDDDDDDDDDDDDA~ J1@KLLLLLL !Dc@~ "D@#D$D ?~ %D @&DDDDD*&2DDDDDDDDDDDDDDDDA~ J2@KLLLLLL !D @~ "D@#D$D ?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J3@KLLLLLL !D0@~ "D@#D$D@)?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J4@KLLLLLL !D@)@~ "D?#D$D@)?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J5@K!DPk@~ "D#D$D`?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J6@K!D`@~ "D#D$D`?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J7@K!Dp@~ "D#D$D1?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J8@K!D1@~ "D#D$D1?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J9@K!Ds @~ "D#D$D?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J:@K!D @~ "D#D$D?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J;@K!D @~ "D#D$D9?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J<@K!D9 @~ "D#D$D9?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J=@K!D{ @~ "D#D$D?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDA~ J>@K!D@~ "D#D$D?~ %D@&DDDDD*&2DDDDDDDDDDDDDDDDADl 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;~ J?@ K !D@~ "D #D $D!@~ %D@ &DDDDD*& 2DDDDDDDDDDDDDDDDA~ !J@@!K !!DDD#!$D!@~ !%D@!&DDDDD*&!2DDDDDDDDDDDDDDDDA~ "J@@"K "!DDD#"$Dc@~ "%D@"&DDDDD*&"2DDDDDDDDDDDDDDDDA~ #JA@#K #!DDD##$Dc@~ #%D@#&DDDDD*&#2DDDDDDDDDDDDDDDDA~ $JA@$K $!DDD#$$D @~ $%D@$&DDDDD*&$2DDDDDDDDDDDDDDDDA~ %JB@%K %!DDD#%$D @~ %%D@%&DDDDD*&%2DDDDDDDDDDDDDDDDA~ &JB@&K &!DDD#&$D0@~ &%D@&&DDDDD*&&2DDDDDDDDDDDDDDDDA~ 'JC@'K '!DDD#'$D0@~ '%D?'&DDDDD*&'2DDDDDDDDDDDDDDDDA~ (JC@(K (!DDD#($D@)@~ (%D?(&DDDDD*&(2DDDDDDDDDDDDDDDDA~ )JD@)K )!DDD#)$D@)@~ )%D)&DDDDD*&)2DDDDDDDDDDDDDDDDA~ *JD@*K *!DDD#*$DPk@~ *%D*&DDDDD*&*2DDDDDDDDDDDDDDDDA~ +JE@+K +!DDD#+$DPk@~ +%D+&DDDDD*&+2DDDDDDDDDDDDDDDDA~ ,JE@,K ,!DDD#,$D`@~ ,%D,&DDDDD*&,2DDDDDDDDDDDDDDDDA~ -JF@-K -!DDD#-$D`@~ -%D-&DDDDD*&-2DDDDDDDDDDDDDDDDA~ .JF@.K .!DDD#.$Dp@~ .%D.&DDDDD*&.2DDDDDDDDDDDDDDDDA~ /JG@/K /!DDD#/$Dp@~ /%D/&DDDDD*&/2DDDDDDDDDDDDDDDDA~ 0JG@0K 0!DDD#0$D1@~ 0%D0&DDDDD*&02DDDDDDDDDDDDDDDDA~ 1JH@1K 1!DDD#1$D1@~ 1%D1&DDDDD*&12DDDDDDDDDDDDDDDDA~ 2JH@2K 2!DDD#2$Ds @~ 2%D2&DDDDD*&22DDDDDDDDDDDDDDDDA~ 3JI@3K 3!DDD#3$Ds @~ 3%D3&DDDDD*&32DDDDDDDDDDDDDDDDA~ 4JI@4K 4!DDD#4$D @~ 4%D4&DDDDD*&42DDDDDDDDDDDDDDDDA~ 5JJ@5K 5!DDD#5$D @~ 5%D5&DDDDD*&52DDDDDDDDDDDDDDDDA~ 6JJ@6K 6!DDD#6$D @~ 6%D6&DDDDD*&62DDDDDDDDDDDDDDDDA~ 7JK@7K 7!DDD#7$D @~ 7%D7&DDDDD*&72DDDDDDDDDDDDDDDDA~ 8JK@8K 8!DDD#8$D9 @~ 8%D8&DDDDD*&82DDDDDDDDDDDDDDDDA~ 9JL@9K 9!DDD#9$D9 @~ 9%D9&DDDDD*&92DDDDDDDDDDDDDDDDA~ :JL@:K :!DDD#:$D{ @~ :%D:&DDDDD*&:2DDDDDDDDDDDDDDDDA~ ;JM@;K ;!DDD#;$D{ @~ ;%D;&DDDDD*&;2DDDDDDDDDDDDDDDDA~ <JM@<K <!DDD#<$D@~ <%D<&DDDDD*&<2DDDDDDDDDDDDDDDDA~ =JN@=K =!DDD#=$D@~ =%D=&DDDDD*&=2DDDDDDDDDDDDDDDDA~ >JN@>K >!DDD#>$D@~ >%D>&DDDDD*&>2DDDDDDDDDDDDDDDDA~ ?JO@?K ?!DDD#?$D@~ ?%D?&DDDDD*&?2DDDDDDDDDDDDDDDDADZl@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;~ @JO@@K@!DDDDDDDDDD*&@2DDDDDDDDDDDDDDDDA~ AJP@AKA!DDDDDDDDDD*&A2DDDDDDDDDDDDDDDDA~ BJ@P@BKB!DDDDDDDDDD*&B2DDDDDDDDDDDDDDDDA~ CJP@CKC!DDDDDDDDDD*&C2DDDDDDDDDDDDDDDDA~ DJP@DKD!DDDDDDDDDD*&D2DDDDDDDDDDDDDDDDA~ EJQ@EKE!DDDDDDDDDD*&E2DDDDDDDDDDDDDDDDA~ FJ@Q@FKF!DDDDDDDDDD*&F2DDDDDDDDDDDDDDDDA~ GJQ@GKG!DDDDDDDDDD*&G2DDDDDDDDDDDDDDDDA~ HJQ@HKH!DDDDDDDDDD*&H2DDDDDDDDDDDDDDDDA~ IJR@IKI!DDDDDDDDDD*&I2DDDDDDDDDDDDDDDDA~ JJ@R@JKJ!DDDDDDDDDD*&J2DDDDDDDDDDDDDDDDA~ KJR@KKK!DDDDDDDDDD*&K2DDDDDDDDDDDDDDDDA~ LJR@LKL!DDDDDDDDDD*&L2DDDDDDDDDDDDDDDDA~ MJS@MKM!DDDDDDDDDD*&M2DDDDDDDDDDDDDDDDA~ NJ@S@NKN!DDDDDDDDDD*&N2DDDDDDDDDDDDDDDDA~ OJS@OKO!DDDDDDDDDD*&O2DDDDDDDDDDDDDDDDA~ PJS@PKP!DDDDDDDDDD*&P2DDDDDDDDDDDDDDDDA~ QJT@QKQ!DDDDDDDDDD*&Q2DDDDDDDDDDDDDDDDA~ RJ@T@RKR!DDDDDDDDDD*&R2DDDDDDDDDDDDDDDDA~ SJT@SKS!DDDDDDDDDD*&S2DDDDDDDDDDDDDDDDA~ TJT@TKT!DDDDDDDDDD*&T2DDDDDDDDDDDDDDDDA~ UJU@UKU!DDDDDDDDDD*&U2DDDDDDDDDDDDDDDDA~ VJ@U@VKV!DDDDDDDDDD*&V2DDDDDDDDDDDDDDDDA~ WJU@WKW!DDDDDDDDDD*&W2DDDDDDDDDDDDDDDDA~ XJU@XKX!DDDDDDDDDD*&X2DDDDDDDDDDDDDDDDA~ YJV@YKY!DDDDDDDDDD*&Y2DDDDDDDDDDDDDDDDA~ ZJ@V@ZKZ!DDDDDDDDDD*&Z2DDDDDDDDDDDDDDDDA~ [JV@[K[!DDDDDDDDDD*&[2DDDDDDDDDDDDDDDDA~ \JV@\K\!DDDDDDDDDD*&\2DDDDDDDDDDDDDDDDA~ ]JW@]K]!DDDDDDDDDD*&]2DDDDDDDDDDDDDDDDA~ ^J@W@^K^!DDDDDDDDDD*&^2DDDDDDDDDDDDDDDDA~ _JW@_K_!DDDDDDDDDD*&_2DDDDDDDDDDDDDDDDADl````````````````````````````````B;aB;bB;cB;dB;eB;fB;~ `JW@`K`!DDDDDDDDDD*&`2DDDDDDDDDDDDDDDDA~ aJX@aKa!DDDDDDDDDD*&a2DDDDDDDDDDDDDDDDA~ bJ@X@bKb!DDDDDDDDDD*&b2DDDDDDDDDDDDDDDDA~ cJX@cKc!DDDDDDDDDD*&c2DDDDDDDDDDDDDDDDA~ dJX@dKd!DDDDDDDDDD*&d2DDDDDDDDDDDDDDDDA~ eJY@eKe!DDDDDDDDDD*&e2DDDDDDDDDDDDDDDDA fRx``````P>@DB   7 Sheet2ggD   g  dMbP?_*+%;&?'?(?)?"d??&U} J}  S}  T} D} E}  }  E} E}  E}  E}  D} @D} @ D} 5 D} 6 EColumn LgBBv 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; CDDDDDDDD!'6DDDDDDDDDDDDA F U V Dd  IIIIIIIjIDDDDDIIDDDDDDDDDDDDDDDDDDDDDDDIIIIIIIIIIIIA~ J? WX O ` !'6DDDDDDDDDDDDA~ J@ WX Oe c       M!'6DDDDDDDDDDDDA~ J@ WX LL j kLo?  o pr?}k?  wx M!'6DDDDDDDDDDDDA~ J@ WX j l ??  o qh ?  ty !'6DDDDDDDDDDDDA~ J@ WX j k*?  o pV?  vu !'6DDDDDDDDDDDDA~ J@ WX m n{n  r s ݿ  z{ !'6DDDDDDDDDDDDA~ J@ WXLYLLLL !'6DDDDDDDDDDDDA~ J @ WX  OYLLLL  M !' 6DDDDDDDDDDDDA~ J"@ WX LYLLLL  M !' 6DDDDDDDDDDDDA~ J$@ WX LLLLLL  M !' 6DDDDDDDDDDDDA~ J&@ WX LLLLLL  !' 6DDDDDDDDDDDDA~ J(@ WX Q LLLLLL  !' 6DDDDDDDDDDDDA~ J*@ WXQLLLLLL !'6DDDDDDDDDDDDA~ J,@ WXQLLLLLL !'6DDDDDDDDDDDDA~ J.@ WXLLLLLL !'6DDDDDDDDDDDDA~ J0@ WXLLLLLL !'6DDDDDDDDDDDDA~ J1@ WX~ ?LLLLLL !'6DDDDDDDDDDDDA~ J2@ WXLLLLLL !'6DDDDDDDDDDDDA~ J3@ WXLLLLLL !'6DDDDDDDDDDDDA~ J4@ WXLLLLLL !'6DDDDDDDDDDDDA~ J5@ WX!'6DDDDDDDDDDDDA~ J6@ WXP!'6DDDDDDDDDDDDA~ J7@ WX!'6DDDDDDDDDDDDA~ J8@ WX!'6DDDDDDDDDDDDA~ J9@ WX!'6DDDDDDDDDDDDA~ J:@ WX!'6DDDDDDDDDDDDA~ J;@ WX!'6DDDDDDDDDDDDA~ J<@ WX!'6DDDDDDDDDDDDA~ J=@ WX!'6DDDDDDDDDDDDA~ J>@ WX!'6DDDDDDDDDDDDAD`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;~ J?@ WX !' 6DDDDDDDDDDDDA~ !J@@ !WX!!'!6DDDDDDDDDDDDA~ "J@@ "WX"!'"6DDDDDDDDDDDDA~ #JA@ #WX#!'#6DDDDDDDDDDDDA~ $JA@ $WX$!'$6DDDDDDDDDDDDA~ %JB@ %WX%!'%6DDDDDDDDDDDDA~ &JB@ &WX&!'&6DDDDDDDDDDDDA~ 'JC@ 'WX'!''6DDDDDDDDDDDDA~ (JC@ (WX(!'(6DDDDDDDDDDDDA~ )JD@ )WX)!')6DDDDDDDDDDDDA~ *JD@ *WX*!'*6DDDDDDDDDDDDA~ +JE@ +WX+!'+6DDDDDDDDDDDDA~ ,JE@ ,WX,!',6DDDDDDDDDDDDA~ -JF@ -WX-P-!'-6DDDDDDDDDDDDA~ .JF@ .WX.!'.6DDDDDDDDDDDDA~ /JG@ /WX/!'/6DDDDDDDDDDDDA~ 0JG@ 0WX0!'06DDDDDDDDDDDDA~ 1JH@ 1WX1!'16DDDDDDDDDDDDA~ 2JH@ 2WX2!'26DDDDDDDDDDDDA~ 3JI@ 3WX3!'36DDDDDDDDDDDDA~ 4JI@ 4WX4!'46DDDDDDDDDDDDA~ 5JJ@ 5WX5!'56DDDDDDDDDDDDA~ 6JJ@ 6WX6!'66DDDDDDDDDDDDA~ 7JK@ 7WX7!'76DDDDDDDDDDDDA~ 8JK@ 8WX8!'86DDDDDDDDDDDDA~ 9JL@ 9WX9!'96DDDDDDDDDDDDA~ :JL@ :WX:!':6DDDDDDDDDDDDA~ ;JM@ ;WX;!';6DDDDDDDDDDDDA~ <JM@ <WX<!'<6DDDDDDDDDDDDA~ =JN@ =WX=!'=6DDDDDDDDDDDDA~ >JN@ >WX>!'>6DDDDDDDDDDDDA~ ?JO@ ?WX?!'?6DDDDDDDDDDDDADJ 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;~ @JO@ @WX@!'@6DDDDDDDDDDDDA~ AJP@ AWXA!'A6DDDDDDDDDDDDA~ BJ@P@ BWXB!'B6DDDDDDDDDDDDA~ CJP@ CWXC6DDDDDDDDDDDDA~ DJP@ DWXD6DDDDDDDDDDDDA~ EJQ@ EWXE6DDDDDDDDDDDDA~ FJ@Q@ FWXF6DDDDDDDDDDDDA~ GJQ@ GWXG6DDDDDDDDDDDDA~ HJQ@ HWXH6DDDDDDDDDDDDA~ IJR@ IWXI6DDDDDDDDDDDDA~ JJ@R@ JWXJ6DDDDDDDDDDDDA~ KJR@ KWXK6DDDDDDDDDDDDA~ LJR@ LWXL6DDDDDDDDDDDDA~ MJS@ MWXM6DDDDDDDDDDDDA~ NJ@S@ NWXN6DDDDDDDDDDDDA~ OJS@ OWXO6DDDDDDDDDDDDA~ PJS@ PWXP6DDDDDDDDDDDDA~ QJT@ QWXQ6DDDDDDDDDDDDA~ RJ@T@ RWXR6DDDDDDDDDDDDA~ SJT@ SWXS6DDDDDDDDDDDDA~ TJT@ TWXT6DDDDDDDDDDDDA~ UJU@ UWXU6DDDDDDDDDDDDA~ VJ@U@ VWXV6DDDDDDDDDDDDA~ WJU@ WWXW6DDDDDDDDDDDDA~ XJU@ XWXX6DDDDDDDDDDDDA~ YJV@ YWXY6DDDDDDDDDDDDA~ ZJ@V@ ZWXZ6DDDDDDDDDDDDA~ [JV@ [WX[6DDDDDDDDDDDDA~ \JV@ \WX\6DDDDDDDDDDDDA~ ]JW@ ]WX]6DDDDDDDDDDDDA~ ^J@W@ ^WX^6DDDDDDDDDDDDA~ _JW@ _WX_6DDDDDDDDDDDDAD lVVV>>>>>>>>>>>>>>>>>>>>>>>>>>>>`B;aB;bB;cB;dB;eB;fB;~ `JW@ `WX`6DDDDDDDDDDDDA~ aJX@ aWXa6DDDDDDDDDDDDA~ bJ@X@ bWXb6DDDDDDDDDDDDA~ cJX@ cWXc6DDDDDDDDDDDDA~ dJX@ dWXd6DDDDDDDDDDDDA~ eJY@ eWXe6DDDDDDDDDDDDA fRx>>>>>>P>@DB"    7 Sheet3ggD &A Page &P&ffffff?'ffffff?(?)?"P??&>  MainModule+__SRP_4.__SRP_5 ThisWorkbook!!   "#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUWXYZ[\]_`bcdefghijklmnoprstuvwxyz{|}~umber formulas Written by Humberto BarretoQ for Barreto/Howland, Introductory Econometrics, Cambridge University Press, 20067 Idea behind the Progress Bar is due to John Walkenbach3 http://support.microsoft.com in the Knowledge Basep( FMRG references in the RNGandSortModulep% All other ideas from Barreto/Howland` Contact Humberto Barreto Dept of Economics DePauw University Greencastle, IN 46135 E -mail: hbarreto@depauw.edu phone: (765) 658-4531- Run the MCSim add-in by executing this macro2 It will bring up a form which enables the user to input necessary information5ז+in case the user is in a graph or something A1 style warning ! You will not be able to click on a cell to select it with columns are numbers. Please change the workbook to the A1 reference style or type in the cell address in the $A$1 pattern.$Reference Style Warning& *(A@"k8 load form with where the user is 2!4 ,!.(0HH ,B@6o) ****************************************. This macro adds a menu item so the ShowMCFormTp 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 userPADDdThe 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'BPADDkdTools'BDDk BWorksheet Menu Bar$>%L(@XPAD+ Check to see if there is a MCSim menu itemNGPADD'< : BWorksheet Menu Bar$>%L!L!NXPAD : BWorksheet Menu Bar$>%L%L!P MCSim ...DDAMeans the MCSim menu item is already there, make MCSimIsHere True'<dH5Means the MCSim menu item is not there, so do nothingXPADk :NGPADD+ Add the MCSim menu item if it is not thereGXXPAD <GXXPAD] Xl BWorksheet Menu Bar$>%L!L%V.RDD R ShowMCForm9ZNGPADD MCSim ...9PGXXPADq k* Message to the user when add-in is loadedAYou have successfully loaded the Monte Carlo Simulation add-in. You must have at least one cell that has a random number function in its formula. You provide one or two cells and the add-in simply recalculates the workbook for as many repetitions as you indicate and outputs the results in a new sheet in your workbook. $^ $^=Click on MCSim under the Add-ins tab (or, in older versions,  B: MCSim ...) to begin. ` Monte Carlo Simulation Available$"'\DDo MCSim ...ToolsWorksheet Menu Bar$>%L%L!ZA@"NGPADDo( !f  #Welcome to Microsoft Excel version !f running on  !D!A@"koAttribute VB_Name = "MainModule" ' DOCUMENTATION@This add-in doe MonCarlo simulation of onex?Ad  !^HxMET@(LT6 LLLLLL"LHX^`"@p @` @\ @T @D @4 @p$ @0 @H @` @x @ @0 @ @ @ @ (`#X`%@)@ @ @| @l @\ @L @< @\, @ H0 *"2,@x @t @`d @xT @D @4 @$ @ @ @ @ (hOHK$*\Rffff*1S4ba89cb0*\R5*#50*\R1*#337*\R1*#10d*\R5*#8c*\R5*#4d*\R5*#4e*\R1*#fc*\R0*#f*\R1*#6d*\R5*#4f*\R5*#50*\R5*#30*\R0*#1*\R0*#14*\R1*#4b*\R1*#220$*\Rffff*1U4ba89d1d*\G{62EFE21E-9124-4BB6-B907-E43BA1F600A6}#1.2#0#..\Forms\RefEdit.exd#Ref Edit Control*#0#v  D ` L  B( &p   X >  :` . ,   ( @ X x $   4 8 "@"h6p /3 &C  0  4P & "  *"2 @XK` ; D$R@ H P ` ,Sh 2  <n Fs H`   <   2  H X >v`        0   " J p " xh  DOCUMENTATION> This add-in does a Monte Carlo simulation of one or two cells from ANY Excel workbook INITIAL DATE: 5 Jan 2004 CURRENT DATE: 27 May 2009F To see complete documentation, including all revisions, see Doc sheet User Input; It is assumed that the user has a workbook with some cells that use random n or two cellsfrom ANY Ex workbookINITIAL DATE: 5 Jan 2004CURR27 Ma*y9Txee compledocument, including all@ reviss, sDoc `sheetx&User InpuA' It xss(d thate uha` with sov rand{numb.8for8Written byHto Barreto /Howl&,Qtrctory` Econ<ckCambridge University Pr,es 6Idea behin6eog=due !John Walkenbach#http://support.microsoft.@`@[)BHKn*e BaseA FMRG@_ferenc@@the RNG3SC Aior i)s DE@1CacjQN0DeptAGicDePauwFGre%astleVN 461 35E -mail: hbe@dep.eduph@: (765) 658`-4531(RunBDMCSimby executtma@QsAwi@pup a  which enab^l@ONBi $neVsain  Sub ShowMCF@() On E$rr`sRe![ Next ' 2c5b @_`a gr8aphAy[ngA1 styl@e warn If ApplDic.Re=S= xlR1C1 Then  MsgBox ppt:="Youhnot-`S aPc` k a"Asele cstocoluDmnrre ls@. Plechanaa&u@[rAype?%Yy6bc!$A $1 pavrn.", TiF5& CW## Buttons:=v bC~@$l E @oIf`)load3K= FalaAMake ,suDTs s Àk Comm s("6W@" ").V`@Tru@ check aguag@mvy# @S`dLeft( eOpeDraASys, 1) "W"Ded'TXks^±TalyormIrn onal(xlNonEnglishFun DO) <> ApE 'it's A8, tPE03gepp4+ qC&> uy?("@6ZPixnstz [d1 appeapo!B US.>?@vB BxӆLusWQ 0E]ns aXsitop# #caET[a SpanC(ramis A"GOOLSMA", 2YuAEo "5E4+! +rrols(/,1C2,`rsif=2`a 520*El7&6j+@^o _ _ nPY u .Counp}1$ 3j).CapRAqrx...5!1'xMead 2l ready#, ~mqDb$s@=7oMHˡ.rsoж8_Кw{jA ' T  i)& RA1LnewAYqWWdcSp.=Tu(%.P (Tx:=msoTs11WqrW.Onp=wiPM5!!1' 5|ss]X"bznϑqAѰ2|edx.p@WՓ(ha v1hcfully #e 63H!must R!@rN\4`qit@wS$ v0; /ppr Dp recalc V-Q syоpe ti7@s yo)@nderoutpsul` S `rP;& Chr(13d) "C5"- xundT`$Pa b (orb[ol, "j& ":$40!)begivbIϗ`AvQ" +ޗ1testqMML"`0M"r!]",)/Zber() If Application.Vers8 = 12 Then MsgBox "Welcome to Microsoft Excel vj" & 0_ & " runnin0g on -OperngSystemR"!" End If Sub rU~| !)Y1a9 !I !A $!!( y$ K/t x$ x ltxi3'':DN44:dNTT 6T4Cx$ (xaT`F4%$  ,)x6T4$$ $xT48pv2\:`%p$ dp H\ )p\KT p$ p t>TFD4  4:`]3p6D4%;D `"%p$ p Dc]@p5D CX RD (%'%'%'%'%: %:` % p$ p D`1Xp5D/ CXSlXF%\:`%p$ dp H\  ,  )p\pvnxlXF%\:`%p$ dp H\  , WDA4")p\6D4dsx@lXF%\:`%p$ dp H\  , DA4:]3)p\6D4vpvx xf kv0v%lXF%\:`%p$ dp H\  , W4$)p\64$ l    c D  $ '<'L:N\\@:`D4$:lXF|:l^i,6D4$|l\L<`4,XHTp\D4$|l\L<0  0 (  \rU $`$`$`$`n8 ^^#<DtbJ6PF 4@ځ~x 4@ځ~DtbJ6PMEPSLSS6" <<<N0{00020819-0000-0000-C000-000000000046} j@`8|k l``B%@ %h0 %8@@OHK$*\Rffff*1T4ba89cb0$*\Rffff*1S4ba89cb04" "0 8 " @ 8H    DX$` D  Put the MCSim menu item inA@8o@@1 Delete the new menu command from the Tools menu.xcheck language] !D$W8Thanks 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'Bin tkpdhTools'B FkP MCSim ... BWorksheet Menu Bar %>%L%LB@nHowlo@Attribute VB_Name = "ThisWorkbook" Bas0{00020P819-0C$0046} |GlobalSpacFalse dCreatablPredeclaIdTru BExposeTemplateDerivBustomizD2Sub _AddinInstall() ' Put the MCSim menu it em inqdd M I ` End ) " -Uni.Dele0new/ comma"from=Tools! . On Error ResuNext Xcheck languagQ D-yAs Sng If Left(Application .Oper@ngSys3, 1)AW" The;'Thanks to John Talyor@Internal(xlNonEnglishFuncAs) <> _DA5m)A<@'it's not , trygeewor8d fB8Lus:e&EB K*putBox("8L of]isxB2Excel doe$appe$arA#be(US. PleeAe )tha1Busing.onB mainC`acrosZtop$e screen. F"LL L LLL LL LL L*L <<<<<<<<<<<0{45A79246-C377-40A7-A2C8-10E37DC233A3}{DF0F0145-963C-45E6-A140-A0BF86360149} %% v`Ho`zx`|tx ~p$`l to h `pdsh, ``for `P `(@ `8`0`(` |`0`X``X` ` x%` ```:```H @% p`% %``0``H``ll b` ena` i` mati`@` s in` g`hing (\ a c with co`Zhang`$`Fe A1`(`4type` `:xStyl ` orm ` is`ZHH H`|k `|o`xrun ``sure( %P( %jt4 @XH,,0HH0h ` x   x X t,.*"@ @ @ @ @ p @8 ` @ P @ @ @ 0 @ H r@  @ @( @ @  @ P @ @@ @X @p p @p`   "*,@ T @ PO @0 L H  @ HO X 0 h 0 @ @ x @ < @8  @ 0 0 @@ ,O X  @h ( @ @$O h h  @  @  @( @@PO h(@x P@ O xx( @  @  8 @H `  @ pH H 24>BFHLR@ @ @ @8 (P#@` x8:@ C`:`<h@ NXnhtz@` i@`%0i@@ `Xm@h ` @tċS@ `hx`%|@ @ %(@8 P @`O x8h@ `_ B@O @8@@  @0 H@X p0@ XX@ @ %0@( @@| P(x(@l `!@h`!@d `% @` `,@(\ `pH0X@`H%(4h`8@H C@8 @( @ @ @` @ (@0 %@H @ @x @ a)I@ @| @l @\ @T 0<%@@P X@hL @@H 1!xhChPhP,."t@t @d @T @P @@ %@X0 @( %@ @@ x@p @ , "@ @ @ @ @ @ @| XX( OHK$*\Rffff*1U4ba89d1d*\R1*#220*\G{62EFE21E-9124-4BB6-B907-E43BA1F600A6}#1*#11*\R1*#2bb*\R1*#1d*\R1*#2d*\R1*#335*\R1*#29e*\R1*#248*\R0*#1a*\R0*#19$*\Rffff*1Y4ba89d328< @ DH LPT X\`dhlptx| %  "  (08@HPX`hP (08@HPX`hpx   "  Q 08 Th"V &M3(  + " H P `x"V&M3 p0 0 & (0 8/X2p8D x  //2,wX8 0  @  P X ` h &p  % (; J+ ( 0 8  @ H J+X         "  .@ P  %h x  " * - JU 8 *@ -p PW   / ` PW *Q2  )H X    @HP @X-'  Xf (0 &8!`!x     Yf   7( J8 8 >/,q0 (`    , "ZR &h " > !! ,j  P ` p     $   -8<P2*f     8Q@ `&D  24[ (-@&X!!          .-@Xp0d   ** ^0 28n  /(,qH (x    ""R " "@ .P- "0ttR "P "`p x/X9 $P"G8 ` ;h;;;      @$4Hp tx d,X ,p 0z   (  7! ! (!.@!4N`!&1! !2o!2o"8x@"8xx"2"0"2" !">" *## # (#0#8#@#H#P#HX#2p#7####7######;#$ .v$ H$ <`$ $ $$ $-$<%H%2P%`%*fh%*f%  %%  8%Q(& H&&Dh&  2&4[&  ('-('&@'!h'!'  '  ' ' ''  ' .'-((@(X(0dp( ( (( *(*(*) ^8) 2)8n) * */(*,qH* (x* ***  * "*"R* "+ "@+ .P+-+ "+0t+tR+ "P, "`,p, x,/,X,9,, ,$P-"G8-"G`-  - ;-;-;-;- ;-;.;(.;@. X. x. . . P/ (/ 0(0 10z1 @1P1p1$4x11 111$412 2,(2,@2,X2,p2,2,2 (2 72 2 3.34N83&1p3 32o32o38x48xP4240424 !4>4 *444 5555 5(5H052H57X5p5x557555555;55 N5 @6 <6 7 " 7-H7 `7,Oh7 838@9H9P9X9p919D30:x;; .;;/; $;O; <3<====/= $=3=P>X> `>  p> @><><?HP? ,? "E?<?N@X@.p@ "B@@/HA`A @hA<A<A<(BHhB ,B"EB<CN CpC.C "BCC`D/DE NE "`E2pE "E BE-EE|CE xF .F/F $FOF G3GHHHH/H $H3HHIPI XI hII0aI I I "I "J2 J "0J/@J PJpJ &xJJ JJ &J J/K&J K;HK `K1xK &K %K 0K K+L-(L@L HLhL pL L L LLL L "M  M38M(N0N8N $@NPNv`NN NNN "N O O 0O@O "HOPOXO"V`O $O`QO OPP PP Pf(P P" PPP"P P`PHQPQXQ`QhQpQfxQ Q  Rx] Xx`]X]p]]]]](]@]X]px]]0]H]`]] ; These are the proposed Widths of the bins in the histogram]]]]](]H]h]]]]]]Get user inputted data First cellmake sure it's a valid cellA@ t Unload MCForm|k ,!.!0$!!'*Get exact location of the cell as a stringNThis string can be used in a Range Object with all usual Properties accessible ,!.!0$%4'|User error check |$!=  aThe selected cell does not contain a formula so the cell value will not change. Please try again.$ *( Fatal Error&A@" Unload MCForm|(k Second cell ,!!0"ɶ'dmake sure it's a valid cell(jYA@ t`  Unload MCForm|kx ,!!0$%4' $!=  hThe selected second cell does not contain a formula so the cell value will not change. Please try again.$ *( Fatal Error&A@" Unload MCForm|pkh] ,!!0$'k@get names of cells]0 ,!.!0$'check to make sure cell changed]H]`]x  it's a OneVar |$!0' $B@  |$!0The cell you selected, |, did not change when the sheet was recalculated. This cell may not be appropriate for a Monte Carlo analysis. Do you want to proceed? Cell Doesn't Change Warning$"'   ,A@r|`kXkPd it's a TwoVar |$!0' $!0' $B@  |$!0  $!0One of the cells you selected, | or  , did not change when the sheet was recalculated. That cell may not be appropriate for a Monte Carlo analysis. Do you want to proceed? Cell Doesn't Change Warning$"'   ,A@r|kkkNumber of repetitions -- check] ,!!0'   $?The number of repetitions must be an integer greater than zero.A@"|k] '  ?The number of repetitions must be an integer greater than zero.A@"|PkH All is well ' ~ Track the Time elapsed ' Get the calculation setting !' Set to manual  ( Turn off screen updating ($ Change RANDOM for RAND if requested ,!RANDOM()RAND()   !B@k$ Change RAND for RANDOM if requested ,! RAND()MCSIM.XLA!RANDOM()   B@k(Set seed if requested ,! !0#g'$First value in BArray() in FMRG code ,! !0''force use of RANDOMRAND()MCSIM.XLA!RANDOM()   B@k( Show the Progress Bar ,!(@9 general variables not used in this histogram application'':handle user's request for more tracked cells, called MCRaw ,!!0 !.check for too many reps  !N !f You cannot have more than !N 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& *(A@"|kdeclare array for results]] Get number of var to be tracked  !   !  '    "!N !f You have selected more than "!N 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 Record& *(A@" A@r|k add 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 number8I added a loop to ensure the user would get a valid name]0'$&  !NMCRaw !N $  $!!name is taken can't use that name $ '$&` kX P %check to see if one more is availablename wasn't taken so use itMCRaw !N $  (<2+1 to make the number match the MCSim sheet numberget the sheetname for later use]H !'(7put down cell addresses in first row as labels for data'z  !   ! %4 z ($%(0 z 'z   size array  kH  Run the Monte CarloDetermine if a One or Two Var  it's a OneVar z  ,!*!0A@0this is actually faster if the workbook is smalldh $B@kP ,!.!0 $%!0 z+~ Track Progress z '2 Update the Caption property of the Frame control. 0%$ ,!(P Widen the Label control.  ,!!  ,!,(, The DoEvents allows the UserForm to update. z 2 !.%0 GA@j"load array if user requested MCRaw ,!!0'initialize for next rep  !   !  ' !0 z +  k z(put down results if user requested MCRaw ,!!0   :   :$  : ($%(0 : k #Get Summary stats and put them down ~    A@2WBasicFour(data() As Double, MEAN As Double, SD As Double, MAX As Double, MIN As Double)+Copy the MCSim sheet to the user's workbook 8 OneVarMCSim MCSim.xla$4%B@6name MCSim sheet]':<  !NMCSim !N :  $!!name is taken can't use that name : ':<Hk@8name wasn't taken so use itMCSim !N :  (get current MCSim sheet name !'(delete previous MCSim sheet if requested ,!>!0 (@MCSim !N :   B%B@nMCSim !N :  (ILdon't need -1 b/c deleting sheet in previous step lowers Sheets.Count by one !' (@kput down seed if used ,! !0 ,! !0$(0Seed$(0kPut down first 100 z d !.%D z$~ z $(0  $(0  $(0  $(0  $(0Make histogram**** Width histrange ^    $F'd    $H'knHistogramMaker FirstCellArray, myReps, 31, xmean, 0, 0, 0, ActiveSheet, ActiveSheet.ChartObjects("EmpHist"), 1^(values, noobs, Nobins, center, manual, minval, maxval, histsheet, histogram, whichhistonsheet B2$(0 AL1$(0 %P( %$.histsheet.Activate (J"make a chart on the existing sheet LB@V R N(P add a series XV  NB@Taa1:ab3 %\ NB@ZC one seriesEmpHistChart 1 %^(size it $%"!EmpHist %`( $%!bEmpHist %`(b $%!d EmpHist %`(d $%"!EmpHist %`( N!fB@H n h!j(l N!pB@H hB@n t N%rB@H h!v z9x n9|q h n9~ n9 n9q t N%r!B@H N!fB@H  N%r99qP t N%r99q  N9 Empirical5!(dq ~        A Label Output5This is the end of the One Var Monte Carlo simulationdXit's a Two Var  z  ,!*!0A@0this is actually faster if the workbook is smalld $B@k ,!.!0 $%!0 z+~ ,!!0 $%!0 z+ Track Progress z '2 Update the Caption property of the Frame control. 0%$ ,!(P Widen the Label control.  ,!!  ,!,(, The DoEvents allows the UserForm to update. z 2 !.%0 GA@j"load array if user requested MCRaw ,!!0'initialize for next rep  !   !  ' !0 z +  k z(put down results if user requested MCRaw ,!!0   :   :$  : ($%(0 : k8#Get Summary stats and put them down ~    A@2     A@2WBasicFour(data() As Double, MEAN As Double, SD As Double, MAX As Double, MIN As Double)+Copy the MCSim sheet to the user's workbook 8 TwoVarMCSim MCSim.xla$4%B@6name MCSim sheet':  !NMCSim !N :  $!!name is taken can't use that name : ':Hk@8name wasn't taken so use itMCSim !N :  (get current MCSim sheet name !'(delete previous MCSim sheet if requested ,!>!0 (@MCSim !N :   B%B@nMCSim !N :  (ILdon't need -1 b/c deleting sheet in previous step lowers Sheets.Count by one !' (@kput down seed if used ,! !0 ,! !0$(0Seed$(0kPut down first 100 z d !.%D z$~ z $(0 z$ z $(0 z  $(0  $(0  $(0  $(0  $(0  $(0  $(0  $(0Dim 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( %$. ^     $F'0 d    $H' kX ^ te    $F'H$Fd     $H'0  k Label OutputA10 B2$(00 C2$(0U I4$(0Thaw K4$(0U AL1$(0 C AM1$(012"make a chart on the existing sheet LB@V+0)U R N(P Tim add a seriess CA0 XV  NB@Taa1:ac3 %\ NB@ZC two series~o<EmpHistChart 1 %^(Rsize it_ $%"!EmpHist %`(&] $%!bEmpHist %`(b}b $%!d EmpHist %`(d $%"!EmpHist %`( N!fB@H80 n h!j(l N!pB@H/T hB@ntampin t N%rB@H+ h!v z9x n9|q  h n9~ n9 n9q t N%r!B@H N!fB@H'/  N%r1899qx  t N%r~ 99qH  N9 Empirical5!(dUq  ~              A00(values1, values2, noobs, Nobins, Center, manual, minval, maxval, histsheet, histogram, myname1, myname2, histbegin, histend, histwidth, histno)u]5This is the end of the Two Var Monte Carlo simulation5_kdo the existing sheet optionz򢪍5 ,!!0D]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'048- H fYou have canceled writing output to an existing sheet. No changes to an existing sheet have been made. `No Writing to Existing SheetA@"ck]MCRaw '#ҶMCSim ' ~I5 $%!0AverageI6 $%!0SDI7 $%!0MaxI8 $%!0Minon1!0The 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. *0Existing Sheet May Not be MCSim Sheet CompatibleA@"lUakx(check to make sure existing sheet existszm@ $B@H  ^wThe 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'> ,GȶfYou have canceled writing output to an existing sheet. No changes to an existing sheet have been made. `No Writing to Existing SheetA@"zwpkhk`' $B@H  Ҡ0SThe existing sheet cannot be found. No changes to an existing sheet have been made. `No Writing to Existing SheetA@"Microsk B@041744  it's a OneVarb3:b102 $%!0b3:b102 $%(0f3:f4 $%!0f3:f4 $%(0Uj5:j8 $%!0j5:j8 $%(0 AH1:AL10000 $%!0 AH1:AL10000 $%(0&update the histogram on existing sheetQEmpHist $%`B@/ N%!'  $AL$2:$AL$ '?&the 10 characters in the search string  ,'     $'EmpHist $%`B@=SERIES( !!$AL$1, ! !$AK$2:$AK$ , ! !$AL$2:$AL$ ,1) N%(K15 %B@Hd>b3:c102 $%!0b3:c102 $%(0f3:f4 $%!0f3:f4 $%(0j5:j8 $%!0j5:j8 $%(0l5:l8 $%!0l5:l8 $%(0 AH1:AM10000 $%!0 AH1:AM10000 $%(0&update the histogram on existing sheetEmpHist $%`B@ N%!'  $AL$2:$AL$ '?&the 10 characters in the search string  ,'     $'EmpHist $%`B@=SERIES( !!$AL$1, ! !$AK$2:$AK$ , ! !$AL$2:$AL$ ,1) N%(=SERIES( !!$AM$1, ! !$AK$2:$AK$ , ! !$AM$2:$AM$ ,2) N%(K15 %B@Hk:Hdelete new MCSim sheet since the user wanted output on an existing sheet (@ $B@n (@<output to MCRaw sheet if user selected record selected cells ,!!0]CDo you want to write the output to the existing MCRaw sheet called ? MCRaw Output Option$"' (check to make sure existing sheet exists $B@H  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' fYou have canceled writing output to an existing sheet. No changes to an existing sheet have been made. `No Writing to Existing SheetA@"x7kp7kh7' $B@H  SThe existing sheet cannot be found. No changes to an existing sheet have been made. `No Writing to Existing SheetA@"6k6 B@   :   :$  : $%(0 : delete original MCRaw sheet (@ ($B@n (@ $B@HMCRaw overwrite end to IFk5record selected cells end to IFkX5existing sheet end to IFk05 Find Time elapsed and report it 'K15 %B@H   Q$(0 $(0run link checker I1$(0 Return the calculation setting  ()report number of errors in the simulation  #ErrorsF5$(0 G5$(0k3One last calculationA@Put away the form Unload Me ,B@reset progress bar Progress Bar ,!(P ,!,( Turn on screen updating ( : $ $ It was not possible to give the MCSim and MCRaw sheets the same number. You may want to rename these sheets to indicate that they were generated by the same Monte Carlo simulation$ `(MCSim and MCRaw Name Report&A@"k1|1x1    ' z 'zathis writes over the ith rep which has a value of 0 since nothing was stored in the results arrayd 1k11o1 Put away the form Unload MCForm ,B@o0H0] ,!.!0$%4'  Your entry, ,!.!02 , is not a valid cell address. Please try again.$A@"'t|/d/'t|/k/ Your entry, ,!.!0 , has caused error,   . Please try again.$A@"oh/X/] ,!!0$%4'  Your entry, ,!!02 , is not a valid cell address. Please try again.$A@"'t|.d.'t|.k. Your entry, ,!!0 , has caused error,   . Please try again.$A@"o.$start FMRG from given initial values-mAttribute VB_Name = "MCF@orm" Bast0{45A79246-C377-40A7-A2C8-10E37DC233A3}{DF0F0145-963C-45E6-A140-A0BF8H63669} G lobalSpacoFalse CreatablPredec$laIdTru BExpose0TemplateDerivCustomizDOption 0licit Pub CellValidSngTest As Boolxean9= Sub CommandButton1_C"k() O/rror GoTo E"Hler Dim I,Long First? ? Array()Do+LSecondQGmyreps!PctdoneSinglDmystartti@DVarianNA yours2eng xmNA,, xSDmaxinC@x22L22mViewTǂtA0AllA@zhistRangCC$myeHndTBw1G/, w2G ' The@ th e proWidths ofbins @/ ogramj )g5iijkkIntegN!a!(CMCR aw myOriginalSh8eet"()UmyCurrentMCSimRSLPosiBt myEndAl13Cour! % 'Get user*pudn c~make sure it's a v!kc!u If= !> 'Un load c !aExit A If ,= 2(#.RefEditOne.ue).PJ8nt.2!bexact loca)K as@TL  caPn be%dPaA Obje with all ual P@Xertie ccessi` #@ .Addr`(exter L:=)'U6echHeck-In 1(L1," ).ula, "=", vbT p`# ) < 11MsgBoxapmpt:="@s sel'`CB$dono$t tara f so"uDue wi`"bh. P `}try ag.s:=vbCr \cal, TitleF@Rl#`C?F#'c^t*(1k"Twog"""Hb`(|00a'1-1t11a z152Ӛ /&itz)/&%&? <%O%O%he4>%PE%sz %%@%%h%%|ti%% ?&Q  &Qdmym`=QqK PHgZn"qA6sewDOmPSnP//S SbRRE to .R=cb9dr?--' Aဠis wj* Re7FirstAArray(1 To- Track@L`ime elapsedstartt= Now!Af' Gehe calculaA se ng  r@%Applic.C SA#anualL T xlHM ' Turnf screenp upd S}Ud MwR`ANDOMBA ifquesta #@\q.Op!BuPtton='uVwActive@Sheet.a.s".elac`hat:="OM(v)`$mpLookA@xlP /, SearchOrd@[_Y! :=xlByRows, MatchCase:=\'Ea _@onFMRG%TrJMCSIM.XLA!gHHsvS#Aw''#4u`;8t2:9= 'q b2from4 gMn`alT vsb626403 'i@ Bc)a" cod mylag1D^q' 5 202{ho"wBPro`ss@ Bar  Frame%.Vi2@m`ore tQ`qc@V 6NlMCRa0P'ACt0j1d%/AsRb= AWindow.BSele0Cop0 y2toQy{G`h > 1?.hCou@ANVers~ < 1F2imptFY@canhave r" && "w"Recor0sh Sr2 o2R oRnyca5tbresultPki@]f0sS  PlePL lo>wNwRfbe 65,535B.TTitlO"T@ay spZs:=vbCr|it`h`f.`declaarbD ()Ds `4ervapo % EachPIn".Ap=6C aec8 1my[=@~(+ 1#N` Ct ra3&> Columns&ap.&֯$ '&s."+'Q'b7Azr&., %D+cpcomo~@$an Ex0 $.may Bina dvertvlygp7`a лr9@_2.V(.a6try again?'ny /`ó1''W?!W)ѽ@$add! R)r bs.Ad'I PP 1A( rS>2S rmakN9'3*spoob?mpÏI ooppensu!Tr would gahid n_C5 0p>`3 =,cinue3: a1iiP]B*C-a5."Rc!N + 8=s(ii).No T9' Nt=nqPatt=?:Go fşy>f@*on_!.pvailqsPb w&ab sor ii= j'+'&m1O&B #b ǀb$4Pb $Str$ myMCRawSheetName =       !"#$%&'()*+,-./0123456789:;<=>@BDXFGHIJKLMNOPQRSTUVWCYZ[\]^_`abcdefghijkmnopqrstuvwxyz{|}~Act(ive.H 'put down cell addresses in first row as labels for da ta I1  F Each a In Range.Areas #C#a.Cjs( )+(1, I).ValuC.A(external:=True)nSPI + R Np C `~size array ReDim`(1 To myreps, mVarCount4 End If' Run the Monte Carlo5DeUmine ifO0Two *f Second"" Then 'it'sgR{If_m.CalcBookCheckBo>xFNAIC ula@)'thi}@ctually 8fas,+2wo(rkb@ @ smElseCpOrigidp# MeFŁ–(MC9RefEditRA9DB ' Track ProgA Pctdo@b= I /D|C' UpesCaption property ofFrntrol.D(e=mat(, "0%"D' Wid~A]:L RFwidthN3* e$+- 10 `M DoEvent`@llowsbUsera- to\ uB!MI \d Applica)!#.WDs!Funon.Maxwci@,50!<0c[g'loadcqaNu requzePdrd+%[1)Q[kk@!0@gni0tial|nexep"S? Fab(I, kkCc-wϐbI!沠Uu~l2"}"8 ii##!j #ar{(@ , jǴ/jDiiM'Get Summary st^aAQ`A!am BasicFour , xmHeanSDaaxin`F(y@a() As]uble, MEANSDAXIh@h` CopyBeMCS_ewg6 ds(".xla").d+" "" Be@We:=-n!  LoopNumberIph`ger  = 0 1Finu0e4: ""s.?Ro !1P@ " & `w=T`!)BhEu'RgtakQcan't at  Ro`;Go VE;q-was sSst Mp7!gP2currZQG'myCB0m12Fdel@leviousFWWD2Pr#`(aDisplayAlss Faƃ 2. e2%c$%m). 74' 'need -1 b/c ing7FppKep seLrs.3 bnԒt s ow1T's scd9SetSTpqǡ<> 4#!s`(5, 6]AmZ.ut F7G"ű"^'PudC100HGA!bNgin02P3`w2g/O Аf2d 6Eп0xSD7ќmax8!hU`;MPL Pta:m*`th{rUt< 862Rw1"$(x -oin)Sqrt!'?0(10 * Log10DŽ His wmyR3ڔxX0, #X-,zChartObjeDct>EmpA") (va noob`NobinpcOer,0 manpinxrq",, whichon ߱("B2vmy&AL1?ll30r@3ʉ<$p1 8@40)Tz' . Win.TGridl@U̷'make a chart on the existing sh@eet CAs.Adds*$29P Category)(HasK Falsd6akp+C=??;k TitBTruib/bac@_s.T exayiric alD Call ogramM r1Var(FirstCellArray, myreps, w1, xminax, c$b, h, ViewTable@l ng, 1A@6 'BK `OutpuT1  is#Հ\ofBO඀ Mo`y Carlo s`imulaaRA EAb<'it's@Two  ReDim Second(1 To"For I@-If MCm.#cBookCheckBox.@`0 Then#e 't d0u+y fas1 icworkb smUOrigi8nalalc @%Ifc 6(I)@ 1uSrdRefEditC)cs it  c' Track Pr(esԄ Pctd1|=/eUpd`AB#CapKpro@perty $F -e !trol~.50= format(, "0%"#Q'Xde,xrm.o *D (ss.s- @vs She 0DoEv0.ll0~2UЍ* to\ u,I3d` Appl @.FW (bFun on.Max(=C> / 50#0.C;loa@ A-u reque.d3Rawl h4313%3kk0 'infala nK@G Each>In%.4"6C 0a.&#! 0r@+ 1 MC (I, TkkaC/NQC ra S!7+G2I'L down@sult~sOO5EO@E iiIj @OCounĩ1 ?g$D0]s(`, jT>Ae jii0 Get Summa@ry staPaIm BasicF -exmeaqdSDde\G`242B2m!hx2h(@a() As+u2hMEANSDAXIb'Copy6?C6/g] 5!^.xlap$"`PPBe/t nP`#U"LoopNumber20 Ginue5: #S@!!"1 :" &  +S"Gp;Eu' a}`n can't at {8uo0~.Nam+U'don't need -1 b/c ding n p p lowersAYby onxmyCurrent]OAVc End If 'put down sXusSetBS Text< > "" Cells(5, 6)FwĀ) 7 *""J(PE(first 100  @ I@1 To ;YFunG@Min(myreps,)B2I, 2F&Array( I 3 SecoDnd NJ NIWAE10 xmea6A%PSD C7Lmax8i@a1H4x2  CDim my, As DoublV'axHistWidthjc=9(=8), (,9:)?'@^ax] _aV@S'T= [M -0) / \N2Make h -ogramSuperi morScal , ,` Rg31, (bWfJ!2, 0E`.Chart@ObjectEmp"3"Var1", 2_ AaGbFu!' gwH @BRanvg!(#j1A 8 40a>a < 862Ȝw1 .xmin SqrEr(10 * Log10Oeijw22`ـ2 'Label Out ,"B2"LmyOnes&C+Two.I4_llZK+_("AL1ol\AM;z3m3a cq+x thex ex4v|tdsx.AdpC'..RTyp!)xlXY7tterLinesN08rk`c'add a0x0ries#Loc Where:H=xlAsS5, :=t!yourceData S:=Äaa1:ac3") 'two eShapep= )">3'size i A.Left[pBsP2N)0lumns(9) ( ).Top).Row?ro/ !'HeighO 1* 13//#bO3 Cof* 6.Plot@Area.S0c'QIn*iorAorIndebx`xlNZ*egendAIIAx#xl")WiBorder# 6.WxlHairl`5Q6Styl7 ;MajorTickMark< RPosi!\/%GridsIP lCategoryf Has \.1inor{; t = sTitR!б'1[ac/s.a@aCirlɇ`all 뢖2`(akcwwQ,l x@}P2 bax:,', `Q dAlangpViewTa0Q΂t(vsnoobNobiJnpC`er anualpin=xrQ2 istogram, mynameB1@2, hbegin(endA width(no) 'T^ is the V ofTwo Var Monte Carlo simulationvEnd IfEdo@x|ing sheet op,If MCForm.CheckBox2.Value = TrTheS DimEGSFAsian t my = @ApplicK. Input/(" PleashterMnumbobeTMCS8Wto whichresults~ will be writt`en. ! n#4 over" alos*t&Cgkncel if you are unsure.", *"A CHN Requested5N=, C2~@cOQFalKaMsgP "Yhave ca"edB "Aage" ,OrCqX6JSD" _ I7jMax?`8in" C D  , SD, ,"YMin&aAW\Zls @J5:J8,@ith labe@fI5:I8. Cm 1)" 32F$3M$?3UeIMAMA23Range("K15Se If A'dte new MCSim s since²user wanted output on pan eC Qp plicaA.DisplayAlertsMFal~s%DB).D"TruH 'toRDawif s@ qd record cells IfeckBox1.`Value A Then D!myMC Overwri%As bI`!ger!s= Msg ("Do you& cA'f( cad ]s%]?", vbYesNoCOC3g[!hI1}%lLC]S*' Returөec3Lqtt03 !Cal=qlrsed+%nN @RertroدiRv 313R/> 0TN/ Fщ "#;8(G)j"One lastS , Paway^QOpUnl oad MMCp;m.Hidp'A prog as bap?FrjbP.Caw>"a5 Bar@ Labelwid٠f= TscrYupdXngSUs6r5LoopgӜ<>Lw A0. > KB1dX`mpt:="Itsxpossiblo giiSt3<Xgs}"{mmQ>_`00}UoPdatqy w gener@ߘd bMo Carloz`mButtons:=_Title` wg t|p?pmxit Sub&Q.6HP:-013(-)p\ZIxI -@0s/RPb)q@6 has a va60Pphnw8stoarrayp pq>R0ObC Pr" ComFm#2_Ck(' 3 '33 $/34q`* NSrTFirst!On or Re~sume Next Dim TestStrin g As H T= Range(MCForm.RefEditOneCell.Value).Ad@dress(ernal:=Tr. If Err = 1004 Then MsgBox prompt:="Your entry, " & m" , is not a valid c a. Pl ease O again."w9YidtsNFalseExit Sub E pnd If ?ohas caused error&pZ@^ [SAecond ( ) Onfor ReATwobJGJHr7}  p`1t)6pM'P%ltF%$ a:@p(0]g6pK3' '`:P Npp: N 6p` +$ a: ]35 Cl4VcP%'%$ a%$  p`1l)6pM'P%llF%$ a:@p(0]g6px3' '`:P Npp: N 6p` 0$ a` 1/50$ a` 1/5 ll 0/'P%ltF%$ aBt 52lF%$  d)8o k@]'P%ltF%$ a35D' '`:Npplt*#*F^ i0/6p` ]3V$ /'P%ltF%$ aBt 5/'P%llF%$ aBt52lF%$  d)wo k@]'P%ltF%$ a/pok]'%llF0%$ a`/ )6`X' '`:Npplt*#*#ll*#*F^ i026p` ]3V$ %$  ;: ]/(P]Ip` @ 5@"+' '`'p:N 6p`  Btooj+' '`'p:N 6p`  "qdldp P. $  li@% $  $" $  -$  c]35U: %:@!%%%fp%$ @Wp"#$%&'(5p-$  c]35W:!%:@)%%%fp%$ X/$  : ]@5W:!%:@)%%%fp%$ X #g{*)$   |+5|,$  -pp-$  c]35 '$ Da.5Xld$ av" $  dl3 /)5 f$ av'' :/N``0"#*#1*Fp 26 p` !a82[T5P. !a.[L5H.| kZpZ H.d P.C YkZ$ `av" $  dl3 /)5b f$ `av'' :3N``4"#*#5*Fp 26 p` l$ 7 '0%'@%'P%'%$  ()pF/$  0ld<^ q6$  0lkF#*F]8li%$  d8Wp3/)86pR kFpF  f< R6$  0lkF#*F%$ @rU (  y$)Y1++.!-A./ I1q1A412l!54I55618l!9c1:Y:::;:;9;a;;;Q< ,?y<!=@C)@!A)DQDqDDidD9m Y8881@8<!EIEmIFFGAGqGGGG!HAHHHHH!IQIIIIJ1JqJJKIKKK!LILLLLL!MQ!QQMSQ)TITyTTTTTP9Y!UnZ9]9Zy]^ _1_Y____1`nI````]P9aiaaabAbcabch9eahhhhh!l9iioop9q$$$ y$8  <%%@ y&   D&&   H&'L&9'   P Y'T'1'X&i(    \(Q(``(q!) d(QI)`ah&q)l(q)p )t%)x&)|(q*&*(qQ*&i*(q*   %*s//oHGKF4Q'%$ a%$ apA``1)6p`VcP%'%$ a%$ /)5"$ @W`145 qx!a82[05,. !a.[(5$. jc% `Fp%(0%x@l4F@%$  d`"C7)6p`lxqx $. ,. ldkZ\ ll 0xldd-$  c]354 $ Th2lF%$  d)f$ a%8lF%$  d8p8A`7Blxlp)86p`lxlds`Q:P9N`M@p :p$  @;/6pD$  -n`n 믳7$  -)Tlx(%ld2k@%$  @B)5 <-$  c]35p!a82[5.!a.[5 .| kp aBlxkl\5  .D .# xf -$  c]35alddakZdWdlll\k%liP%@l4F%$  d"C7)5 f f* M0@(M@@0MP@8M@p =n$ @Qo%:P>%8:?%$  D@8 A dBC)85p/$  0ldqD$  0lk#*F]8li%$  d8Wp3/)86p kp f1RD$  0lk#*F%$ @/)5"$ @W`15-$  c]35" $  tD$  0lk#*F%8$ H A8 dE/)85RD$  0lk#*F%$ @/)5"$ @W`15" $  /$  : ]@5MX$  (%(@%$ XpC7)6p9:F%(%(@%$ XC75Pxldi%(@d%$  @B)5dGlxlpk%lxi%(@%$ XC75 xf;o8k%(%(@ %$ XC75;o0k%(%(@ %$ XC75;o(k%(%(@ %$ XC75;o k%(%(@ %$ XC75ld^ ld9 Gto(o ot@4o(o k@(P dM@ Hp`Bt5,lF@%'P%:I%$ ,,lF@%'P%:J%$ ,z(%(@%$ @"(P%((%$ @p"8p$ Q8)6p f%$ D,7 '0%'@%'P%'%$ \ () K$ , K.lF%$ , K)N'@%:L%$ @8.[8$ , lK)851:@M%:N%$ @OC5o(P %lF%$  dPApQ%:0M%8$ @8`RCQ)86p`o(P %lF%$  dSApT%:0M%8$ @8`RCT)86p`x(P %lF%$  dSApU(0 `%:M%8$ @8 RCU)86p x(P %lF%$  dPApV(0`%:M%8$ @8 RCV)86p 2 $ , K)0% $ WWCX52 $ , K)! $ E> %$ , KY)* $ WZq%lC[%lC\ q $ Q%]%^%_cH %$ , KWp`Y)5p2 $ , K)7 %$ , KQf%af%bc7 %$ , KQf%af%bc$ ,Q  pK;:c%  Ka[d,)5cv$ @(pM @M @lM @Qo(M0@ M@@MP@dM@p e(l=.6p2ldhxldd!-$  c]35+ $ T_2lF%$  d)f$ a%8lF%$  d8p8A`7Blxlp)86p`f$ a%8lF%$  d8p8A`7Blxlh)86p`lxlds`Q:P9N`M@p :p$  @;/6pD$  -n`n 믳7$  -)Tlx(%ld2k@%$  @B)5+! <-$  c]35!p!a82[5.!!a.[5.! kp aBlxkl\5 .! .! xf-$  c]35"ldd"kZd"dlll\k%liP%@l4F%$  d"C7)5 fF" f2"* M0@(M@@0MP@8M@p =*M0@M@@MP@M@h =n$ @Qo%:Pf%8:?%$  D@8 A dBC)85p/$  0ldE$qD$  0lk#*F]8li%$  d8Wp3/)86p9$ kp# f#RD$  0lk#*F%$ @/)5"$ @W`15-$  c]35&" $  tD$  0lk#*F%8$ H A8 dE/)85RD$  0lk#*F%$ @/)5"$ @W`15" $  /$  : ]@5&X$  (%(@%$ XpC7)6p9:F%(%(@%$ XC75Pxldi%(@d%$  @B)5d|'Glxlpk%lxi%(@%$ XC75Glxlhk%lxi%(@%$ XC75 xf|&';o8k%(%(@ %$ XC75;o0k%(%(@ %$ XC75;o(k%(%(@ %$ XC75;o k%(%(@ %$ XC75;ok%(%(@ %$ XC75;ok%(%(@ %$ XC75;ok%(%(@ %$ XC75;ok%(%(@ %$ XC75z(%(@%$ @"(P%((%$ @p"8p$ Q8)6pld^>*ld9 Gto(o ott*4o(o k@(P dM@ Hp`Bt5ld^*ld9 Gtooot*4ook@(P dM@ Hp`Bt5,lF@%'P%:I%$ ,,lF@%'P%:g%$ ,,lF@%'P%:h%$ ,,lF@%'P%:i%$ ,,lF@%'P%:J%$ ,,lF@%'P%:j%$ ,7 '0%'@%'P%'%$ \ () K$ , K.lF%$ , K)N'@%:k%$ @8.[8$ , lK)851:@M%:N%$ @OC5o(P %lF%$  dPApQ%:0M%8$ @8`RCQ)86p`o(P %lF%$  dSApT%:0M%8$ @8`RCT)86p`x(P %lF%$  dSApU(0 `%:M%8$ @8 RCU)86p x(P %lF%$  dPApV(0`%:M%8$ @8 RCV)86p 2 $ , K)0% $ WWCX52 $ , K)! $ E> %$ , KY)* $ WZllqh%lhC[%lhC\ qh $ Qd%d]%d^%d_cdH %$ , KWp`Y)5p2 $ , K)7 %$ , KQ`f%`af%`bc`7 %$ , KQ\f%\af%\bc\$ ,QX X pK;:c% X Ka[d,)5cX$ @(pM8 @MH @M` @lMp @QoM@M@(M@ M@M0@M@@dMP@dM@hp l@l=.6p-$  c]35vDO (%'%'%'0%'@%:Pm%:n%o$  f]3Z33' '`:PpNpp@:qN 6p` xD:6:D:r%$  d8Ap7:@s]<`:0t%8$ 8 d 8A7:u]<:v%$  d8A7:pw]<:`x%$  d8A|7:8y] LL**L 8 I@ X I x I   IB  'ID  (I   WI  `z T`: x ]`   ]@ `DI G`0ls--  unne%`FDi HP s Stll Retri8  J va(1)off p  L  for  N 4 Tfor zero ` `'` `P`R `TND`VRAND`X`Z`\`^`$` `! `'` `  `b`d+ %`f   @ ``!`  j OSe%  0P`jge.C`"!Co`$ = m  `k'' ) 08% :$ )px: ' X)& Friate st I inglI nge I  liI$ 8If)(pxWhmit X), 0 Then  `tmit `zp `:8l$ p`!(k@I ` I  `x H`pDk I  x` 8I Xxx)I x`t = `z `+I  mulI ( I H I h I  I|  `(x `Xk,,I  EnI @ limI~ me f`tless`l(xx)` dalra`\imit`Tebe`LB@`J beeh%`H(@Hl  Ac8Axes(xlete1:v9 st@x\ !@L  @D 9 0`8H@`(PhxH8P@` artN@ 9 *@ r ot RNgr@ ame @t labe@d Char@T +@ D ber @4 the@`0 stA0x85rowH A@@, aria0`5@( = hi@$ eft@  ge.R@ .ro@ 15).@ ۫@ ge.CX`=.Left @h Ahi@EartObj@ ObjehE.Left hH?hi@ (his@ Coun@ Ihitnhzectsis@( `Coun@= rowH@X `eet.x(ts(hisp`idth4%X@ `h!&@ 8(@`'$'$@( `!&Hj@`vD%(%`x@(@@8 iewT@p( 2U@X ed vH use the metdo@ Var@ imit@ 7 MiHhp@@Xh obs @X  @H @8 scal@( x @ xx8  0 pH pgBox histRan.A@\ G @X <@T ! @D s(Va@ 4 U@$ @   @X  #p 0 @  0 X 0@ @ X @  b ! @ ! !  8! @H! `! ! @ PSp!H!H!" `@P" "h"@8"h @X @"H PSHPS@"8 b @( H @        (  H X hx     (  (08@*H 6x D (8HP`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> Histogram 22 October 2002 Routines modified 8 Jan 2004 This revision: 22 Feb 2004 Contains NewLimit routine, which does a better job of finding binwidth 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 array!. noobs: number of observationsact  Width: binwidths  minval: minimum valuen be maxval: maximum value all/ histsheet: the sheet where the output is to go+ histRange: the Range where the output goes!# ViewTable: include Frequency tableell do AllLong a number:e  This routine determines Width: this is binwidth xx: this is the starting value]]el] ]8 ' absolute value of minimum's a vd''     Un Bring xx back above absmin     'H  '= xx = 9 * yyhThe 'z   doe z 'za z ' c! We're done: xx is just below yy   eFata   A@"  'Un ''z     $ z 'z z 's ( z  '! We're done: xx is just below yykkd''k   'This is the difference$0 Next step it to adjust width so that it is evente' the default;ge w   t wa '6we're going to have to lower xx by 10*yy if minval < 0anal   wan   '   'ha   '"  'x  G    'j/ Next step is to get the Width to be reasonable Start with original width]Ph!'   0   One ' ecte ' o' enot @'t wa'dX ' n'k80 'd    wan '  'ha' e @'A@r'd ''k 'kp   ''d@ '$k(k o Written by Frank Howland] ]8]P The bins]]]]]]H]]]]]](]@`]weI Get the variable labels--this routine may be unnecessary if public worksDim Varlabel(1 To 2) As StringCall RetrieveLabels(Varlabel)varname = Varlabel(1)off ]]   '] for]0]H`4 This code checks for zero width (SD = 0) histograms  ' 'd   A for Rk   '     AND A!RAND   Se   ] 8'$$     $$A  ,!  %!' e use No. Obs. ,M. z  z$  G z+jress z $ to  z$ z  ,hi z$ z  ,' z  % z  %$.CRaw !"B@ !'!   G ' j zepsOSet myboxRange = Range(histRange.Cells(17, 3), histRange.Cells(18 + Nobins, 4))nsCall PutInBox(myboxRange)4 Is this right or should it be histRange.Columns(3)?heet. !Columns(3).ColumnWidth = maxwidthk8& %'ny :  :$ : % ,el if :$ : & , Name : MC]P '  G 'j& Figure out appropriate starting point to Dim limit As Singleges toDim mult As Longe Dim oldy As Single limit = 10mult = 0cIf limit > Abs(xx) ThenRaWhile Abs(xx) < limit oldy = limit$ If mult = 0 Then  limit = limit / 2% mult = 1$ ElseIf mult = 1 Thenerage, limit = limit / 2.5in mult = 2in cel Else:I8 of limit = limit / 2ntai mult = 0 may n End If. T Wends  If xx < 0 Then limit = oldy Elseting S While limit < Abs(xx) oldy = limitx If mult = 0 Thenin limit = limit * 2  mult = 1 you e ElseIf mult = 1 Thename of limit = limit * 2.5to mult = 2ts wit Elsehis si limit = limit * 2ques mult = 0 End If Wendu # If xx > 0 Then limit = oldyg sheeEnd Ifs toIf xx < 0 Then limit = -limit`: do same for valrange if it's less than abs(limit)-abs(xx)*Then add the valrange units to lower limitHistogram TitlebeEmpHist %`B@stin R N(P bee% %  & %$\  NB@Z1744 N!B@H  N%rb3:b99q`  t N%r%99q0 N(!0 N!fB@H n h!j(l N!fB@H$ hB@!0 ActiveChart.Axes(xlValue).Delete1: N9 Histogram of 5!(d%qx  N%ristogr 9 $9 Q .MajorUnit = tickwidthq t N%rB@Hp h!vGO z9x n9|q h n9~ n9 n9qDim chartName As Stringp9 **** Need to check for other charts named Histogram ****&chartName = "Histogram" '& Varlabel(1)osofActiveChart.Name = chartName +@ Count the number of chart objects and then just assign the lastA0$Dim rowH As Variant, colW As Variantc!leftC = histRange.Columns(3).LefttopC = histRange.Rows(1).Top^KIP9&.rowH = histRange.Rows(15).Top '.RowHeight * 14۫,colW = histRange.Columns(10).Left 'Width * 60bAhistsheet.ChartObjects(histsheet.ChartObjects.Count).Left = leftC?histsheet.ChartObjects(histsheet.ChartObjects.Count).Top = topC0Ihistsheet.ChartObjects(histsheet.ChartObjects.Count).Height = rowH - topCIhistsheet.ChartObjects(histsheet.ChartObjects.Count).Width = colW - leftC %B@]h0 !&'"d8'"k( !('$d'$k  !& " 9*j !( $ 9,nvq %B@HoCall HistogramMaker2VarRev(values1, values2, publicnobs1, publicnobs2, w1, w2, MINvalues1, MAXvalues1, MINvalues2, MAXvalues2, histsheet, histRange, ViewTable, 1)' 21 Oct 2002UA In this revised version I use the methodology from HIstogram1Var$ for setting limits on the histogram997 Mi] ]0 ]H  The bins] ] ] Dim Nobins As Long] Dim histsheet As Worksheet] ] ]X ] ] Dim noobs As Long] ] ]0 ]H ` ) keep track for scaling purposes]x   ] ]  z] ] 8 \]P h ] ]  A] - Find overall max and min for the 2 variablesCk :'MsgBox histRange.AddressY > : G >'j <' @ < G @'j! 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 6'6 <'Td0 : 6 TAk 8'8 @'Vd > 8 VAk4 Choose the larger Width as the class interval Width 6 8  ******* 6'b'dP 8'b'k8 : < 8'b'k > @ 6'b'k1 This gives us parameters for the first histogram T V  T'dd V T  b'f V f b 'd d T G d b 'djk(d   V'dd T V  b'f T f b 'd d V G d b 'djkk b    'bd`'bkPkH < d '  b 'R R8 RX R Hx R J$$$. 2 R b d$H$JA  ]  z R z$  G z+j>avoid rounding problem z $ to  z$ z  , z$ z  , z  % z  %$. !"B@ !'   G ' j z $"( % R  %$.Call PutInBox(myboxRange)k z R z$ z  , z R$'Z : R :$H : % , :$J : & , : reset bins for second histogram*Call Limits(minval2, maxval2, Width2, xx2)2 This gives us parameters for the second histogram @ d '  b 'P P P( P LH P Nh$$ 0 4 P b d$L$NA   No. Obs. in $` , No. Obs. in $` , % %$(h P R  why is this column 4? % P  %$. z R P z$  G z+j z $ to  z$ z  , z  % z  %$. !"B@ !'   G ' j z  , z $"(d % R  %$.kCall PutInBox(myboxRange) z P z$ z  , z R P z P R z  , zk z P z$ z  , zkx : P :$L : % , :$N 2 4 : ' ,?* (Width1 / Width2) : P$ Z G P$'Zj Figure out chart scalingDim tickwidth As Single]p R P R'jd@ P'jk0& %'\' %'^ Put in the chartEmpHist %`B@ R N(P% % j ' %$\  NB@Z ActiveChart.SetSourceData Source:=Range(histRange.Cells(1, 37), histRange.Cells(Nobins * 2 + 3, 38)), PlotBy' :=xlColumns XV ! NB@T N!B@H  N%r99q` t N%r99q0 N( N!fB@H n h!j(l N!fB@H hB@ ActiveChart.Axes(xlValue).Delete N9 Histogram of \ And  ^5!(dqh N%B@H h!v9l l9x n9|q  N%B@H h!v9l l9x n9|q If Nobins2 < 30 Then With Selection.Border .ColorIndex = 57 .Weight = xlThin .LineStyle = xlDot End With End If  N%r d9 Z9 .MajorUnit = tickwidthq t N%rB@H h!v z9x n9|q`  h n9~ n9 n9q8  %B@6 %B@] !&'"d '"k !('$d '$k   !& " 9* !( $ 9,qX  %B@Ho8 Y Usage Call MakeBins(binleft(), bincount(), values(), noobs, Nobins, Width, xx, x(), y())< This routine determines the binleft() and bincount() arrays]]  Ap   !.%r+Dxx z $  z z+ z$  !.%r z+ z Put data into bins : 'z z$ :$b z GtFj z 'z0 t( z$ z+ : Get coordinates for histogram$++ z  z ': z $ :+ z$ :+ z$ : + z$ : + z $  +  +o]'    'h $H 'oH $v$@$v'Hi]x]0'  0.' z  0' zk (zox]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]]]]]  ''dx'kh $H' $H  '?$H'@$H'@$H'!@$H' '          'dx 'k`dX @'k8d0 'kk   '~ ~ '~ G ~  '~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`Rble, 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@FnoobJMxx, X( Xy(),F=M.Cells(18,5.Column'BView0@}TrueB6 H4) "No. Obs."Æs| IHXAa@AbsF7I)) < 1[10 ^ \-6AnLThen q  Next :Ic'Ȍ17, 3M18@2-R4P'}PutInBox( ' Is t` right should i0t beWVs(3)?CX! "O _Ernamne8!Xj` T2Ma *j27aQX(jd Y8AyF0jtickC7 S@l =Cbv- > 5ga ' Figure out appropriat@e star PpoinN'AlAmult@oldB#@ w'@̀@SC> axx) Whil@< 'aB =a]'' $ / 2)g g .5S )s' 5 ?|U,rWend z< P+><DAH!%s*eO=  /P >dyoc-R!d8o sQIa ifO's lesQQan (2)-2]addSef un`o lower3HiBs TitEActSheet.ChP@ObjeBc"Emp").2`BOTypTxlXYSc`erLinesNoMarkn 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 subLongBIfp@t>1 Then  @ = 0` ElsJ1@@If vFWiWindow%H .ScrxollGE3-` )S -[ME:$N(q/Sel`uaS,ubA FMaker2`(values1()3Double, 2noobs1, 2, BBminmax"A" i,>, All!C E`Vie8wTaCallRev%, picn+` wwMIN@ , MAXga`(1)' 21 Oc@t 2002@w Iis revised version I useBmethodology f@rom HID1@4afor setting limi !K-mIEmGjObini,"I"o!U' Fa  !:2pUriMS cg1e'MsgBox8qAdd8res@n2 < rG}D=A DQcY >TbQ AHWReeveLqs( JYm$!=82U(v2Sp$] excepdASto ac"two0Qries ŴPs!plop^is1&r va3‚10CFA3NewLY(qp !xA3 iC3~2c!pF2wq.=O= 0  1 $*-Xth  _\ ar }gsQ`| paepsXfi&y7$" b-_ eg &OTp;In1 phll D0  ,E&Y@s 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[w 1[A=` 0 EϦ1`M l l h !d1cCW8indAB.ScrollRow -G4v g$-~Sub1 MakeBPD(2left()Double, @counT vys{noob&sI, 5, xx, Xy@~' Usage< C<`, i, W<x(@PHas Bu_e denm‹the $a #AarrayPb'q+preciskIAjr F`"P( ! 0)0# Appli.WorkFunon.R d(9G 'PC IP`3oDbyIqH+S`IO , NA' Put data intoq$y j BO  Doi`x <=5"jTfsD GoTo veloop:@I໗ @1LP  S25+ j)Get<in@Qs for ;CX(B 0 ycpOO412 -ahX(jYIy G @$1Dy(]2L[K5h';X!9SPinglX% Wh#X >S/tX / 12W f,-Log10(X)ytpc 0pmc_3Bp/(10#`XU#Y" ҿSps(sPw #p tr vH!$= "0" q > 5!.! &vS *&CxF .Numbe'P 1Dro` p[g%S!,P Allhrш,4dwhichsǴ$[ ' 7RS:= u(}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  #__SRP_f|RNGandSortModule"__SRP_2 __SRP_3$1Ik/////////x`@ay !9QqIkk//////////////\`$yYkhk////kk,!`y //(I`Y/,q`) //@`y1Ii//////0` y///nrU~| ! )Y1d '\ 5\'\ ϳ5\'\ ϳ5\#gD jDttDvDdzDԁD„D428OHK$*\Rffff*1R4ba89cb0*\R0*#15*\R1*#337*\R1*#10d*\R1*#fc' : &@ ,h " F \ Xh * 2 6( 4` <  ` bHD ( "8 @P X p 0@P`p<*w^Z0xMEP> LL<<<@P @ꀎ$@☒C%4 o P`(>k''' D0 the`>n yo `|kN+2``k b ` m `  u -76h `  8` [[)@Hduleoject (w (i h worist bibook``renc`or d``zrU~|  %n:rrr)Y1 ,qGAH_GIKK!IQI!sas!LssIIIJ1JqJJ)tAtI!0    ` *sD(]3E (b Bt\M@ll  ok*sD((l$DM@l,ATBt6T(c]3 (%(%:d%l$Dxl %$ ,  )* $ Wq%lC%lC q $ Q%%%c$(%(%l$>T5Tl$A (]35 q ql$A!(]35 q q$ D Q(l$A liT"i%,5(l$A!liT"i%,5c$(%(%l$>T5T,` llX @pPT,($ T0 @  tM\@l #O$\BkD%t@TX$  X $T@B )XT5p\"d / k4 Dlpi\Blp 5Wlp .@@t@TX$  X $T@-Blp )XT5 pf {l\"d qplp llnlpi\]D3[nlpqp-lplp lf# (,p\"d:lpqllp ll(lpll,lp ll(lpll, pf/\" 4(D($"(5&D(\(4",5($ XT\sxnxjh] Xt0 nx sx (xMh@H "H8(X(b5H T\$ H8(h4,$@ tD\B9 oDkLl 84(l 0   L X(h]3" (hbF"h XH8((bdL$ H8L ((8]g. 8b pJ7pJl "Btt5"l "otk8Btl5$? "Btd6$@ "Bt\6$@ "BtT6$!@ "BtL6 otk8b odol o\ol oToly oLol^((8 bt((8b(8@b((8b# 8(b(kJD8b kJ 8(b$ Cx`(+` x i  agaiiX from+` Pipis si +x `  i(i``  `  $)+ i%Piiabil``$``z VBA  ` 6`z a V  `eded`8 `0he r`D it + `  Aint bien B  ` H Xice      ` $$e) mplyin a cel ixls `   '2is functp   ion WILL  8 DiB@St @ `zr te`:eede  ` )   A@" i0 6 MHiC G4`ds` `z' ( `    `z6**+ ` h i nd pielow  ` ..  h` ` )6 T a macro iQ Tiarraiewheile 1``to 1` be 8`  NN) ii( oile 2` `zto 1` N`h S`Hices`L zer`give` Exa` le 3` yOut ` x##A iHes ciL is iutedie FMii8 TiXi x0 oris th ` d)e genera i8umbeiXodd ixdeviiiratei ``' ` H y) $F' hizi:8 i`z`" U `  pꀊ@ꀌ@(@ꀐx(@x 8  (  P h(8Px8@l @\ `2tuse No functheet, simp t@p in a@` par(x ,@H`p "x Z   0 @ >P < < >( 8>h     B J . $P `  h p Jx R B  ( 8 @ H BP "         & &@ h " p  x   $        < ` Fx  "  ` ` <x X $ `8  d   "8 &`  n  *( $X f   ( "0 VX . h "`   .      @ "`      (8 P `p   " @HPpx    . B   V ^H .  "( P 6pB   V ^ .h    08 B  @H X` hx X  *8  @` "  X08 *P  "  "  @ `p B  x  >  48 Fp $ , 6 H X Np 4 . B  ( B0 x    "   !!! "  ! ((!P!X! h!p!! !!!!!!!""0"H" h" " " 4""," (# 88# "p# # # # #$ "  $ $($8$ H$P$ *h$$  $*$ $%" %%(%@%"X% %% B  % >% >% @ &`& p&& && &&$&& & ' D ' " h'p'' l'(( ( (0(X( <`( ( ( ( ,( T) @p) 0) V) T8* V* * F+ BH+ P+ B+ 2(, :`, , ,, , X- ,X- "  -------"- .8.X.`. B  h.p.x..... B. / :(/ *h/ / / /// /&0 80 @0P0 X0h0p0x0 0800 @0 $1 81 @1 P1 h1 1 1 1 *1 1 2 2 (282@2H2P2X2`2h2 p2 2222222 2 B2 3 (3 03 $83 `3 x333 333 63 .4 404 h4p4"  x444 24 4 4  4 445@5>H55 65 $5 66 6 6406h6Bp66  6 6664787@7H7P7 X7"  `7h7p7x77 77 77 77 7 8 8(808 88 @8 "  H8P8X8`8x88888 " 8 8 T8P9h99&999 99 ::0:,H:x:: : :4 This module contains three general purposes macros:tines 1) FMRG random number generatorev%2) Normally distributed random number3) qsort sorting proceduresob 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" workbookograRor, on a PC, from the Project Explorer, drag this module into the "active" project#For questions and comments, contact,Humberto Barreto Frank Howlandt /hbarreto@depauw.edu howlandf@wabash.edu-765.658.4531 765.361.6317pHis6******************************************************% 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)H] ] 6 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 'arlo 'XLoad B values from p. 147 of Deng and Lin, "Random Number Generation for the New Century," The American Statistician, May 2000, vol. 54, no. 2is s]#g+ j+.tt+%v+~dz+ԁ+„+)+ +U +A + + ++++++٥+++++f++%Pick a B, each with equal probabilityrray]8  '! $'oPTTo use FMRG in VBA code: FMRG runs the rng, myFMRG is the actual random number drawn!0To get a VBA Rnd, just use RndP*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@j8This calculates the next "random" number in the sequence           '  ' ' 'o8C To use Random function on a sheet, simply type =random() in a cellz( See sheet1 of Random.xls for an example B@A@ '2iCThis function will NOT recalculate with F9 or Application.CalculateLThis function WILL recalculate with CTRL-ALT-F9 or Application.CalculateFullDi B@StA@ 'i;The two macros below remain for testing, but are not neededL]@BP ' z@B A@  z+   A@"$A@" PowerPC G3; 266 MHz -> 9 seconds PowerPC G4; 500 MHz -> 4 secondso z A@ ' ffffff? H}8g?  z 'z  z+A@  z+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 choices!Q 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]]]]] 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] $'a Because we generate TWO deviates at once, we divide number in two (with MOD handling odd number) We waste one deviate if odd ' Use VBA's Rnd to generate random draws z    '  '   '  Gj $v $F'  '     z+'d8    z+'k zd " Use FMRG to generate random draws z  A@  'A@  '   '  Gj $v $F'  '     z+'d    z+'k zkh o` 'This is the old version of normalrandomDefault was Rnd8O 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@]    A@$'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@]  'Error: SD must be >= 0dSD OK   A@$'ki B@]H]`  'Error!dh      $F '   '$draw a random number on 0,1 intervalA@map to computed interval      'kxipx]8]P]h$'      $F '   '] z $draw a random number on 0,1 intervalA@map to computed interval      z+ zo]]'$ A](@Xp$   0 DA2HiA@"o B@A@      'i@Barreto 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@]h  DF must be greater than 0'd  A@$'ki "Get number of t deviates requested]@ $']X   A@]   ]  X ]` ]x z  z+ A@ :  :$ :+ z$ :$ z+ : z-create t distributed deviate and put in array z  z$ z$ $F z+ z1Uncomment 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)Nexto ]P h $'$'] z  $draw a random number on 0,1 intervalA@map to computed interval $v    z+ zo ]'  $A z' z$ z  , zoh( 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@  'Error: SD must be >= 0"dPSD OKA@ $v    'kiEnd Oct 2004 version=************************************************************* ]@B 'eNormalRNG NormRand:=result, TypeofRand:=1, mean:=0, SD:=1 ', is an alternative way to pass arguments A@   A@" $A@"!PowerPC G4; 500 MHz -> 17 secondso6******************************************************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 ] ]8]P    $'   $'       A     A     Akoh]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' 'z  z   z $ z $' z 'd z 'zk z'  z  z $ z $ G z ' jk09 ptemp is needed in case we have three equal pivot values in the median of three method      $'H  $'L   '  $' H L       A  H L H  L  'd 'k d 'k L  e H  'd` 'kP dF 'k8 k0 < If check = 1 we assigned p already in the Check for smaller  routined ' If x(p, M) < x(p - 1, M) Then p = p - 1 End Ifkp dh 'kP G 'j/ 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 'i 8' ', This checks to see which element is smaller June 2002 FH H L H  '% pick x1 because it is larger than x3d '0 pick x3 because it may be bigger than x1 and x2 H G' j0 This says that all three elements are equal, so we need to discard the resultk H e H L '% pick x1 because it is bigger than x2d '4 pick x2 because we know it is bigger than x1 and x3k@ L e L H ' pick x2d '& pick x1 because we know that x1 <> x2kd'ko]] ' '    $   '  $ e  'd    A  '  'k 'o]]   z $' : $ z +  : +0o( read in the dataM the array must be DIMmed as a Double to be compatible with the qsort routine]X z : z :$!0 z :+80 use qsort A@ Output results z : z :$ z : $(0xoph?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 How&l hb @depauw.X hC f@waba sh@ 765.658.4531361.6317.'*2mv@"Dec0laraC@:en8abltzbexd by oVros@~SAMVEBf(p)T|o \a REFERENCEVB must mad!ovp IPub@lic my)As Do@e ! Int Curncy ' ssheet (DocR@som@.xls fd8ocuAFOlag1) 222bLongfISETege|dGECVt p@214748364DSuby2(0 IFxb C@z 'Gru@b`n; no npeed @7aFg%anycI  0"= Rnd * 2 ^ 4242`'Load B valsc}p. ! De3 Lin,b4 NG¨aW"6~New CAyury,"@e AmericSt@9 cia@ May 2000, vol. 54,@. 2 Dim BArr ay(1 @X25R). )a/6403F= 2p7149F @9,81 430 22 = 31336323J6f 7C98G8B46019B6098F10c4181`66 713684h"3709KE1C787hc961 40859404217@Ee 424 5g 24319G'24369Z24 24024567h4633` 'PickB, each wi@th equq{robability"myX1";f04 + 0b # 9EP2:Ya;?L;P'A7MPrVBApjde::.rng,Pp!t ۃ@rwnXgpOaA1:, jVߠg9Xnr9@, TWO P|pɀreAedSQ xX`CR 3;``Px'If`OP`pAEtime,cplearfAB@1s=eZcplow L׌#KwzhB 00BnRrcal culatDnext "S"E s0!encgfP9@(bPpQ2 -tR)p@(/ p@## OV' j(FunP`_(Og0 fa sAu, simpxtypPa cell"S%x1qvwZexac0App0~x.Voi(Trues'= U R"1 will NOT recV=F9 ICCe ^WILLCTRL-ALT-3FuQNV?Fals ,NVF/'a1twoE1em{`-t, bu 9@cond@'4; P%4'/egra$phqWh&I < 10C X % PDX > 0.74 A\X00F ѼI`C 0s(I, kX3Blu If Wen0W -)*O@4 vers Co Cۃנ abovbeQV ] A=ey`C'* (=G, OpewTEofQwBooa0MePD&SDL$l Recip^J+t. 77:#rt1 Scientific Computing, 2nd ed.; vol. 1; p. 279 ' Box-Muller Method XTo use, call from a macro with FOUR parametrchoicesphe f irst  requires an array so it has to be DIMmedmewhere pin tFoSExample 1: Explic>ChoosDimfsult(1S100) !/doub pMUST4 @c1atinNormalRNG.1, 01These tw o linqcrpeatexG @vectorfat isd+ly dist ributvalu#base' on VBA`'s Rn2: Defa1'Q3Sinc1@\.dske, mepzero,sd SD = 1,@76giv.msJao 3:AmmyOutFn0 o G` ,2a5a aFMRG roe/V1 As DB B2MRadiusMFACI 6 Optionalr ' TypeofRand, which0 @FalSif not set--th4!8on@=y`|s)LaG et:e numb{of $Sy|es@Uc( $= UBound(c7())A&eca we genereTWO` devi``t ^c` divi.d@m(tMOD`ndog o ddd ' WeX wa e ! Ifh'נX%In! U@'nr@-`drawF-Ia` CB ISET0  rep : F= 2 *Az - V2:J= 2^ + ` /@>t Or  GoC"AaR= Sqr(-Log(q) /,!Gsc* c=I)@`b`@+a+$`ETq *A0 E IfNext Ic`b 9v{8oDHq8$ ~  Ae!:my"2;T=Y7 _>/()2 ???? ?? ? t?0#W Sub1 'TGNNhold_rsT PLn}S<"5tBvFuncV >Adv(FXC'WZBool@q,}GZM1rP 0f)y^`\–: f a sheet, sipy t`_$ = ()Aa 0cell/G 3 cc{`, e8g.,P|@, ~52a* 5A ' See1]om.xl P@e Ap`on.Vol(TrueDwqr%Vq I1DNG,oq,B yH?0!#~#Feb 2004#@It for`c<'Alexdecl౲$p'$ G&ORMALRANDOM(#"4"d i"o"Q/"e""a^l"" "n "Ќ""Q"9Tqw"Po< wD&Nh ""L'E rror:  mu`SqF6i'OKD$(u(?+(1(Al(Q"Uni%m!DD2OH#aGq8fb  T!kvb(1r,+& 1) !k- 4`a 30)0Pia- p]QtdЗ! 4{p1PP谪ǣ:map9inx* (b0a) + ~aFgE@(dS,'a?Lopng formRand()) b = (2 * Mean + Sqr(<) ^ 2 h- 4|(<3)SD:) /` 2 a- b Dim I As Lon@g For= 1 To number 'draw a rom on 0,1 interval aFMRG 'map to computed .HUniI)myAbja)wa Next I End Su[  Test hl10)zDouble CalDl NG( ), 10, 2.g, Max+, Min3BasicFo-H o= gs2@OK()~&&&>Qu2uawOct4 vers0`*-VcN(,resul2th,00Z rttimeT``N'g!P:=s, Typeof3@*:=-SD:=1 ', Palnativ e wayYpasrgu1[sdf{zdǢ`< & (9 'PowerPC G4; 500 MHz -> 17 second@!SU'_CSort algor@`!'anksDaa Mahoo'p AugQ16ipck s02 x`5Px(l: l P P lefnrYrighnp it'saaqbottb" x[l..r`,n] u7 mth column formparison ' n is the number ofs in`array xXmXthat will be used (B0is, lcsort(A) SB_eto ma@de it # decimal {s bylng severvables as Dou OIf youNketemp +iswap sub antePger,cspee wnY(coLs doin$g.ZToy, caqYwith8FIVE ametvaluRdescribed*1) T;p #ed. mustDIMmqarA' 2) l, standsLeft, firrowr;.u-y a 1"3) ?Righla4)eA 5)ǃ -U a = X(p CaMyPartiG pv, k, n$!k - 1U( E0If @+a1Func-&,fou 4Boolea A/I,OmidHdlex1e,h x2h3e checb:I"Fpb$' ^a ks3re`t `}twneqsEen_listEsto rp!case ithr hoailsEIncorpporat@EReferenc`s SedgewickHelpDavemh2yFH#9= Fals@!VI@`lWhi`5* AL < rC5f_IYcX(I +Wmda Truap" EFA ^AWen!1= I wI7(` >v hn 6 s5!'$pneedN!we h#7wo'xt!Dc d rBl >_he\5J@rmgqAЃdpnj := 0\1 >CN3_5r2o+1,o '=3/A1O6 Q:  O ?p&'A'1 =assign>p already<. s.' routinO GKq(0M)Lxﰈҁ)'p bl}-0P2?F 'Cu2m`lҫ,@`d tells 'ђRk%Bм 7ayw' jp¨@origin@pя wP P1U< `J(@ '*#wNNJ@O0 =rAelem0v''Jun$e C' A `0_7#C@SPXl Pn beca1p oP}nPC&#E3@Fmbig1!xq1F o %25'\ say7"pe$,#@ so ' we need to discarPhe re0sult(End If ElseIf x1 = x3 Then@(> x2(pRl ' pick8because it is bigger than;ld @r@2 @!knowH1 rax3O_2_2 _1_>(PqPnt}=w - Call swap(X,'@N$n>?S(HmWendCkAHrr qIEX, j?jhrCltemp C{For A [o vb= X(I, kh`X(jM@ Nex A BT Dorttesting( '`ad in}e data 'barray must`{ DIMmas aZabe compati\ withBqs ro"u e  myA (4, 3d !I@C=g$jeE #j)`Cells(17 SB.Valua) ! N cd , 3, 2%' OuPtputs `5) *?4a*6a)D DU DA D D DDDDDD٥DDDDDfDD'\ p>5\k>D )@P8D$'W\  >  출  $ p<$ c`%p$ p pp  ttt<$ p0   0   0   0   0 8  0   0   0 x  0   0   0    0   0  0  0 (   0  0   0 h  0 8$  0   0   0        prU 4$`$`$` $`$!`$I`8q`ak0`  ,` ++, ` ++4 ` k++$9 `,a ` ++( ` &0` k&4` 1k++$`,)` ++$Q`<y`Yiyk((((8`Yiyk(((P`$iy !((((((&&(H `Yiy1Ak((+(((81`YQak((($Y`nrU~|  ml ˀ Lqx L kLb L kLb Lt dDollt tl4lt kL]glt kLb4lt kL]tlt kLb tfDYollxkLbtl Lt d7lt kL4$Btl olodtdololo\t\U    NM+!"#$%&'()*,-./0123456789:;<=>?@ABCDEFGHIJKLOPjRSTVW`YZ[\]^_Qabcdefghikmnopqzstuvwxyl{|}~SDModule __SRP_c__SRP_d "LinkCheckerModule$ 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 macrolXF 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$$  'R<f set MAX and MIN to first valuee$$'0$$'D0b) First pass to get the mean, max, and min :$$  :$ '  0 :$ G :$'0j D :$ G :$'Dj  ' Second pass to get the SD'  :$$ :$  '      !"#$%&'()*+,-./012345679:;<=>?ACDEFGHIJKLMNPQRSTUVWXY[\]^_`abcdefghijklmnpqrstuvwxyz{|}~   " '"   $ '$( $ " "    '$$SD Corrected two-pass formula. $$F' 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. :$$  :$ ' L4  ' Second pass to get the SD'  :$$ :$  '   " '"   $ '$ $ " "    '$$SD Corrected two-pass formula. $$F' The SD is the sample SD.% The Population SD is Sqr(n/(n-1))*SDoH]+@B+](]@   A@&  $^ *A@"oX]' z'  z+h]]   A@& A@"$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 `TE^ZxME(6 < < 2x`Pk@--) x `I > fI the I0 ID@ `xs it`:t` lrd d`"d? I`$\ss a &` ;)n SD*SD iiget `in y`:M th` N, S`"e in`$F (X`  Z  M an`macr`C ,` g les ```xXH%`0Hp @XL H @0H .AH @D 0@4 @$ 0@  eeee! @ !0 % F eeee X OHK$*\Rffff*1W4ba89d32*\R0*#15u  H :X D L &0 FX  ,   "  @ JX L . $( JP   "  & H`  0x$)$) 08 Hhp   N ( 8 ,X "   "  " @X` p NP ` , "   ( H " P X h          Source:duB http://www.ulib.org/webRoot/Books/Numerical_Recipes/bookcpdf.html4 A clever way to minimize roundoff error, especially> 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 codeV+ 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 tf o\ododlx춯lxt\m\ kLb`l$ $0 x  0   0 X  \rU < `)9k////4 ` k++$ `$ `n</ ^|xMEP6 "LL<<+`k$$`0t`6:.%XH@XH``xpHphB8@p @l @(\ @L @2< @z( @@ @ @ X@ @ (xpOHK$*\Rffff*1Y4ba89d32$*\Rffff*1T4ba89cb0*\R1*#116*\R1*#337*\R1*#10d*\R0*#13$B   P0 ^ \ &@ 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]@ h!'0 (get all the links in the active workbook B%4'2,check to see if any links are to this add-in]X 2$8 z 2 z$2 0'6 6 ?Warning! This workbook is using formulas available only in the 0{ 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 zTd 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 __SRP_108__SRP_11@gFunctions ]__SRP_aBrU~|  H 1)Y1Ix9uIv<p$ >p1t.L '\%l$ Hl L<l< ((<5Iih1( @`  )@%+D`  >>iF@iH``iJPiLiNevio P  0(ge Rp2P`zX`:z` z` TWarn V@in t( Xxnsta` Zulas` th+b(`  xxPid0)fPX%)hx%)j% l n pH0`r`t`v`x`z`: V PH0 LMEMh T   ( H h  ``z`|`~` `  `d f% h% j%  ` GGid@ )f` h %)h %)j % l   n   pX @ `r`t`v`x`z`: V   PX @   x  T     8 X x  p` 44`F`J`H`L`N P      R(`z`:`` T V X0 ZhP `` **MI I I I `z`:`` % `  w)% H0P `zx( p:%@`0`x@d @T @P @H@ @0 OHK*\R1*#220$*\Rffff*1V4 B   D0P`h  ( $ $ $ $@ h 6    . 8 P "` F   0 H ` x          ^(     \  ( 0  8 fH     h H X ` h        (  @ `  \x        <  ` p x      ( "8`x      8PX$p &  "  @Hh"   D0@Xx  ( $ $ $@ $h  6    .0 ` x " F   8Xp0 H h x   "@X h x  $  H&X  $ "   &0HPp 0@P "`    0HP$h &   "      (8 ` .( 8 (H p   (    ( 0 @ P &h  4    B   F F` B P B@ 2 : !"! 0!@!H! h!!  !!!!sthis function takes a cell address, like $A$2, and outputs the name of the cell, if it's named, or the cell addressxp]` 4$!!'<es <G 4'<j <'i(xPrints out formula as text0Maja Sliwinski developed this for Simon Benninga c B@Ra<-- @!B %d'>>ati]]8he]xded]]]H] B@ N 8Absolute value of rho must be less than or equal to one.A@"invalid'D\FkP+P N+P N+P+P H+X F+Z L+X J+Z H SDs must be positiveA@"invalid'D\Fk` L SDs must be positiveA@"invalid'^\Fk Call choldc to get weights   + ' P A@` error'D\Fk`  $  +P  VA@ :'  :  : $P $V '   :$X :$Z :+T : T'D\PiH>Dim corrRange As Range, meansRange As Range, SDsrange As Range B@]]Ph]]]PDim 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 IftIf corrrow <> corrcol Then( MultiVarNormal = "Matrix wrong size" GoTo EndnowEnd IfIf corrrow <> meansdim Then@ MultiVarNormal = "Matrix doesn't agree with means dimension" GoTo EndnowEnd If dV d dP d  dT@ dn` dp d dl]] h!!N'z j!!N'| f!!N'~ f!"!N' z dTMeans array must have the same number of rows as the specificed number of variables.A@"error'b\FkX | dRSDs array must have the same number of rows as the specificed number of variables.A@"error'b\Fk ~ d[Correlation matrix must have the same number of rows as the specificed number of variables.A@"error'b\Fk0  d^Correlation matrix must have the same number of columns as the specificed number of variables.A@"error'b\Fk z dMsgBox meansRange.Address z h% z+n z j% z+p z$p SDs must be positiveA@"error'b\Fk : d z : f% z :+l z :$l QCorrelation matrix must have entries less than or equal to one in absolute value.A@"error'b\Fk z : z :$l2Correlation matrix must have ones on the diagonal.A@"error'b\Fkkx : z z d : z z :$l z :+P z :$P : z+P : z Call choldc to get weights  d  +  P d A@` error'b\Fk  d $  +P  VA@ : d'  :  : $P $V '   :$p :$n :+T : T'bMsgBox MultivarNormal(1)\i(]h'd MCResults$.I22:K24$.f f!4A@"M22:M24$.hN22:N24$.j d f h jAo >Dim corrRange As Range, meansRange As Range, SDsrange As Range B@]  ( ]` x   ]  ] ]( `  Dim 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 dV  d dP  d0  dTP  dnp  dp  d dl z d h!4A@" z h% z+n z j% z+p : d z : f% z :+l : z z d : z z :$l z :+P z :$P : z+P : z Call choldc to get weights  d  +  P d d A@`  t $  +P  VA@ : d'  :  : $P $V '   :$p :$n :+T :MutltiVarNormal = ResultArray@o8 ]  0 H ` ]x ]  ]0H`]x]]8'F'J'H'L?'N+P N+P N+P+P H+X F+Z L+X J+Z Call choldc to get weights   +  P A@`  $  +R  VA@ :'  :  : $R $V '   :$X :$Z :+T :$TA@"$TA@"opDim n As IntegerDim np As Integer!Dim a(1 To np, 1 To np) As DoubleDim p(1 To n) As Double']0]H]`]x](Set mySheet = Worksheets("CholeskyTest") z  : C6) z :$':P z '  d  z $ : $ '  'x z :8  :PLImpossible Correlation MatrixA@"'\ k $F z+$d  z$ : z+k :p zFor i = 1 To nk 'mySheet.Cells(i, 1) = p(i) For j = 1 To n- mySheet.Cells(i + 1, j + 1) = a(i, j) Next jNext i@\o@qsort(X() As Double, l As Long, r As Long, n As Long, m As Long)%? To use, call qsort with the FIVE parameter values as described< 1) The array to be sorted. This must be DIMmed as a Double.WI 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] !!Np load array]x z !!N z$!0 z+p  z A@ 'i@8ܶAttribute 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 GetFormula(RngPE Prin f ap'Maja Sliwinski developedÀSimon Bennin ga Ap plica.Volle True%A<-- " & T@,./Local , "");BiVarN1al( mean1 `) `DAIMsg Box "@olvalue x4must be lthanwequal to one.@aCNin@ idGo9leave2:LIfiB=, 1)1 >`1, 2 ? (`P g'!T c"S|D2g"BUaX< 0o$SDs %"positive3  {0vEr.b.' Call choldc9getwgk1"p#rD(k*k Q= 0 `75, ", r*IOlEBD)$ Ek, CRNG pK, 0joc|Y*j8`uI`= + j}* %c%j;)*}`+ EWjv %9 iOjb M iF"(P9rOpcorrrkN, !1sM/rvvWf['zzbsoLti`meL_ (* cS row Long, cold@U#s}c XA &'/ ry9, 9" Pm0ysheP: WolrkS = S!s("MCs!}P= UBound(01&'W()N&<>P/s7*$f0Ч wr` sizAW'Bqnow0M'  @"Max/ /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/Shsa number` "speci8fic_ia0sٚka)Ix5횷  :a = @  # CqeQՀ+rix_ _ N_ _ W &%<> nvars Then MsgBox "Correlation matrix must have the same number of columns as:peci`ficedFiables."!ultiNormal = " error!Go To lex: End If @ For I-1. R'meansRange.Addressc(I):"C0ells SD7r!If < 0gbe posiqez,|jN|cڀ(I, jq` rCdAbs(J ) >  "en@es@ksan iequvto one i n abste valueɂރA ʄEH7݁jhJ< v< 4s6diagonal4 h 5Next j @ BIA@ HAj, yfat#K%' Call ch oldc =get weightoakVr(k!Uk  D%`,, , r 7c FvQak, CGRNG !Array, 1, 00qs ~0C'!82+a(/`*g@&H0@&Res (*  M@* A(1) iFunc Sub@Test`DimC9 As Integer,9rrⓡ, Dz"F #= 3@MmysDhe= Ss("MCs"NS@~ = ("I22:K24!aMMAfNNBcF#V.(!,7a@,, #+c 0W{'ee ApplDic.Vole (True6)aDou(*`  rowLong0r dP' #sIj/f>/ r9, 4* Work+@7&,,Pq= UBound|)pHx, 2(@X&s=c'L@f.O"MH a wr` sizQ"ZqP.now .q'ߐ rpM///G"??doesn't agree Pwith e\nsг@ ' Re,W&posRPq&g&r('c mcZŷ1m tU"G( ##P.owCu#q /4'qr` {xjc lsVAFQ pC o 6ooT= ooy vok`'1o=o'Mut J[? Ix Bדy(@uble Dim I As Integer, j hk4resHultXDoR*Array(1@ To 2)D,` Norm8USDvec9Mea"n m1 (= 0 2 SD1 SDHrho.5'at(1, 1) m2^= S3 NG;WA2 ' Call choldc to get weights `For kQ D r(k2k Next L c, S, r,(k, "&galRNGj@E, 0@+}#j#Á_',j_,ċS +E9(j"*k) 8Ij?* b+ `j@]sgBox ʥ M2End xSub:Dh(a, n, erTrop 'ntpaCnpSAtnpH pJ^IICOsum$mysheNWorkS@HmySB= &s("CTesky`Test"=I%Bn6aDd!@= a(I@Aa[I - jEWhile_>azoC- X*_ BH k endIf=`8The(a < 0  DF"Im possi Correlation Matri.Cells(i`Tp(i@" ;A, + j!^aa r'i !: !$pFuncB+So rtAsc7ing(myRangeAY@qs(X(f, lLongGubAB2use, c! with the FIVE parameter values as describe Kf'G a߁bDe ed.Jis must @DIMmeda q.`l, stands f`Left, Afirrow numbinB ; usuy a!i' 3)6 h Raʆ law 4) n  of col`umns 58) m3 at! from vl nhULmyNRerT.Rows.Co,unp1loaqO+R+fJ?C(ItJ1).Vr !$2,E0L>*+6-- 0   0 (  0   0  0 p  0   CrU ( __SRP_bO_VBA_PROJECTX.dirZ-__SRP_0r?`0, `=< ` !1IY+++++H `i&===$! `HI `i&===$q `8 `!////, `=n)0* pHd MCSimAddIn4@j = r OHK J< rstdole>stdole h%^*\G{00020430-C 0046}#2.0#0#C:\WINDOWS\syst em32\e2.tlb#OLE Automation`MSForms>SFFrms3ED452EE1-E08F-@101A-8-02608C4D0BB4EFM20.DLL#Microsoft : ^ Object Library/;D1u00}t#0#k04 AX%IE7)l 8bLerrorUformat(Randomwidth Excel+ VBAWin16~Win32MacVBA6# MCSimAddInstdole`MSFormsC RefEditOfficeu MainModule  _Evaluate (ShowMCForm]A Application*ReferenceStyle9xlR1C1pMsgBoxRpromptmTitle~Buttons] vbCritical+} MCFormIRefEditOneCell;ValueK ActiveCellAddressShow(AddMCSimMenuItemja MCSimIsHeren CommandBars VisiblemyTools='OperatingSystem[ InternationalxlNonEnglishFunctionsInputBox ControlsKCount0vCaptionxnewItemICommandBarControlPAddrmsoControlButtonnOnActionresponseChrK~ vbInformationn(test4(testversionnumberVersion ThisWorkbook|Workbook_AddinInstallWorkbook_AddinUninstallDeletex Sheet1 Sheet3CellValidStringTestCommandButton1_Click ErrorHandlerOI` FirstCelluFirstCellArray SecondCell$SecondCellArray>myrepsPctdone mystarttime yoursettingFxmeanxSD!xmaxxmin;x2mean-x2SD@\x2maxHx2minf ViewTableAllLong histRangeO&Range  myendTimew1t_w2u_ii]jj]kk]aXCZ MCRawRangeExmyOriginalSheetName$myCurrentMCSimSheetmyCurrentStringmyStartPosition* myEndPositionS Err13Counter)CellStringTestFirstCellParentexternalwFormulaqY vbTextCompare QRefEditTwoCell~CellStringTestSecondCell` myTwoCellNamex( CellNameο myOneCellName0myFirstCellInitialValueEmySecondCellInitialValue@myCellDoesNotChangeWarning[Sheets  CalculatevbYesNovbNo% myrepsentered TextBoxRepsKo IsNumeric$*myrepsdQNow% CalculationhxlCalculationManualScreenUpdating! OptionButtonRAND ActiveSheet%NCellsReplacefWhatC ReplacementLookAtOxlPart< SearchOrderxlByRows MatchCaseOptionButtonFMRG+SetSeedTextBoxK (myFMRGIntlag1  (myFMRGIntlag2! FrameProgress CheckBox1 ActiveWindow+RangeSelection7~RowsUMCRawhmyMCRawVarCounteAreasKColumnsp9SheetLoopNumber  continue3rmyMCRawSheetName\CalcBookCheckBox) LabelProgress}WorksheetFunction&MaxȲ (BasicFour` WorkbooksBCopyƿBeforeSheetLoopNumberMCSim? continue4sDeletePreviousCheckBox!Y DisplayAlertsActiveWorkbookMinSqr((Log107HDisplayGridlinesCharts ActiveChart  ChartType?xlXYScatterLinesNoMarkersvLocation-Where֍xlLocationAsObject SetSourceDataaSourceGShapes< ChartObjectsHTop5Height|PlotArea SelectionZInterior; ColorIndexxlNoneLa  *\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.6#0#C:\Program Files\Microsoft Office\Office12\EXCEL.EXE#Microsoft Excel 12.0 Object Library*\G{00020430-0000-0000-C000-000000000046}#2.0#0#C:\WINDOWS\system32\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{296F3057-851D-4C0A-9E43-E26EA8BBF718}#2.0#0#C:\DOCUME~1\Depauw\LOCALS~1\Temp\VBE\MSForms.exd#Microsoft Forms 2.0 Object Library.E .`M *\G{00024517-0000-0000-C000-000000000046}#1.2#0#C:\Program Files\Microsoft Office\Office12\REFEDIT.DLL#Ref Edit Control*\G{BA2147BC-7BB6-4548-B868-94E00BE1D7D7}#1.2#0#C:\DOCUME~1\Depauw\LOCALS~1\Temp\VBE\RefEdit.exd#Ref Edit ControlEF&*\G{2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}#2.4#0#C:\Program Files\Common Files\Microsoft Shared\OFFICE12\MSO.DLL#Microsoft Office 9.0 Object Library   OHK  ^ MainModule1S4ba89cb0MainModuleH`!ThisWorkbook1T4ba89cb0iThisWorkbook^ Sheet11I4ba89cb0q Sheet1^0- Sheet31J4ba89cb0s Sheet3ZH- MCForm1U4ba89d1d- MCFormh]` Sheet21Z4ba89d33 Sheet2ZxHistogram1X4ba89d32HistogramG RNGandSortModule1R4ba89cb0 RNGandSortModuleZ0wSDModule1W4ba89d32SDModuleZK"LinkCheckerModule1Y4ba89d32."LinkCheckerModule|5 Functions1V4ba89d32:FunctionsJ`0Hx)&gA,SjAoHM6x\$/FO~:Ys+@OUBl]kG$:xUBD}Dq847U- SC[ړsO5B!jɄ!X F;1gT࿆'BiZMzegendPAxesbxlValueOne of the cells you selected,   or E , did not change when the sheet was recalculated. That cell may not be appropriate for a Monte Carlo analysis. Do you want to proceed?ҋB ` OneVarMCSim MCSim.xlaF Before! ~The number of repetitions must be an integer greater than zero.CҋB ` RANDOM() RAND() CellsWhatReplacement LookAtSearchOrderMatchCaseReplace $MCSIM.XLA!RANDOM()ƏYl| 4You cannot have more than  MCSimR B 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`F 8You have selected more than ExampleExample2CopyQ > 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 MCRaw Value Range 0%pj).` BasicFourMeanSD Delete SeedLimitsHistogramMaker1VarHistogramMaker2VarMakeBins FindPrecisionLog10FormatSumStatsDriveFormatting NewLimits  B2 AL1F aa1:ab3 EmpHist Chart 1 ShapesColumnsLeftChartObjectsRowsTop Height widthInteriorColorIndex Select Border WeightLineStyleMajorTickMarkMinorTickMark "TickLabelPositionMajorGridlines "HasMajorGridlines "HasMinorGridlines EmpiricalxF TwoVarMCSim C2 I4 K4 AM1 aa1:ac3o 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. Min K15 :Existing Sheet Name Requested MCSim Number5 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 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. `Existing Sheet May Not be MCSim Sheet CompatiblefĤIx8@}/R KQDBTimes New Roman8KEFҋB `EF8KEFҋB `EF `, #FrameProgressanG074CommandButton1nGS0 ;4CommandButton2nGg S$#Frame1$/#Frame2P 9<SetSeedTextBoxnG1Enter the number of repetitions,:pCalcBookCheckBoxE\ ;l0CheckBox1hec Select cells on sheet, then execute Tools: MCSim( <pCheckBox2hec; (=2@ Label64>l DeletePreviousCheckBox (ProceedqNqTahomad(CanceldENqTahomadH,{ 5Times New RomanGHF{0 Recalculate the Entire Workbook 5Times New RomanGDF{0 Record All Selected Cells 5Times New RomanGHF{0 Output to Existing MCSim Sheet 5Times New RomanG(Set Seed# 5Times New RomanGDF{0 Delete Previous MCSim Sheet 5Times New RomanGi02* n``@A@Afo)+0CompObjp<H   }S"Progress BarR KQDBTimes New Roman80 20LabelProgress "1Tahoma4 n``Microsoft Forms 2.0 FrameEmbedded ObjectForms.Frame.19q8H  ' }SRequiredR KQDBTimes New Roman(P i26(8. n``@A@Afo-/$i3331 n``@A@A@ RefEditOneCell{Click on the cell to be analyzed(2HLabel3L DTextBoxRepseqEnter the number of repetitionsd(2XLabel5$!#Frame3  H${ 5Times New Roman ( Select a cellanq 5Times New Roman@H,{1000 5Times New Roman0(Enter the Number of Repetitions 5Times New Roman<H  $ }qChoose RNGRoR KQDBTimes New RomanL "XOptionButtonRAND{Uses Excel's RAND function\ #\,OptionButtonFMRG"Uses Barreto/Howland random number generator, $\OptionButton1MRG ffo02CompObjpCompObjp0Fp ,0 All RAND 5Times New Roman4F ,0 All RANDOMRo 5Times New Roman4F ,1 No ChangesRo 5Times New Roman n``Microsoft Forms 2.0 FrameEmbedded ObjectForms.Frame.19q n``Microsoft Forms 2.0 FrameEmbedded ObjectForms.Frame.19q8H  8%}SOptionalR KQDBTimes New Roman(02LLabel4NDX 1@%RefEditTwoCellnG{Click on a second cell to be analyzed $(Select a second cell  5Times New RomanGH$F 5Timei476 n``@A@Af o57CompObjps New RomanG n``Microsoft Forms 2.0 FrameEmbedded ObjectForms.Frame.19q Microsoft Forms 2.0 FormEmbedded Object9qVERSION 5.00 Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} MCForm Caption = "Monte Carlo Simulation" ClientHeight = 3915 ClientLeft = 45 ClientTop CompObj49aVBFrame/PROJECTwm^PROJECT= 345 ClientWidth = 6945 StartUpPosition = 1 'CenterOwner TypeInfoVer = 147 End MainModuleMainModuleThisWorkbookThisWorkbookSheet1Sheet1MCFormMCFormSheet2Sheet2Sheet3Sheet3RNGandSortModuleRNGandSortModuleSDModuleSDModuleHistogramHistogramLinkCheckerModuleLinkCheckerModuleFunctionsFunctionsID="{AB8AEE19-FD9C-11D2-9CEA-20F807C10627}" Module=MainModule Document=ThisWorkbook/&H00000000 Document=Sheet1/&H00000000 Package={AC9F2F90-E877-11CE-9F68-00AA00574A4F} BaseClass=MCForm Document=Sheet2/&H00000000 Document=Sheet3/&H00000000 Module=RNGandSortModule Module=SDModule Module=Histogram Module=LinkCheckerModule Module=Functions Name="MCSimAddIn" HelpContextID="0" VersionCompatible32="393222000" CMG="F4F6F25532FA36FA36FA36FA36" DPB="9D9F9B0CEDB4EEB4EEB4" GC="464440E34025E826E82617" [Host Extender Info] &H00000001={3832D640-CF90-11CF-8E43-00A0C911005A};VBE;&H00000000 [Workspace] MainModule=22, 29, 636, 411, Z ThisWorkbook=0, 0, 0, 0, C Sheet1=0, 0, 0, 0, C MCForm=132, 174, 746, 556, , 22, 29, 888, 563, C Sheet2=0, 0, 0, 0, C Sheet3=0, 0, 0, 0, C RNGandSortModule=110, 145, 724, 527, 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 EF"EFPROJECTlk;:6SummaryInformation(>xDocumentSummaryInformation8CompObjrOh+'0H hp  ( 4@ Monte Carlo Simulation Add-inBarreto/Howland$Monte Carlo Simulation 28 May 2009Humberto Barreto@b%13@AL@A՜.+,0HP X`hp x  MCSim4 OneVarMCSim TwoVarMCSim  Worksheets F&Microsoft Office Excel 2003 WorksheetBiff8Excel.Sheet.89q