ࡱ> 0S%~$Times New RomangressW)074CommandButton1O5%0 ;4CommandButton2g 5%$#Frame1$#Frame2u$%#Frame4( 0tCheckBox1tto!  Microsoft Forms 2.0 FormEmbedded Object9qi272 n``e#e#f o13CompObjp \pHumberto Barreto Ba= ThisWorkbook=hxL,<X@"1Arial1Arial1Arial1Arial1Arial1.Times New Roman1$Arial1 Arial1.Times New Roman1.Times New Roman1.Times New Roman1.8Times New Roman1. Times New Roman1.Times New Roman1. Times New Roman1.Times New Roman1. Times New Roman1.Times New Roman1. Times New Roman1. Times New Roman1. Times New Roman"$"#,##0_);\("$"#,##0\)!"$"#,##0_);[Red]\("$"#,##0\)""$"#,##0.00_);\("$"#,##0.00\)'""$"#,##0.00_);[Red]\("$"#,##0.00\)7*2_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"_);_(@_).))_(* #,##0_);_(* \(#,##0\);_(* "-"_);_(@_)?,:_("$"* #,##0.00_);_("$"* \(#,##0.00\);_("$"* "-"??_);_(@_)6+1_(* #,##0.00_);_(* \(#,##0.00\);_(* "-"??_);_(@_)"C"#,##0_);\("C"#,##0\)!"C"#,##0_);[Red]\("C"#,##0\)""C"#,##0.00_);\("C"#,##0.00\)'""C"#,##0.00_);[Red]\("C"#,##0.00\)72_("C"* #,##0_);_("C"* \(#,##0\);_("C"* "-"_);_(@_)?:_("C"* #,##0.00_);_("C"* \(#,##0.00\);_("C"* "-"??_);_(@_)A<_("$"* #,##0.000_);_("$"* \(#,##0.000\);_("$"* "-"??_);_(@_)C>_("$"* #,##0.0000_);_("$"* \(#,##0.0000\);_("$"* "-"??_);_(@_)=8_("$"* #,##0.0_);_("$"* \(#,##0.0\);_("$"* "-"??_);_(@_)94_("$"* #,##0_);_("$"* \(#,##0\);_("$"* "-"??_);_(@_)E@_("$"* #,##0.00000_);_("$"* \(#,##0.00000\);_("$"* "-"??_);_(@_) 0.000000000 0.00000000 0.0000000 0.000000 0.00000 0.0000 0.000 0.0%0.0 0.0000000000 0.000000000000.0000000000000.00000000000000.000000000000000.0000000000000000.00000000000000000.000000000000000000.0000000000000000000.00000000000000000000.00000000000000000000 0.0E+00 0.000E+00 0E+00 #,##0.0 #,##0.000 #,##0.0000 #,##0.00000 #,##0.000000 #,##0.0000000#,##0.00000000"Yes";"Yes";"No""True";"True";"False""On";"On";"Off"],[$ -2]\ #,##0.00_);[Red]\([$ -2]\ #,##0.00\)                + ) , *        ! " a     q *0  *8 (0 ( a( " "  (   (@ @  (!@ @   ,!@ @  (!!@ @ "    ,  @  $@   (@ @  ,@ @  (    @ @  $@ @    !@ @  $!@ @     a   ! "    *8  *8 "   !!@ @  (@ @  ,!@ @  (@ @  ,!@ @  ,!@ @  ,!@ @     (!@ @  ,!!@ @  (!@ @  ,!!@ @        (   (   (  (@  (  @  (   (   (  (@   (@   ( @  a (@  a (    "8"@@  "8"@  "8 " @  "8"@@  "8"@  "8 " @  *8@ @ q*0!@ @ "8@@ "8  @ "8@@ "8  @  *8!@ @  Normal_AutoCorr Normal_MCSimNormal_MonteCarlo8 ``i̜̙3f3333f3ffff333ff333f33f33BBB\` YDoc"OneVarBootstrap&gTwoVarBootstrap7myBootDataSheet". Sample NumberSimulation Stats repetitionssecondsSummary StatisticsNotesAverageSDMaxMin?Only the first 100 repetitions are displayed on this worksheet.Var1Var2secs9This add-in enables boostrapping from any Excel workbook.Results of Bootstrap5Fixed Second Cell (hadn't modified it from MCSim.xla)@Added a draw again feature if the cell being tracked is an erroroAdded a faster method which puts the data down in the data sheet of this add-in instead of in the user's sheet.sThis way, Excel does not recalculate the user's sheet which may be loaded with junk and slow user-defined functions'Added code to allow fewer than 50 reps.VAdded code to check to make sure user realizes that existing data will be overwritten.:Added the number of bad samples drawn reported to the userfAdded the ability to record a lot more cells (max 256) and display the full results in a BootRaw sheetAdded Oct-2004 RNGandSortModuleFixed Symbol font problem$Modified chart method a la MCSim.xlaCFixed problem with wrong color coding of chart in a 2 var histogramJLog10 function in Histogram sheet and fixed language ID test on auto open.Foreign language uninstall fix"T sq)ccb  "O!!  dMbP?_*+%"??U} $ "    ~ T @ ~ T@ ~ T@     ~ T@ ~ T@ ~ T@  ~ T@ ~ b@ b~ b @ 2 !~ b@ !D>@"""7 Sheet1   g%ATcf  dMbP?_*+%;M\\scholar\baxter_mita-q4C odXXLetterDINU"4"d??U} $ %}  } } } m} } I } }  $ } I } I } I}  } I } I }  I } !*I } +1I } 2I Estimated rho in SUsgBBv BH# BJ BJ B;B;B;BJ B; B; B; B; B; B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;B;  !#$%&*&2A  !" ij$$$$$$$($$$$$$$$$$ ~ !$ "$$#$$$%>&$$$$$$$$$$$$$$$$$$$$$A~ %?& '( )!!?~ "#$!?~ %&*&2A~ %@& '* + c  de  f  gh ,!!?~ "@#$!?~ %@&*&2A~ %@& -.- / 0|u? 1]UV ,!1?~ "@#$!?~ %@&*&2A~ %@& 2 3 0n? 4`aV !!?~ " @#$!?~ %@&*&2A~ %@&5 6 7>i@ 1^WV !@)?~ "@#$1?~ %@&*&2A~ %@&5 9  :lSQ? ;_[\ !1?~ ""@#$1?~ % @&*&2A~ %@&<-=---- !9?~ ""@#$!?~ % @&*&2A~ % @ & < '=----  , !!?~ "(@ # $!?~ %@ &*& 2A~ %"@ & -=----  , ! ?~ " @ # $@)?~ %@ &*& 2A~ %$@ & ------  , !@)?~ "@ # $@)?~ %"@ &*& 2A~ %&@ & ------  !`?~ "@ # $1?~ %"@ &*& 2A~ %(@ & > ------  !1?~ "@ # $1?~ %"@ &*& 2A~ %*@&>------ !?~ "@#$9?~ %"@&*&2A~ %,@&>------ !9?~ "@#$9?~ %(@&*&2A~ %.@&------ !?~ "@#$!?~ %(@&*&2A~ %0@&------ !!@~ "@#$!?~ % @&*&2A~ %1@&------ !c@~ "@#$ ?~ % @&*&2A~ %2@&------ ! @~ "@#$ ?~ %@&*&2A~ %3@&------ !0@~ "@#$@)?~ %@&*&2A~ %4@&------ !@)@~ "?#$@)?~ %@&*&2A~ %5@&!Pk@~ "#$`?~ %@&*&2A~ %6@&!`@~ "#$`?~ %@&*&2A~ %7@&!p@~ "#$1?~ %@&*&2A~ %8@&!1@~ "#$1?~ %@&*&2A~ %9@&!s @~ "#$?~ %@&*&2A~ %:@&! @~ "#$?~ %@&*&2A~ %;@&! @~ "#$9?~ %@&*&2A~ %<@&!9 @~ "#$9?~ %@&*&2A~ %=@&!{ @~ "#$?~ %@&*&2A~ %>@&!@~ "#$?~ %@&*&2ADl B;!B;"B;#B;$B;%B;&B;'B;(B;)B;*B;+B;,B;-B;.B;/B;0B;1B;2B;3B;4B;5B;6B;7B;8B;9B;:B;;B;<B;=B;>B;?B;~ %?@ & !@~ " # $!@~ %@ &*& 2A~ !%@@!& !!#!$!@~ !%@!&*&!2A~ "%@@"& "!#"$c@~ "%@"&*&"2A~ #%A@#& #!##$c@~ #%@#&*&#2A~ $%A@$& $!#$$ @~ $%@$&*&$2A~ %%B@%& %!#%$ @~ %%@%&*&%2A~ &%B@&& &!#&$0@~ &%@&&*&&2A~ '%C@'& '!#'$0@~ '%?'&*&'2A~ (%C@(& (!#($@)@~ (%?(&*&(2A~ )%D@)& )!#)$@)@~ )%)&*&)2A~ *%D@*& *!#*$Pk@~ *%*&*&*2A~ +%E@+& +!#+$Pk@~ +%+&*&+2A~ ,%E@,& ,!#,$`@~ ,%,&*&,2A~ -%F@-& -!#-$`@~ -%-&*&-2A~ .%F@.& .!#.$p@~ .%.&*&.2A~ /%G@/& /!#/$p@~ /%/&*&/2A~ 0%G@0& 0!#0$1@~ 0%0&*&02A~ 1%H@1& 1!#1$1@~ 1%1&*&12A~ 2%H@2& 2!#2$s @~ 2%2&*&22A~ 3%I@3& 3!#3$s @~ 3%3&*&32A~ 4%I@4& 4!#4$ @~ 4%4&*&42A~ 5%J@5& 5!#5$ @~ 5%5&*&52A~ 6%J@6& 6!#6$ @~ 6%6&*&62A~ 7%K@7& 7!#7$ @~ 7%7&*&72A~ 8%K@8& 8!#8$9 @~ 8%8&*&82A~ 9%L@9& 9!#9$9 @~ 9%9&*&92A~ :%L@:& :!#:${ @~ :%:&*&:2A~ ;%M@;& ;!#;${ @~ ;%;&*&;2A~ <%M@<& <!#<$@~ <%<&*&<2A~ =%N@=& =!#=$@~ =%=&*&=2A~ >%N@>& >!#>$@~ >%>&*&>2A~ ?%O@?& ?!#?$@~ ?%?&*&?2ADZl@B;AB;BB;CB;DB;EB;FB;GB;HB;IB;JB;KB;LB;MB;NB;OB;PB;QB;RB;SB;TB;UB;VB;WB;XB;YB;ZB;[B;\B;]B;^B;_B;~ @%O@@&@!*&@2A~ A%P@A&A!*&A2A~ B%@P@B&B!*&B2A~ C%P@C&C!*&C2A~ D%P@D&D!*&D2A~ E%Q@E&E!*&E2A~ F%@Q@F&F!*&F2A~ G%Q@G&G!*&G2A~ H%Q@H&H!*&H2A~ I%R@I&I!*&I2A~ J%@R@J&J!*&J2A~ K%R@K&K!*&K2A~ L%R@L&L!*&L2A~ M%S@M&M!*&M2A~ N%@S@N&N!*&N2A~ O%S@O&O!*&O2A~ P%S@P&P!*&P2A~ Q%T@Q&Q!*&Q2A~ R%@T@R&R!*&R2A~ S%T@S&S!*&S2A~ T%T@T&T!*&T2A~ U%U@U&U!*&U2A~ V%@U@V&V!*&V2A~ W%U@W&W!*&W2A~ X%U@X&X!*&X2A~ Y%V@Y&Y!*&Y2A~ Z%@V@Z&Z!*&Z2A~ [%V@[&[!*&[2A~ \%V@\&\!*&\2A~ ]%W@]&]!*&]2A~ ^%@W@^&^!*&^2A~ _%W@_&_!*&_2ADl````````````````````````````````B;aB;bB;cB;dB;eB;fB;~ `%W@`&`!*&`2A~ a%X@a&a!*&a2A~ b%@X@b&b!*&b2A~ c%X@c&c!*&c2A~ d%X@d&d!*&d2A~ e%Y@e&e!*&e2A f? x``````P>@    7 Sheet4   gl~U  dMbP?_*+%;M\\scholar\baxter-q? odXXLetter.HP LaserJet 4100 PCL 62exeM+Da6Xx\ql,,PJ)3).Y K vXyy9?_hm 2) ?$}khR 8e@}Ȭ-H/3AH?4~=&2{.d9YaqacAᆃGn Q= ------  !' 6A~ %*@ DE>------ !'6A~ %,@ DE>------ !'6A~ %.@ DE------ !'6A~ %0@ DE------ !'6A~ %1@ DE~ S?------ !'6A~ %2@ DE------ !'6A~ %3@ DE------ !'6A~ %4@ DE------ !'6A~ %5@ DE!'6A~ %6@ DE8!'6A~ %7@ DE!'6A~ %8@ DE!'6A~ %9@ DE!'6A~ %:@ DE!'6A~ %;@ DE!'6A~ %<@ DE!'6A~ %=@ DE!'6A~ %>@ DE!'6AD`ljrvvvlvvvllzlllV`VVVVVVV B;!B;"B;#B;$B;%B;&B;'B;(B;)B;*B;+B;,B;-B;.B;/B;0B;1B;2B;3B;4B;5B;6B;7B;8B;9B;:B;;B;<B;=B;>B;?B;~ %?@ DE !' 6A~ !%@@ !DE!!'!6A~ "%@@ "DE"!'"6A~ #%A@ #DE#!'#6A~ $%A@ $DE$!'$6A~ %%B@ %DE%!'%6A~ &%B@ &DE&!'&6A~ '%C@ 'DE'!''6A~ (%C@ (DE(!'(6A~ )%D@ )DE)!')6A~ *%D@ *DE*!'*6A~ +%E@ +DE+!'+6A~ ,%E@ ,DE,!',6A~ -%F@ -DE-8-!'-6A~ .%F@ .DE.!'.6A~ /%G@ /DE/!'/6A~ 0%G@ 0DE0!'06A~ 1%H@ 1DE1!'16A~ 2%H@ 2DE2!'26A~ 3%I@ 3DE3!'36A~ 4%I@ 4DE4!'46A~ 5%J@ 5DE5!'56A~ 6%J@ 6DE6!'66A~ 7%K@ 7DE7!'76A~ 8%K@ 8DE8!'86A~ 9%L@ 9DE9!'96A~ :%L@ :DE:!':6A~ ;%M@ ;DE;!';6A~ <%M@ <DE<!'<6A~ =%N@ =DE=!'=6A~ >%N@ >DE>!'>6A~ ?%O@ ?DE?!'?6ADJ lVVVVVVVVVVVVV`VVVVVVVVVVVVVVVVV@B;AB;BB;CB;DB;EB;FB;GB;HB;IB;JB;KB;LB;MB;NB;OB;PB;QB;RB;SB;TB;UB;VB;WB;XB;YB;ZB;[B;\B;]B;^B;_B;~ @%O@ @DE@!'@6A~ A%P@ ADEA!'A6A~ B%@P@ BDEB!'B6A~ C%P@ CDEC6A~ D%P@ DDED6A~ E%Q@ EDEE6A~ F%@Q@ FDEF6A~ G%Q@ GDEG6A~ H%Q@ HDEH6A~ I%R@ IDEI6A~ J%@R@ JDEJ6A~ K%R@ KDEK6A~ L%R@ LDEL6A~ M%S@ MDEM6A~ N%@S@ NDEN6A~ O%S@ ODEO6A~ P%S@ PDEP6A~ Q%T@ QDEQ6A~ R%@T@ RDER6A~ S%T@ SDES6A~ T%T@ TDET6A~ U%U@ UDEU6A~ V%@U@ VDEV6A~ W%U@ WDEW6A~ X%U@ XDEX6A~ Y%V@ YDEY6A~ Z%@V@ ZDEZ6A~ [%V@ [DE[6A~ \%V@ \DE\6A~ ]%W@ ]DE]6A~ ^%@W@ ^DE^6A~ _%W@ _DE_6AD lVVV>>>>>>>>>>>>>>>>>>>>>>>>>>>>`B;aB;bB;cB;dB;eB;fB;~ `%W@ `DE`6A~ a%X@ aDEa6A~ b%@X@ bDEb6A~ c%X@ cDEc6A~ d%X@ dDEd6A~ e%Y@ eDEe6A f? x>>>>>>P>@  "   7 Sheet5  }  dMbP?_*+%MBaxter Mita PrinterS odXXLetterPRIV0''''\KhC0'ϊ"dXX??U~ ~ ~ ~ ~ ~ d>@7 Sheet2 ThisWorkbook!Sheet1"?__SRP_10R__SRP_11VB  !"#$%&'()*+,-./0123456789:;<=>@ABCDEFGHIJKLMNOPQSTUWYZ[\]^_`abcdefghijlmnprstuvwxyz{|}~l sO a0#{OB>NdFiHmDAIxME [LSS<4<8<<<<< <$<(<,<0<<<N0{00020819-0000-0000-C000-000000000046}PX @`|@T  `DX `"%@  %8 %@pH,.t p @` @P @@ (0 , @(  @ @ @ @ @ @ ison@ er o@ ay x@@ecD/ " &0  " 8  8PX  Dx$`  Hm  Put the Bootstrap menu item inA@check languagessag] $!&$Wdydox@1 Delete the new menu command from the Tools menu.0Thanks to John Talyor n $%lWTools'"=it's not English, try to get the word for Tools from the userto gThe 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 $%0'" TOOkPdHTools'" k0 Bootstrap ... "Worksheet Menu Bar $%2%4%4B@6o(NAttribute VB_Name = "ThisWorkbook" Bas0{00020P819-0C$0046} |GlobalSpacFalse dCreatablPredeclaIdTru BExposeTemplateDerivBustomizD2Sub _AddinInstall() ' Put the Bootstrap menu item iRnsdd M I End G.2Uni3Dele5newA2comma"fromBToolBs . On Error ResuNextC.check languagSDim myAs Sng If Left(Application.Oper@ngSys4, 1)W" The@='Thanks to John Taly orInternal(xlNonEngli@shFuncAs) <> AbDA5)AA<'it'@s not , trygehword fB8Luse&E¤ K*putBox(F"8L of]is@zB2Exc@el doe$a ppearA#be(US. PleMAeAe )tha1Busi$ng.onB mainCacros#Ztop$e screen. D F :( .h " ,    0 H h $p    8 8 "P" x6 /3  0 68 &p "   *"2 K0 T)a0jxMEl@ Lf6 %00(@0T (7@D 4 @$ 0 @ x 0(@x HnjnrFxp @` 8p\ `T @D @4 @$ @ @ @ @ (@ @ ( @ @ 0,.j $p `*@ ( @ ( xF(`l @\ @L @< @, @  @ @ @   @ X(  (@  Ph (  ( , ( x @h (d @` @P L @< @, @ ( @  p  @  @  X 8  X ` @P @@ @0 @ @   @X @ @   @x @h (d @` @P @L @< X@, PPP;p D$R    ,S( 6X  <n Js P( x  @   6   ( >v0  p   x      $   z[ X 0h  ` " N  "     0  H X  x  &  &  " <[   DOCUMENTATION1 This add-in does a bootstrap of one or two cells from ANY Excel workbook INITIAL DATE: 16 Mar 2004 REVISIONS:new me- To see complete documentation, see Doc sheetlang User Input; It is assumed that the user has a workbook with some cells'" that use random number formulas w Written by the u Humberto BarretoE for Barreto/Howland Introductory Econometrics, CUP, Forthcoming 20057 Idea behind the Progress Bar is due to John Walkenbach3 http://support.microsoft.com in the Knowledge Base( FMRG references in the RNGandSortModule Histogram code from Howland% All other ideas from Barreto/Howland Contact Humberto Barreto Dept of Economics Wabash College Crawfordsville, IN 47933 E -mail: barretoh@wabash.edu phone: (765) 361-6315 FAX: (765) 361-62771 Run the Bootstrap add-in by executing this macro2 It will bring up a form which enables the user to input necessary information+in case the user is in a graph or somethingmake sure it's A1 style $!B DThis add-in will not work with columns as numbers. Please execute Tools: Options, click the General tab, and uncheck the R1C1 reference style option.HUnacceptable Reference StyleJ NLA@Fk@ PB@Ro(check language]@Tools'") ****************************************0 This macro adds a menu item so the ShowBootForm macro can be run from the menuX define variables]]'\$ Make sure the Tools menu is visibleWorksheet Menu Bar$2(^ $!&$WThanks to John Talyor n $%l.=it's not English, try to get the word for Tools from the userfor dhThe 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 $%0'"k8d0Tools'"k "Worksheet Menu Bar$2%4(^/ Check to see if there is a Bootstrap menu item'\ Z "Worksheet Menu Bar$2%4!4!` Z "Worksheet Menu Bar$2%4%4!b Bootstrap ...IMeans the Bootstrap menu item is already there, make BootstrapIsHere True'\d`9Means the Bootstrap menu item is not there, so do nothingk Z/ Add the Bootstrap menu item if it is not there \]( jl "Worksheet Menu Bar$2%4!4%h.d d ShowBootForm9l Bootstrap ...9bq8k0 n|dHThere was an error generated that may affect performance. The error is, n!p. r Error WarningA@Fko* Message to the user when add-in is loaded Bootstrap ...ToolsWorksheet Menu Bar$2%4%4!lA@Fo0]] ]8  2 '| V | 'V |G ~ '~j VA@F ~A@FoPmyBootDataSheet Bootstrap.xla$%!B@o0(@You have successfully loaded the Bootstrap add-in. You must have an Original Sample from which bootstrap samples will be drawn. You provide one or two cells that are tracked 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. $v $vExecute  ": Bootstrap ... to begin. rBootstrap Available$F'tAttribute VB_Name = "MainModule" ' DOCUMENTATION@This add-in doe bootstrap of one or two cellsffrom @ANY Ex& PworkfkINITIAL DATE: 16 Mar 2004AREVISwS:To see compledocument@ation,Doc sheet1]zCUser Inpu' It ss7d thate u hafN with soc randinumb.formu lau8Writ@ten byHto Barr8eto /Howl&Ptrctory Ec on Ahg`'iqaf, tge`dc2WA? )9 t;(" < 51XLinstalnl Y2d1 appe ~be  US."GOOLS>", 2U>Eo "5E7+ +!prM(/,1CcL3ifAYaAV52p*H=d8ۦ6aj0,0Zo0t4`4 .Coun{$.n` lsj).CaX5 ...6a1'Meaamlready#, lA5=8kSJ`a0 re o@  @u!e2yj"' `X Vw i+fS O TCqNnewa]aXta S@}=_!NV6+.! (TyptmsoT3s31WW.OnAcQ mO5"!"Y0ExPq)R-"P6w!n ergtepmay affpect c0.i" &".& "Hvb"I', "ÛWa\rnQ` 'pCss0lf0wEpTloaded : ps ("You hav ssfully> ,(ì`U1mu stOrigin@SVfss0`dAZvp ͐ackSasi]y calc0tIJd"Ro@Qpetiuu 0yqeђout`sbPultsQcawwC`rҩ!H& Chr(13)"Ee s$1":8=)Qb&e_ "?Av"3(r)B;t)Wheet Menu Bar").Controls("To0 Bootstrap ...dOnAction End Sub  test2() Dim mybnrow A@s Long,X yFor i = 1 To $10 E= Int(Random * 2) @+ 1 X1X ( If|Ohen y(y3Next Ms@gBox Xy hiwritinBglWorkkkC.xlaS&h my Da ta Cells.Clear0__SRP_c Histogram__SRP_2<,__SRP_3rU $ `$ `$ `$ `$! `nrU  8` !////X`(1I ak/////////x  PecD/$*\Rffff*1z445b71a8*\R1*#1c8*\R1*#2ab*\R1*#1c7*\R1*#d7*\R1*#31*\R1*#78*\R1*#1c4*\R1*#2c*\R1*#1f0*\R1*#246*\R1*#2aa*\R1*#10*\R1*#261*\R1*#1c*\R1*#262*\R1*#15*\R1*#db*\R1*#3c*\R1*#e8*\R1*#2ac*\R0*#15   $0 "X 2 & . :0p  $  H `  6 2 * @ P ` " h p  & l0=21a0 x ``MEX@( > L**LL<\<` x* )@PX 8IB xID IF IH IJX$ IL(  INx, 8 IP0 X I4 x I8  IR<  IT@  ID  Ip H `X x`Zt 8 px `  0 l  `d` \  X %`V P XLP 8  Zx H p  \X D   ^ @  ` <` 8` 4``H 0`b,`d$`f0 `h `j `l `n` ` ` ` `  p  `r `t  x %`v |  @ ``h d` \  X%  0P`z d`.`0`  `#k'' ) 08 )px X) I I I I$ 8)V(px X)~, `t`p`Z8l `&(k@I `8I `VxT`'Dk IV 8 `(,k8I XI8 `Xx`t `*+I I (I HI hI I `(x@ `+k,,I  I @I `t`l` d`\`T`L`JH5 H%8P@h `@(@XD @p4 (@$ @  0`8H@`(PhXX(@@ @ @ @ @t @d @T @D @4 @00 @, 05@p( @$ @  @ @ @ @p (0=@8 PE@ `8E8H@ @ @ p B6x@ `@( `H)X@`,%p(@ `X@ @`h@ `B`% pP    H 8 !X"@#X@ @( @ @l @hT @{@P h{ @L @H @D @4 @$ @  @8 @P @h P@ @ @! h@ @  (8 @H ` @ pHH @ @ @ @  ( @8  P  @p  ` 8  8 @ |  p @ x   @ t ! x@0!p ! H! @X!l p!0!@"h !X!!X!@!` @\  RTB@"X `"! @("T `0H""8"P@"`P,%p"("@"@ "X"@"<`""#@ #, "" @H#(` #"@p#$ `8#"@ `# #  @` #H# @# `#p#!60 #`%#%xx8%$(%$@%$X%$ $@$\ @$X @T @$D @%4 @$ @% @h% B6%@% @%  %h% @ @ %%h% @% &%@0&  &%H&%@X& p&0&x@ &X&X&"`@`'0'x'@H'h @X @'H T@@'8 @( `(((`((x(@Tl p(`(X0@(\ @L @D x)))*@)d @P*T @D *P@*@ 0*)@< @*** @, "@8+h ;5H)HhP+h*@+X @h+H @+8 @( @ `x@,P,,@h,8 @,( @, T@, @ @ 0(  * 8@ H X "h        (  H X hx       (  ( 08@*H 6x  D  (8HP` p&2 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$41P$ $ $$P($>U%"+X%<g%$% %% %5&6_ & X&x&&(+& & & &"+'"+(' P' &`' 0' 8' '( (0( #H(#h( 21( (.+(.+)0`0) `) p)6_)) 4() >U* <gX* $* * * %* *5++6_ +X+ `++"++ + ++ %+ ,(,0,"+H, p, , ,"+,J?, -$0- X- x-- ----- - - ./. 8.TH. ..9@/ 2`/7p////7//// "/2/0/20 !0 & 0H0P06YX0060 *0000 060 *0111 1 (1 $H1 "p1 "1 $1 1 2 72(202 82X2 >`2 *x222 222222 $2 $22 3 3 3 (30383 H3P3 X3`3 h3(p3(3 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  808 H8 X8h8 p88 " 88 8 f8  9 89 H9 P9 0p9 9 09 99"9 .: @: n: 0h: nP; 8: ; 8; << <" (<0<8<@<H<P<X<`< h< x< <<<<<"<$<$<$=$ =8= H= X= h= x== == == =====>#> > 0>P> `> HistogramON 22 October 2002 Routines modified 8 Jan 2004 f This revision: 22 Feb 2004 Contains NewLimit routine, which does a better job of finding binwidth 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 observations Width: binwidth minval: minimum value maxval: maximum value/ histsheet: the sheet where the output is to go+ histRange: the Range where the output goes# ViewTable: include Frequency table AllLong number:6 This routine determines Width: this is binwidth xx: this is the starting value]]] ]8 ' absolute value of minimumd''     Bring xx back above absmin    '5  ' xx = 9 * yy '    '  '@5! We're done: xx is just below yy   e    '4 ''      '  '4   '! We're done: xx is just below yyk@4k84d04''k4   'This is the difference0 Next step it to adjust width so that it is even' the default;   '6we're going to have to lower xx by 10*yy if minval < 0     '3   '   '  '2  G    'j/ Next step is to get the Width to be reasonable Start with original width]Ph'     '  '' e @''d1 ''k11 'd1   '  '' e @''d1 ''k00 'k0   'd0 'k0k0ox0 Written by Frank Howland] ]8]P The bins]]]]]]H]]]]]](]@`]I Get the variable labels--this routine may be unnecessary if public worksDim Varlabel(1 To 2) As StringCall RetrieveLabels(Varlabel)varname = Varlabel(1)]]   ']]0]H`4 This code checks for zero width (SD = 0) histograms ' 'd .   Ak.   '     A     V  ~] 8$$     $V$~A 0 %!'G jNo. Obs. ,$    $  G +j8  $ to  $   , $   ,G   %   %$.ge.C !B@is !'(18   G 'j x(myboOSet myboxRange = Range(histRange.Cells(17, 3), histRange.Cells(18 + Nobins, 4))3)Call PutInBox(myboxRange)4 Is this right or should it be histRange.Columns(3)?Z !Columns(3).ColumnWidth = maxwidthk*& %' Z  Z$V Z % ,( Z$~ Z & ,nt Zlimit ]P '  G 'j& Figure out appropriate starting point 0Dim limit As SinglehenDim mult As LongliDim oldy As Single limit = 10mult = 0IfIf limit > Abs(xx) ThenWhile Abs(xx) < limit oldy = limit If mult = 0 Thenul limit = limit / 2mit  mult = 1mult = ElseIf mult = 1 Then  limit = limit / 2.5  mult = 2 En ElseWend limit = limit / 2y mult = 0While  End If Wendim If xx < 0 Then limit = oldyElse  While limit < Abs(xx) oldy = limit If mult = 0 Thenul limit = limit * 2mit  mult = 1mult = ElseIf mult = 1 Then  limit = limit * 2.5  mult = 2 En ElseWend limit = limit * 2 old mult = 0  End If -li Wend# If xx > 0 Then limit = oldylimit)End If(If xx < 0 Then limit = -limitimit: do same for valrange if it's less than abs(limit)-abs(xx)*Then add the valrange units to lower limitHistogram TitleEmpHist %B@  (% %  & %$  B@ ! B@H  %  99q#  %  99q# ( !B@H  !( !B@H B@"  ActiveChart.Axes(xlValue).Delete 9$ Histogram of 5&!((dq"  % 8 9* $9,ot .MajorUnit = tickwidth(qx"  % B@Hlabe !> B9@ 9Dq8"  9F 9H 9Jq"Dim chartName As Stringth9 **** Need to check for other charts named Histogram ****&chartName = "Histogram" '& Varlabel(1)eftActiveChart.Name = chartName@ Count the number of chart objects and then just assign the lastis$Dim rowH As Variant, colW As Variant@!leftC = histRange.Columns(3).LefttopC = histRange.Rows(1).Topts(his.rowH = histRange.Rows(15).Top '.RowHeight * 14eet.,colW = histRange.Columns(10).Left 'Width * 6= rowHAhistsheet.ChartObjects(histsheet.ChartObjects.Count).Left = leftC?histsheet.ChartObjects(histsheet.ChartObjects.Count).Top = topCIhistsheet.ChartObjects(histsheet.ChartObjects.Count).Height = rowH - topCIhistsheet.ChartObjects(histsheet.ChartObjects.Count).Width = colW - leftC %B@]hto !2'.d'.k !4'0dh'0kX 6 !2 . 98heet, !4 0 9:le, 1)q %B@HoCall HistogramMaker2VarRev(values1, values2, publicnobs1, publicnobs2, w1, w2, MINvalues1, MAXvalues1, MINvalues2, MAXvalues2, histsheet, histRange, ViewTable, 1)' 21 Oct 2002A In this revised version I use the methodology from HIstogram1Var$ for setting limits on the histogram] ]0 ]H  The bins] ] ] Dim Nobins As Long] Dim histsheet As Worksheet] ] ]X ] ] Dim noobs As Long] ] ]0 ]H ` ) keep track for scaling purposesge.A]x   G ] ]  ] ] 8 ]P h eL] ]  me] - Find overall max and min for the 2 variablesas H J'MsgBox histRange.Addresst N J G N'j L' P L G P'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 variablear Fthe 'F L'dd J F dAkp H'H P'fdH N H fAtk(4 Choose the larger Width as the class interval Width F H  *******d F'r'd H'r'k J L H'r'kp N P F'r'kH1 This gives us parameters for the first histogram d f   d'td f d  r'v f v r 't t d G t r 'tjkd X f'td` d f  r'v d v r 't t f G t r 'tjkk r+  oid   'rd'rkk L t '  r 'b b8 bX% b Xx b Z$$$> B b r t$X$ZA ]0   b$ $  G +j>avoid rounding problem  $ to  $   , $   ,Z   %   %$., !B@ !'   G 'j ( $( maxva % b  %$.e Call PutInBox(myboxRange)k  b $   , ` b$'j Z b Z$X Z % ,\$ Z$Z Z & , ( Z$p reset bins for second histogramn *Call Limits(minval2, maxval2, Width2, xx2)2 This gives us parameters for the second histogram P t 'um  r '`% ` % `(  ` \H ` ^h$$ @ D ` r t$\$^A   No. Obs. in $p , No. Obs. in $p , % %$(x ` b  why is this column 4? % `  %$.  b ` $  G +j  $ to  $   ,   %   %$. !B@ !' `   G 'j   ,  $(d@ % b  %$. kCall PutInBox(myboxRange)  ` $   ,G ` j b ` e ou  ` b   , k0  ` $   ,% k Z ` B@ Z$\ Z % ,% Z$^ B D Z ' ,?* (Width1 / Width2) Z `$ j G `$'jjce:= Figure out chart scalingDim tickwidth As Single38]p b ` =xlC b'zd `'zk & %'l' %'n Put in the chartEmpHist %B@  (% % z ' %$  B@  ActiveChart.SetSourceData Source:=Range(histRange.Cells(1, 37), histRange.Cells(Nobins * 2 + 3, 38)), PlotBy' :=xlColumns 3 ~ ! B@|ec ! B@H  % .Color99q   % = xlTh99q  (le = x !B@H  !( !B@HIf B@"%  ActiveChart.Axes(xlValue).Deleteit 9$ Histogram of l And  n5&!((dq  %B@HChar !>ecti9 R9@ T9Dq  %B@HR !>%9 R9@ T9Dq8  If Nobins2 < 30 Then With Selection.Border .ColorIndex = 57. 9 .Weight = xlThin .LineStyle = xlDot End WithBi End If  % , noob t9* j9,.MajorUnit = tickwidth@q  % B@Hinle !>coun B9@ 9Dq  9F 9H 9Jq %B@R %B@] !2'.d8'.k( !4'0d'0k 6 !2 . 98  !4 0 9:q %B@HoY Usage Call MakeBins(binleft(), bincount(), values(), noobs, Nobins, Width, xx, x(), y())< This routine determines the binleft() and bincount() arrays ]]  A   $!%+Dxx   +V$   + $  $!% + +V Put data into bins Z ' $ Z$b  GFj  ' $ + Z Get coordinates for histogram$+V+~    'Z  $ Z+V $ Z+~ $ Z +V $ Z +~  $  +V  +~o]'V V  V 'V V$T 'o V$X$@$X'Tix]x]0'  0.'   0' k (o]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 Ifdh   '( Call FindPrecision(width, precision) If AllLong = True Then) Call FormatSumStats(statRange, 0)1 Call FormatSumStats(statRange, precision):h Set statRange = Range(histRange.Cells(8, 1 + whichseries), histRange.Cells(12, 1 + whichseries))g Set statRange = Range(histRange.Cells(6, 1 + whichseries), histRange.Cells(7, 1 + whichseries))`  Else1 Call FormatSumStats(statRange, precision) End Ifk9o9]`]x]]]]]  v` '` l` '` n` d 9'k9 $T' $T  'yh` ?$T'i` @$T'@$T' t` !@$T'n` '     zj`     $l`  '` d 8 '` k8d8 @'*w` k7d7 'k7k7   '`  '` G   'joh7`7{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**  #'" / /4A$+$A_bM%F(-)j+)EIf%-#bI[k=T ?` 'C`8ydiff@nce!NextҸep C ‰h so@a@evvAfyPAp" efault; =B>c02wKgok`haj@Kby a2* if= < $xcA+*;8-RH @*'PF4 &2= --$xy' Ds 0get0etbeason`SVth originalۥQWmQSyPX{ 2= 1C<]=*1A / Ж5tWYCe.5[T(2 t ,4%3g#S# <Q1  e*lq 101 1pa   U0 > 1}ନ 4>+'QP@-"dTi@є@V,erHWrin NFrank How~lຑ >(%jW%left}'4c8ounUMin!Maxaom my GWorkGXgyo!choiceQ colOPWgPvarn@ S0ng4Nosp\co`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 subLongBIf p>1 Then  x = 0 ElsJ1@@If v?FWiWindow%H .ScrollGE-`? )S -[ME:$N(/8Sel`uaSubA FMaker2`(val@ues1()3Double, 2noobs1, 2, BBcminmax"A"i,>, All!C E`ViewTaCalblRev%, picn` wwMIN@ , MAXg?a`(1)' 21 Oct 2002@w Iis revised version I useBmethodology from HID1@4afor setting limi !KmiEmGjObinWi,"I"oG!U' Fa } !:2pUriMS cg1e'MsgBox8qAddres@n2 < rGD>=A DQcY >TbQ AH WReeveLqs( JY$!6=82U(2;Sp$] excepdS to ac"two0Qries Pbs!plopi/s1&r va3‚10C?FA3NewLY(qp !xA3 iC<1#)Cho?rg/a)c0s interQ(i>3~2c!pF2q;.=O= 0  1 $*O-Xth  ς\ ar }gs`| paepsXyfi&y7$" b-_ eg &Tp;In1 phll D0  ,E&Y@ 83>  (]K- minval) / 2 aElseHhoverallWidth = 1nd If E .ranDgeLmax1H - ~xxDN@obins1@Ihnt(^/%e) +f/lReDim 9left@(0 To H) couW1! 'x1C2 *G82) As DoXubl~;YC MakeBn(T),YIv ues1(noobs1,>, xx, UA)umyƆW4, wЉ yb8oxRȀ  If ViewTasTrue ThenHFor| iIPCAbs7i)) < 1@Q10 ^ L-6ii)0 'avoid rfding (promhistB'.Cells(i8, 4 Si1) & " to&m5ci4Sxet CB @,(9$, .Columns.AutoFitG+q~=H D xG @^ >gzmG=  N`ext iD (|4);a!mɇF= s!17a3 187s, 5!V!,'cPutInBox( %ŇeO{.2, 24E0*E0 IIC jۉ[2 j37@ujDe^8azej'0 resRs f`qseco 0ograf)Limits(C Fxx2`' ris gives us pa!`eter thde / @ 2븃'2%'6?%#t% x2-56 #`+"D!92!Y2xm\1""No. Ob s. inbVarlabel(1 MA6!MWF/bOO>).WrapT!FK0d  >ue' w(hy )t cbQ 4?Q H bHcH4HE 1a=Gtq1P ononincn7 mpmIia?j?jQ rso#q1jjqgjn߼j jxja1Р00w]q?nQ˭'''geo6'Ymo4t)OG="-P.822{1nȳwws7t(1`Q| |2(|pT9|;(3Pԣa) '* (11@W A0e?Pl"3P=u ' Figure out chart scalPtick;6SṕnbsLong231f[y"=\e/mynameA] 38/SA_ 39P `1o Act Sheet.Ca Objec"EmpH").2ValuentinuouV+2+5 .a$If Nobins2 < 30 The@f)@olGV5`*+DotHg+bIf !"[CMinimumScab9overallxx .MabxImax#'#Uni!Etickwidth"ep$oTc-.(HairA!L>g+k "TMarkvɎ3 'oraLabelPosi<= ARange.Cell s(1, !yhow(`: Dim rowsub As Long, col[ Row1[A= 0 E1`M l l h !d1cCWindAB.Scroll -G4v g$-~Sub1@ MakeBPD(2left()Double, a@counTvys{noobsI, 5, xx, Xy@~' Usage C<`, 4, W<x(_@PHas u_e denm‹the $a #arrayPb'q+preciskiAjr F`"P(O ! 0)@*Appli.WoDrkFunon.R d(G 'PC i.noDbyiqH+S`i , NA' Put data intoq$y j B  Doi`x <=5"jT1@sD GoTo veloop:i @1LP  gS25+ j1)GetS/tX / 1W f,-Log`10(X)ytpc 0pmc3Bp/(10~#`XU#Y" ҿSps(sPw #p tr vH!$= "0" @0 > 5!.! &vS *~&CxF .NumbeP 1Dr` p[g%S!, Allhrш,4dwhichsǴ$[ ' RMS:= (}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  #tAl<ot?o$ooq|o$l|ot oo? oot oz n4nootxt(ok*s4n4oq,l,pl,l#l,L#l,H1HLMl@M@,M@ll lp $Dc]3hxl,dPlxlp ϳ lxlpUlxi%(l%lxlp#L*#Hlxlp#D*F%l8D2LHD5+lxi%(l%lxlli$%l8Dglxi%(l%l8Blxi%(%l8B$ Q6$a 5aBtd5 odo\odt\ xfPp5o\kl%(%$ ` C5_(%(l%l8Bl,i%(%l8B$ QX6xl,d+lxi%(l"%lxlpk$%l8D xf} l,lpt tl,d=+lti%(l%%ltlLk$%l8D+lti%(l&%ltlHk$%l8D tf0ok*s4n4oq0l0pl0l#l0D#l0@1@DMl@M@0M@lllp $Dc]33 +(%(l%!*F%l8D5+(%(l%!*F%l8D5`(%(l%l8B(%(%l8Bc%$ ,6 l0l, _(%(l%l8Bl0i%(%l8B$ QX6l,xl0d lxlp ϳ~ lxlpUlxi%(l%lxlp#L*#Hlxlp#D*F%l8D2LHD5glxi%(l%l8Blxi%(%l8B$ Q6$a 5aBtd5 odo\ odt\%lxi%(l%($%l8D xfV5o\kl%(%$ ` rU~|  ,,1XiXXy0y8q4X!8ACCAAqATLD!YaY;AAA)BiBBYYY;)ZQZ!y Iq.qZZZX  D4Btd (Ddbtl od$odkD]godkD]$ts$(D 4bE$(D 4Btl qTodkD]$glTqTlTolkDbodkD]$godkD]$g$(D 4b DBtl qTodkD]$@odkD]$tulTqTlTolkDb<lTolkDb (DbtlodkD$4Bt\ qXo\kD]$tW qX$odkD]g$olkD4b$olkD4bodkD$4Bt\ ol tl $(D]gD4lXolk$bsolkD]$t;$olkD]g,oltt n oltls% nol@tls% oltls olkDbolkD]$goltt n} oltls nol@tls oltls= otkDb$D4t$(D]Z (DbD4bt $ 4 ` *sD(]3E (b Bt\M@ll ok*sD((l$DM@l,ATBt6T(c]3 (%(%:d%l$Dxl %$ ,  )* $ Wq%lC%lC q $ Q%%%c$(%(%l$>T 5Tl$A(]35 q ql$A (]35 q q$ D Q(l$AliT"i%,5(l$A liT"i%,5c$(%(%l$>T5T,` ll\X @pPT%,($ T`p $Bt,$g- ,Bt (Bt0(tX 0Bt(]3 (b (Bt$$M@ll$  (]3 (b 0BtM@l l,  t Bt q<&  Bt q<$(3Q  Bt q<,03| Bt q< o$ol<o$too$oq|ol|ot oo$ ooC5M _(%(l%l8Bl,i%(%l8B$ QX6xl0d +lxi%(l%lxlli$%l8D xfb l,l0 l0xl,dx %lxi%(l%($%l8D xfx xl0dp3 +lxi%(l%lxlli$%l8D xfp tl0dh +lti%(l%%ltlDk$%l8D?lti4%('%ltl@kl%l8D tfhT l0lpo  l0lpt l,l0 l,qd l0qd (%(l&%l8B`15 (%(l'%l8B`15/: %$ @   5 K$ ,  (%(l%%l8Bldi%('%l8B$ %Q[\`$ , ` l)\`69l4A"`$ ,  )`52` $ ,  `)`7` %$ ,  Q`Xf%Xf%XcX7` %$ ,  Q`Tf%Tf%TcT" $ ,  h2` $ ,  `)`0% $ WC52` $ ,  `)`! $ $ , QP P pYl*#L#*#Hl*F% P a[``,2LH)`6cP6` (%$ ,  `)`* $ W@@q<(%l` %$ ,  `)`* $ Wq%lC%lC q $ Q % % % c $(%(l%l8>$5$(%(l%l8> 5l8A(]35 q ql8A (]35 q q$ D Q(l8Ali"il%,5(l8A li"il%,5c$(%(l%l8>5D|pXTP$ @,@ld9XXp%@lLD)H ,LHD`\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 h)CxT(d]tS*Cxtd"dJ''' D`> `|JN+2``J b x`J v d8@B `J (`J[[) 088 i X`i8xi`Xt`l`d`\`T8`pL+`@Jh i ihi H+`>J@i `i `H+h `HJ i i`X8`P  `TJ %)  ii`t`l`ph8`\ x `J 7`x `'`@`8`008`(+ ``J Bi i  `8J8 Yi @ p   `8J $$f)    i`px tx `  \   0 X( @ 0 ` T `ZP  `hJ )   i i`px` tx`p  `J d ' `\ + ``JX i x i V`J.. Xd@B  X`TJP )V  X piii i8`t`p`h (`8J NN)V iii`j x`d```\p2\`XX`\T`^PP``N`bL`dJ f`| h##B iX i\i^iii\ (i`$Hib(hi, `tJe)V  f8 i(iHihii i$`x` t  `h 8 z)V px XiiZi`x`p `J \`ꀦ@ꀨ@ꀬ(@ꀰ((@☲Ch( x x  X((P(xHxxxh v`  m8X0XXht @D(p @` HX@F@\P @@ 00 @ @ @  0   @ @ @ @  @ @l @@\ (L (< @, @ @  @ @ @ x  D @< (pXX,  @ @ 0: @ XA  hAT   P 0 t@ !X@p @` @@ P!@P!!h!t x!p Xp @` @@ > 8P""""t"pX @p @` @H pX(@# $0$(` @X H$T `$X$`H%FX @P @H  %% @ % @ & @  &&'&xt&@X(p @` 'X 8 ' ((l (X` @h @X @8 P H  ))x8))Xr @h @` @L ) )H ))D *@*@ * 0*X*h*< *8 *p @ X +X+pl ++h +,X X,,`5,XX @P @H @D ,-@ -< @, @ -@ @ @ @ X x `.`..x.xt.@X (p @` //`FHP @@ @< (0: @0 @(  @ @ 0 1@ 0 @  (@P1@@(X @T h8p222`l 22x(8 hHh(h(H4 @x84Xl 5 h 5x5H7H6(H @8 @( @ @ p66 66( @ 66 `77p 7@ @ @ p78 8 @ @ @ @ @p 88H8` `8p8P 88@ k, c8 @0 Then@ x1 @ 8x1 > x 7(42PecD/$*\Rffff*50446308ea' 0 @ >P < < >( 8>h     B J . $P `  h p J9 R :B  x::::: Bx "        = ,0 &@ &h  "     $3  2  5 0 8 @ <H  F  "  `(  < X $8 ``  d H "` &  n 8 *P $ f  08@HP "X V .  h  "   .   0H  h "!       0 (8` x    "!8 H hpx     . B   V ^p .$ 8H "P x 6B   V ^0 .$    0HX` B  h$p    XC  *0`  h "   XC@X` *x  "  "  0Hh + B  $  > @ 4` F $ , 68 p  N 4 .  B  P BX $ "   ( 8 @ "  H (P x         !!! 0!@!X!p! ! ! ! 4!",) " P" 8`" "" " " # #0# "  8# @# P# `# p#x# *##  #* # $($" 0$8$P$h$"<$ $$ B  $ >$ >% @H%$% % % %% %%$%&  & (& DH& " &&& l&,('=8',H' (X'' <' ' ' ( ,( T@( @( 0( V) T`) V) * F(* Bp* P* B+ 2P+ :+ + ,+ , X(, ,, "  ,,,, , ,-"- @-`--- B  ------- B- 0. :P. *. . . ///  /&*8/ `/ h/x/ /// / /89// @/ $80 `0 h0 x0 0 0 0 0 *" 1 0 1 P1 `11p1x11111 1 11111 11 1 B2 H2 X2  `2 $h2 2 222 222 62 .03 4`3  33"  333 23 3 4  4 (4484p4>x44 64 $5 0585 @5 P54`55B55  5 66(6406h6p6x66 6"  66666 66 66 77  7 87 H7X7`7 h7 p7 "  x77777 77 78 " 8 8 T(8888&W8 8 8 9-9 09H9`9,_x9 9 9 9 :4 This module contains three general purposes macros:1) FMRG random number generator%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 Howland/barretoh@wabash.edu howlandf@wabash.edu-765.361.6315 765.361.63176****************************************************** 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)] ] 6 see sheet CurrencyDoc in Random.xls for documentation] ] ] (] P] ]( Initialize FMRGizA@4 CRandomize runs on open; no need to use Randomize again in any macro ' 'Load B values from p. 147 of Deng and Lin, "Random Number Generation for the New Century," The American Statistician, May 2000, vol. 54, no. 24, n]@#g+ j+tt+v+dz+ԁ+„+)+ +U +A + + ++++++٥+++++f++%Pick a B, each with equal probabilityabil]p  ' $'o)TTo use FMRG in VBA code: FMRG runs the rng, myFMRG is the actual random number drawnnstallTo get a VBA Rnd, just use Rnd*To get an FMRG value, TWO steps are needed FMRGBo8 x = myFMRG to get the random number and put it into x5If you RESET VB at any time, you clear the value of BX6The If statement below runs auto_open again when B = 0al r GA@j8This calculates the next "random" number in the sequencen           '  ' g ' 'oP'C To use Random function on a sheet, simply type =random() in a cell8( See sheet1 of Random.xls for an example B $B@A@ '2i&;The two macros below remain for testing, but are not needed  ]@BH ' @BA@  +%   A@F$A@F PowerPC G3; 266 MHz -> 9 seconds PowerPC G4; 500 MHz -> 4 seconds eo%x  A@ 'V Vffffff? VH}8g? r te  ' n V +A@  +k$$o$6******************************************************FOct 2004 versionMH@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 choices04 vQ The first choice requires an array so it has to be DIMmed somewhere in the macro Example 1: Explicit ChoosingutioZ 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 basedires on VBA's Rndto b 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 1wo Example 3:sult v$ Dim myOutput(1 to 100000) as doubleuted v NormalRNG myOutput, 1, 20, 5istr_ These two lines create a myOutput vector that is filled with Normally distributed values basedt on the FMRG routinehoices]]]]] The Optional parameters arele 1 SP TypeofRand, which is 0 or False if not set--this means the macro uses VBA's RndNG' Getting the number of values requested c]$'pr a Because we generate TWO deviates at once, we divide number in two (with MOD handling odd number) We waste one deviate if odd Optio ters a' Use VBA's Rnd to generate random draws i  pans th  VBA  '  'requ   ''pbe  Gj $X $'in two  'dl     +'d(    +('k andom d" Use FMRG to generate random draws  p '  A@  'A@  'j   ''  Gj $X $'  '     +'d    + gener'kp  pkXoP'This is the old version of normalrandomDefault was Rnd (O 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@lr$'iThis is the Feb 2004 versionalRandIt forces use of FMRGly t/Also forces explicit declaration of mean and SDs O To use NormalRandom function on a sheet, simply type =normalrandom() in a cellanW The 3 parameters can be set, eg., =normalrandom(1, 100, 50) uses FMRG, mean 100, SD 50( See sheet1 of Random.xls for an examplele $B@s ]  orce'Error: SD must be >= 0DdSD OKe No   A@ a$' tki $B@]8]P  eg.,'Error!0, 5dX      $ '   '$draw a random number on 0,1 interval 0A@map to computed interval      'khi`h](]@]X$'pin      $ '   ']p  pnds $draw a random number on 0,1 intervalthe saA@map to computed intervalrr      + Ho]]'$ A]0H`$    A'HiA@F oxx $B@pA@      'n 0,i0Barreto 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.thUSAGE:tionTwo lines requiredHDim myArray(1 To 10) As Double CREATE AN ARRAY WHERE T DRAWS ARE PLACED.TRNG myArray, 100 CALL TRNG WITH ARRAY AND DFsqua(Example provided in TRNGtest macro below2<Used to draw a single t distributed variate in a single cellcite KUser provides df $B@]X  HDF must be greater than 0'TE AN d  A@E $'ki "Get number of t deviates requested]0 $'pro]H  p to dr A@te]  p  ]  H ]P ]h  p + g A@ Z  Z$ Z+ $ Z$ + Zuested -create t distributed deviate and put in array  p $ $ $ + 1Uncomment and go to Test sheet, then run TRNGTestFor I = 1 To UBound(tRand())ZCells(I, 1) = myzRand(I)Cells(I, 2) = myyRand(I)Cells(I, 3) = tRand(I)0Nexttributo  ]@ X a$'pp$']p   p +$draw a random number on 0,1 intervald go tA@map to computed interval $X    +s(I, 1 d(I)o  ]'  $At re ' $   ,'p ' oX  8 This is a quasi-Exponential random variable in which we7 take lambda = 1 in the standard defn of an exponential 9 and adjust the mean and then scale to get the desired SD $B@  A'Error: SD must be >= 0"d@ SD OK,A@ $X    '8k i End Oct 2004 version in wh=*************************************************************l ]@B and t 'eNormalRNG NormRand:=result, TypeofRand:=1, mean:=0, SD:=1 ', is an alternative way to pass arguments XA@   A@F $XA@F!PowerPC G4; 500 MHz -> 17 secondso 6**********************************************************Sorting algorithm Thanks to David Maharry Aug 2000& quick sort of array x which is x(l:r):=1 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 Doubleght,O If you make the temp variable in the swap sub an integer, you can see what theum code is doing.? To use, call qsort with the FIVE parameter values as describede < 1) The array to be sorted. This must be DIMmed as a Double.reto mI 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 arrayee wha+ 4) n is the number of columns in the arrayl qsor3 5) m is the column that is sorted from low to highThe ar Exampleed%Dim myArray(1 To 4, 1 To 3) As DoubleHqsort 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-2002umbe]](]@ V   $'that i   $V' V      A1 To 3 V    A V    As sortkoX]8]P]h]](]o Lo< 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 failsV$ Incorporates median of three methodA Reference is Sedgewick Help from Dave Maharryrs FH has p' '    et  $V  $V i'  'd  'kx 'd   a  $V  $V G  'djk 9 ptemp is needed in case we have three equal pivot values in the median of three method      $V'X(  $V'\   '\ \ $V'^ ` X \ e li ' X \ ^   \ ` b Af are at X ^ nequ \ ^ 8dX \'kH d@ 'k0 \ ^ e( X ^ medi 'd \'k dF 'dgk k < If check = 1 we assigned p already in the Check for smaller   routinedp d' If x(p, M) < x(p - 1, M) Then} p = p - 1' End Ifk d '0k bG d'j/ check2 comes from CheckforSmaller and tells us w( that all three x1, x2, and x3 are equalme.If all three are equal just use the original p 'i( ('b'`, This checks to see which element is smaller\ June 2002 FH^  X \ b X ^ '% pick x1 because it is larger than x3dX \'0 pick x3 because it may be bigger than x1 and x2 X ^G'bj0 This says that all three elements are equal, so we need to discard the resultned k X ^emall X \  '% pick x1 because it is bigger than x2M) Td0 '4 pick x2 because we know it is bigger than x1 and x3}}k \ ^eG \ X 0 ' pick x2fod '& pick x1 because we know that x1 <> x2 arek`dX'`kHo@]] ' '     $V  ec  'h  $V e  ' 2d V   A }  ' }  ' pkph 'oX]]  ause i  $V'an x1 Z $V  +Vbj  Z +Vhat alo read in the dataM the array must be DIMmed as a Double to be compatible with the qsort routine% p]H  Zk x2 b  Z$!  Z+d  use qsort A@ p Output results ause w Zx2  Z$  Z $( oh%?CThis function will NOT recalculate with F9 or Application.CalculateLThis function WILL recalculate with CTRL-ALT-F9 or Application.CalculateFull  $B@A@ 'vi88/Attribute VB_Name = "RNGandSortModule" ' This m< contains three general purposeLacros:l1) FMRG rom number`torB2) Normally disd 1'3) qs ing procedures Thehas an auto_open mat initializ=t2Nx!o Xuse%,upy N of it+d pasinto a:in your "active" workbookNor, oH PC, fr=Project Exploredrag?1*{WFor questioWmments\ct>HAUBarre  Ffk Howl bC h@wabash.X hC f@ 765.361.631n57.'~*2mv@"DeclaraC@:enabltzbexd by joVros@~SXAMEBf(p)To \a REFERENCEVB must maZd!ovp IPublic myA)As Do@e Int Curncy ' ssheet DocR@som.xls fဉdocuAFOlag1) 222BbLongfISETege[|dGECVt p@2147 48364DSuby(0 IFxbL C@z 'Gru@b`n; no need @7ag%any9cI 0"= Rnd * 2 ^ 242`'Load B valsc}p. 3!De3 Lin,b4 NG¨aW"6~New Cyury,"@e Ameri2cSt@9 cia@ May 2000, vol. 54,@. 2 Dim BArray(1 @XH25). )aa/6403F= 27149F @981 403022 = P313363(236f 7C98G8B4601B9B6098F10c181`(667136 84h"3709KEC787hc961 408540p4217@Ee 4(245g 24319G'2436j924 2A445302a4567h4633` 'PickB, each with equq{robab ility"myX1";f04 +z 0b # 9EP2:Ya;L;P'A7#MPrVBApjde::.rng,Pp!t ۃ@rwnXgpOaA1:, jVg9 Xnr9, TWO P|preAedSQ xX`CR 3;``Px'If`OP`pEtime,clearwfAB@1s =eZclow L׌#KwhB 00BnRrcalculatDnext "]S"E s0!encgfP@(bPcpQ2 -tR)mp@(/ p@## ?OV' qj(FunP`_(;Og'P) f#a sAu, simptypPa cell"S%x1qcvwZexa0App0~x.Voi(Trues'= U R"1 will N`OT reV=Fl9 ICe ^WILLCTRL`-ALT-FuQNV?Fals NVFg/ ГwoĞe1e.m{э-t0, Lbu0=otV= HT 1qfzk#q1s !08rvt; Tp;PѶigbt4 si#i) N@6 MsgBox "@0f5000 'PowerPC G3; 266 MHz -> 9`cond`'4; P%4'0AegraphqWh'i < 1PC X % pDX >@ 0.7 A\X300F [i#`C@0s(i, kX3blu If xWenPW -*oO`4` vers@ C>o cנ abov"beqV0@] a=ey(`cG* w(=H,X OpўxTEofqwBooaP9MepDFSDM$l Rpecip_J+t!. 77:Crt1 Scientific Computing, 2nd ed.; vol. 1; p. 279 ' Box-Muller Method  ,To use, call from a macro with FOUR pa rametrch oicesphe first  requires an array so it has to be DIMmedmewhere in tFoSExample 1: Explic>Choos#Dimfsult(1S100) /doub MUST4 @catinNormalRNG.1, 01These two linqcreatexG vectorfat isd+ly distributvalu#base' on VBA's Rn2: Defa1'Q3Sinc1@\dske, mepzero,sd SD 0= 1,@76giv.msJao33:AmmyOutFn0 o G` ,2a5a aFMRGh roe/V@1 As DB B2MRadiDusMFACi 6 OptioXnalr ' TypeofRand, whi$ch0 NFalSif not set--th4!8n@=y`|s)L1aGet:e numb{of $Syes@Uc( $= UBound(7())A&eca we@ genereTWO devi`5`t^c` divid@m(tHMOD`ndog0 oddd ' `We wa e ! I^fh'X%In! Uo@'nr@-`drawF-ia` B ISET 0  re2p: F= 2 *Az - sV2:J= ^ + ` @>t0 Or  G>oC"AaR= Sqr(-Log() /,!Gc* c=i)@`b`@z+a+$`E7Tq *A0 $ E IfNex`c`b 9v{8oDHq8$ ~  Ae!:my"2;T=Y7 _>/()2 ???? ??| ? t?i#Y 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]Qtї!֒ 4{p1PP谪:mapinx* (b0a) + asfU gE@(dS,'a?Lo0ng UniformRand()) b = (2 * Mean + Sqr(<) ^ 2 - 4|(<3SD:) / 2 a- b Dim i As Long For= 1 To number 'draw Ha rom on 0,1 interval FMRG 'map to computeJd .Ui)myA^bja)wa Next i End Su[  Test hl10)zDouble CalDl NG( ), 10, 2.g, Max+, Min3BasicFo= gs2@OK()~&&&>Qu2u#awOct4 vers0`*-VcN(",resulth,00Z rtthimeT``''g!P:=s, Typeof3@* :=-SD:=`1 ', Palnative wayYpasrgu1[sdf{?zdǢ`< (9 'PowerPC G4; 500 MHz -> 17 second@!SU'_!Sort algor@`'anksDa0 Mahoo' 8AugQ16ic8k s02 x`5Px(l:X l P P (lefnrYri ghnp it'Ӱsaaqbottb" x[l..r,0n] u7 mth colum n formparison ' n is the number ofs in`array xXmXthat will b@e used (Bis, lc0sort(A) SB_eto made it # decimal {s bylng severvables as Dou OIf youNketem p iswap@ sub an@teger,csee wnY (coLs doing.ZToyP, caqYwirFIVE !ametvaluRdescribed*1) vTp #e d. mustaDIMmqarA' 2) l, standsLeft, Afirrow+r;.u-y a 1"3) ?!Righla34)eA 5)ǃ -U a = X(pCaMyP0artiG pv, k, n$!k - 1 U( E0If @+a1Func-&,fou 4Boolea; A/i,Omiddlex1e, x2h3e checb:I"{Fpb$'g ^a ksgre`t& `}twneqEen_0listEstorp!cas4e ith&r hoailsEIncorporat@EReferenc`s SedgewickHelBpDavemheyFH#9=` Fals@!Vi@`lWhi`5 * A?i < rC@cX(iYc@ +Wmda Truapi! EFA!^AWen!1= i @ >P_he\5J@rmgqAЃdp := 0\1 >CN3_5r2o+1,o '=3/A1O6 Q:  O ?p' '1 =@assign>p already7<.s.' routinO Gq1M)Wxҁ)'p bl}-02?F 'Cu2m`lҫ,@`d tells 'ђRk%Bм 7ayw' jp¨@origin@pя wP P1U< `J(@ '*#wNNJG@O0 =?rAelem0v''JuHne C' A `0_7#C@SPl Pn beca1p oPnPC&#E63@Fmbig1!xq1F %25'\ say7"pd$(l, so ' we need to discar(he result(End If ElseIf x1 = x3 Then@(> x2(pRl @' pick8because it is bigger than_;ld @r@2 @knowH1 ax3O_2_2 _1_>(PqPnt}= - Call swap(X,'@N$n>S(HmWendCkA?Hrr qFX, j[, jhrCltemp C{0For ![o vb= X(@*kh`X(j,> K@ NeNx ABT Dorttesting( '`ad in}e data 'barray must`{ DIMmas aZabe c@ompati\ withBqs  rou e Q myA (4, 3d !i@Cg$jeE #j)`Cell0s(17 SB.Valua) s! cd ,B 3, 2%' Output:s `5) *4a*6a 0   0 (  0   0   0 h  0   0 x  0   0   0  0  0    0  0   0 X  0 ($  0   0   0        prU 4$`$`$` $`$!`$I`8q`k0`  ,` ++, ` ++4 ` k++$9 `,a `) ++( `9&0`I9k&4` ak++$`,)` ++$Q`<y`k((((8`k(((P`$ !9Q((((((&&(H `aqk((+(((81`k((($Y`nrU~| .0-h8 ˀ Lqx L  L  Lt dTollt tllt } lt lt   ( $5 $5  @ H X x  #    N 8 H ,h  "        . :v0 p "x         . :v P `  Source:duB http://www.ulib.org/webRoot/Books/Numerical_Recipes/bookcpdf.html4 A clever way to minimize roundoff error, especiallyr> for large samples, is to use the corrected two-pass algorithmF Given an array of data(), this routine returns its average, Mean, and sample standai37086 n``##fNo57HCompObjprd deviation, SD.? I took out the skewness and kurtosis calculation from the code The SD is the sample SD.% The Population SD is Sqr(n/(n-1))*SD Barreto Feb 2001& Pass arguments per Howland suggestionx, aX Added Max and Min to make it suitable to replace Excel generated output for Monte CarloB This enables more than 65536 repetitions (Excel's row limitation)? Then display only first 100 results from Monte Carlo in Excel, tD while using histogram code to do draw histogram for all repetitions=SD, M: and using this macro to calculate mean, SD, max, and min. Barreto Mar 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 macro(F calling this macro; no numbers need be assigned to these variables' 2) DIM and load a vector that has data' 3) Call this macro like this:'C BasicFour data:=myArray, MEAN:=MEAN, SD:=SD, MAX:=MAX, MIN:=MIN  Examples follow below]]]0]H]` find n$$  ' set MAX and MIN to first valuefo$$'$$' sam) First pass to get the mean, max, and min Z$$  Z$ '  Z$ G Z$'j  Z$ G Z$'j pas  ' Second pass to get the SD' Z$$ Z$  'pa   'SD    '        ' $SD Corrected two-pass formula. $'  The SD is the sample SD.% The Population SD is Sqr(n/(n-1))*SDo8x]X]p]]]F This routine doesn't dimension the variables:data, mean, SD, Min, Max4 so can be used when these variables aren't doubles. find n    '' $' $'"  Z$ 'X First pass to get the mean. Z   Z$  G Z$'j Z$  G Z$'j  ' Second pass to get the SD' Z   Z$  '   '    ' h       ' $SD Corrected two-pass formula. $' The SD is the sample SD.% The Population SD is Sqr(n/(n-1))*SDo]+@B+]H]`]x]     A@       A@Fo]']X]p]] '  +p     A@       $a0zxME (> <P x`Lk@,,) x `i> fithe ii@am`xs it`Zt`lrd d`d? I` \ss a r`X k))DI n SDI I I 8I `x`Zt`l`d8` \ t`0km d@(`X `P``H`@ dP8% (` xk{'8`X`P`pH`@x`0Hx@@X x@T @P !@L 0!%pX8(@X@L @< @, @h( @@$ h@ h@ @  Hx F@< @ 8 @0 4 @H 0 @` , @x ( @ $ @  @  @ @  @ @8 @P @h @ @ X( 8  @0 @P @h  @ @ @ F@ @ @@  @X  @p  @  @  @  @  @  @ @ @0 @x ecD/$*\Rffff*26445b71c4*\R0*#15*\R0*#14  H :X D L &0 FX  ,    , ^H H F J8 @   "  @ J0 L . $ J( x  "  & 8  0P$$   @Hh   N    ,0`" hpx L : ( 8 X p "A@FoVAttribute 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` 2001Prgln:pdHowl@4 su0gges3 Ad"dTMaxA? Min]makeHA itabl@`o replace Excel g`enerac@p#@ApMon@Ca$rl$Th@ens morrg 65536et"iA!s ('sQw ltL)'Bn disy only first 100qsul@7V @1,Awhi@0@using @$togramd5do drawHA2I(Nt5`macroJye mSD, maxBA' jMarjAPublic SubPsicF(í As Doħ@I@o?(o h2get^A @CA@you must1) DIMBWf tJMEAN, AX`hIN aEA0v  %l0'; no numbers $neQbe`sign`@se variH 21load a vthat h '!3) C95li[` Ba.:=myNA:=E:=@d IN Ex# foll`ow bea;Dim n6Long jsc<ep-VE9 Afin bn(UB!F) - L + 1/@`Bs!?AX#~6-jvalu=k Cui Ft aMABdc F`7jTo + s(s +#jIf a<%nBD; b>@Nex]b / +A%Seco`-03j4 `@>= ep 6 =*" K#E(!- *) / (nk>u'CMmdH= d`f@pA_h_h]hEK LSumS0\s4Q#ax1 1D1?1 ~do esn'tdmens |rLD:BZk R2oKn6C h4p440d14 2a-bl 4`44T644>4a4a 44dS^U$f1(q3yTj(0p?(0)РP= -P03N@A(-S(?t5t`rPa4tax p%iVMsgBoBxr & "d"pqsG Z x2/9)u?m0^ ;s`i02 iRn\//p/n/ w/1%(__SRP_7__SRP_62BootForm XSheet2 lt  tfTQ ollx춈tl Lt dLlt tl olodtdololo\t\ tfL o\ododlx춯lxt\ m\ 08$ l 5Il 5ILqxl 5ILiL%l ,bl 5ILiL%l ,bl 5ILtl 5Id$(olk<t@l ,Btl6, t@l ,?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\^_`ab{dfghijkvmnopqrstucwxy|}~P (08@HP X `  "   0 T8"V    "  ( 8PX 0` T"V 0$e8$e`(s b        " @ P `x 0 T"V 8&MP3x  ( 0+H`h "p  &Z&M 3(      )   8 X "`  .   %  "   @ ` h p  > $e $e .(  X  ` (h b <[ B8 - '   & !!  0 @ P `3p hx  7 L </H.r` &Z  "$S &@h"p > !! $]   0 @ (2(a0RrMSqHQh2|ݖ+:W7O~ ֟Tlxrgc;G`Rͥ )H c f`2l 4h ``6pd`8``:P `<(@ `>8`@0`B(` `D`FX`H`JX Bo`  ` Bo x(%`L H`NTTo`Pe: F`|0FMRG`Zm nu`RTo`T use`VH*To`XTWO @p % pto g% Z( it %`\B at`fT the`lR6Th Pr in+ 48X xolum from lo``le R`To 4 `TQ T( `x@d v`|Di`xx 8`|Dx`x ^`|D`x h`|D`x8H9XP(%X %j*4 @8HHP (X@"H# @ `  @x @h @X  L p@< @8 @4 9 0 ( :8 ,  :  @ vx|N~ @ @ @ @ Cx D $ p H @ @ p@ @@         P ` (2`  x  O    O O     8 H  ` p     O   O 0O  HX| px t p l  h 8XX HD @4 }}}@$ @ @  2 (---8O P ` xO O      0 H x X h    #8 (#h%x(BTrx >`>  `B `I  `0M @ XR h`R  `V`[ @ 8` (8 P` x| x t  p 8HlO `ph dO O ` \ 0@X XT hxP F*L  < (88`P`4 `x0 `, ` `( `HX@`X  H#X  @ @ @ XH#(H#x (H#nF*0H#t @d @T P @@ @0  @ H# @ @ x @  @  @ H#| H#nF*0H#t @d @T 8P @@ @0 h @ H# @ @  @  @ @ @ H#| x xnF*0 `t @d @T P @@ @0  @ @ @ H  @   @ " @ H#| h!"nF*!"t @d @T "P @@ @0 @" @ "@ @ @ $`Pp p@ # @ 0# p @ X| @ @ @ h(X ecD/   "  (08@HPX` P `   $   (e( ,P ^ $(m 80 Lh $eR| P X 2` R $58Q(N`6&t< (&tH Ip R    8 8QP p&D  24[  ( H X`    *-&!8!P  h  x     0-(0d@ p  * ^ 6@nH/*l &Z  ( "0 MX x(P"G h= "p=22220 (x< 7< < <$\C4N<&10= X=,[ A,[B2d=2d=2H>0X>2h> !x>>> *>>> >>>>>>H>2?7?0?8?@?7H?`?h?p?>@ *@@@ @@AAPAXAx??;?? H ` p  $4  H<h CEC(DX t dp E E 0z! H! .vp!` <! ! !" " 0" P"(ep" ," ^" $(#(mP# 8x# L# $$e$R|@$ $ $ 2$ R$ $8%5H%8Qh%N%6%&t&<(& h&&t& <&&'' R' R0'P' X'p'  8'Q' '&D'  2 (4[X(  *(-(&(!)!)  0)  @) X) h)x)  ) 0)-)))0d* 8* H*X* *`*** ^* 6+@n, MP, "`! p,$P,"G,"G,  - 2-2(-2@-2X- 2p-2-2-2- (? 7< < ?$\p@4N@&1H@ =,[@,[(C2dXC2dC2`A0pA2A !A>A *AAA AAAAABHB2 B70BHBPBXB7`BxBBB>> *=(>0> 8>@>????BB;BB - - . 0. . (`/ P8+ CE(D(HD0+ // x00z0 000$401  101P1$4X11 1E1E1E1E2E 2E@2 N`2 2 <H3 3 &33&J3;3 8<1P< &4 %(4 84X4 `4 x4 4 "4  4E45 5 "5 5 55 "555"V5 $6Q6 666 6666 P7"X7`7h7"Vp7 $7Q7 0888@8 H8P8X8`8 8"889"V9 $09Q@9 999 9999 :":::"V: $:Q: h;p;x; ;;;; (< pDx] ]X]p]]]]](]@]X]p]> f]0]H]`]] ; These are the proposed Widths of the bins in the histogram]]]]](]@]X]x]]Get user inputted dataOriginal Sample Rangemake sure it's a valid cellA@^ *Unload BootForm|k*Get exact location of the cell as a stringNThis string can be used in a Range Object with all usual Properties accessibleFd P!`!$%b'\onds]Get user inputted datadsBoot Sample Rangemake sure it's a valid cellff? A@h *Unload BootForm|k*Get exact location of the cell as a stringNThis string can be used in a Range Object with all usual Properties accessible04 vd P!j!$%b'fxcheck for non empty cells] R P!j$!n!` dis Z P!j$!!`ical R Z P!j$%$p6Writing the Bootstrap Sample in the cell range given, P!j!g, will write over information in cells, destroying this information. Are you sure you want to proceed? r!Original Sample Overwrite Warning$F'lExplic l,|Hd@t8k0k( Get user inputted datath N First cellmake sure it's a valid cellse twotA@vate a *tor thUnload BootFormal|kx*Get exact location of the cell as a stringNThis string can be used in a Range Object with all usual Properties accessiblelRNGd P!x!$%b'.hoicesUser error checker .$!z= | ulaThe selected cell does not contain a formula so the cell value will not change. Please try again.H NL Fatal ErrorJA@FUnload BootFormut|k Second cellly dis P!~!d '2dmake sure it's a valid cellA@ * TUnload BootForm a|0k(d P!~! %%b'2se 2$!z= | t hThe selected second cell does not contain a formula so the cell value will not change. Please try again.H NL Fatal ErrorJA@Fwith MUnload BootForm)| kkNumber of repetitions P!!'6 60andom  Track the Time elapsed ': Get the calculation setting  $!'<If Set to manual '  $( Turn off screen updating $(  Show the Progress Barra P!(^9 general variables not used in this histogram application''get the original sample U]  P!`$!n!` P!`$!!`x R P!`$!n!` Z P!`$!!` R Z P!`$%! R Z+!clear the data sheet of all cells[must give complete name of the cells (including workbook) to access the sheet in the add-inmyBootDataSheet Bootstrap.xla$%!B@n of<handle user's request for more tracked cells, called BootRawalRand P!!heet, 6!.Zordeclare array for results]] Get number of var to be tracked 5  Z! 100,  ! sheet  's mple  A  You have selected more than 256 cells to record. This cannot be accomodated on an Excel sheet. You may have inadvertently selected a large range of cells. Please correct this and try again.HToo Many Cells to RecordJ NLA@F=nor A@r) in a|kxadd a sheet for BootRaw., B@hndom(1EI added 1 to Sheets.Count and used 2 instead of 1 to make the BootRaw5sheet number correspond to the Bootstrap sheet number R !`BootRaw !`  R$!names taken can't use that name R$! ( kname wasn't taken so use itBootRaw !`  (get the sheetname for later use] !'7put down cell addresses in first row as labels for data'  Z!  ! !b  $%(  '   size array 6 kx  Run the Monte CarloDetermine if a One or Two Var 2 it's a OneVar  6Draw a bootstrapped sample R P!j$!n!`%Draw one row from the original sampleWInt(Random*numberofrows) + 1 gives equal prob of picking any row from 1 to numberofrowssee Test2 macro in MainModule 2 P!`$!n!` '|2put down the data in the data sheet of this add-inFthis saves time because Excel doesn't recalculate any functions in the user's sheet Z P!j$!!` | Z$ R ZmyBootDataSheet Bootstrap.xla$%%( ,copy from the data sheet to the user's sheetKthe only way to use the Copy/Paste method is to Activate the add-in's sheet] !' Bootstrap.xla$B@myBootDataSheet Bootstrap.xla$%B@H$ R Z $myBootDataSheet$%B@ $B@ P!j!$!!$B@H  P!j P!j!$!!$%B@calculate the user's sheet P!x!$!!$B@ P!x!$$ X 'XFdP P!x!$! +0k( Track Progress  6'82 Update the Caption property of the Frame control. BootForm.Show 80%$ P!(b Widen the Label control. 8 P!!  P!(, The DoEvents allows the UserForm to update.  62 $!% GA@jspecial Burnette outputFor kk = 1 To 21RSheets("BootOut").Cells(i + 1, kk).Value = Sheets("Live").Cells(337, kk + 4).ValueNext$load array if user requested BootRaw P!!'Vinitialize for next rep  Z!  ! V 'V !  V+  kH *put down results if user requested BootRaw P!! R 6 Z  R Z$ R Z $%( Z Rkh#Get Summary stats and put them down 0 > @ B A@WBasicFour(data() As Double, MEAN As Double, SD As Double, MAX As Double, MIN As Double) @to md8k0-There is no variation in your selected cell, ., so no histogram is produced.H No HistogramJ rLA@Fappropk @ F/Copy the Bootstrap sheet to the user's workbook OneVarBootstrap Bootstrap.xla$%B@ R !` Bootstrap !` R$!names taken can't use that name R$! (XkPHname wasn't taken so use it Bootstrap !` (Put down first 100  6d $!% $0  $( > + @ + B +  +Make histogram**** Width histrange 6^ B   6$'Nd B   6$T'NknHistogramMaker FirstCellArray, myReps, 31, xmean, 0, 0, 0, ActiveSheet, ActiveSheet.ChartObjects("EmpHist"), 1^(values, noobs, Nobins, center, manual, minval, maxval, histsheet, histogram, whichhistonsheet P!x!B2$( P!x!AL1$( %P( %$.histsheet.Activate !'P 0 6 N  B    A 5This is the end of the One Var Monte Carlo simulationdit's a Two Var 64@  6Draw a bootstrapped sampleDraw a bootstrapped sample R P!j$!n!`%Draw one row from the original sampleWInt(Random*numberofrows) + 1 gives equal prob of picking any row from 1 to numberofrowssee Test2 macro in MainModule 2 P!`$!n!` '|2put down the data in the data sheet of this add-inFthis saves time because Excel doesn't recalculate any functions in the user's sheet Z P!j$!!` | Z$ R ZmyBootDataSheet Bootstrap.xla$%%(,copy from the data sheet to the user's sheetKthe only way to use the Copy/Paste method is to Activate the add-in's sheet !' Bootstrap.xla$B@myBootDataSheet Bootstrap.xla$%B@H$ R Z $myBootDataSheet$%B@ $B@ P!j!$!!$B@H  P!j P!j!$!!$%B@calculate the user's sheet P!x!$!!$B@ P!x!$$ P!~!$$ X 'XFdh P!x!$! +0 P!~!$! +4k  Track Progress  6'82 Update the Caption property of the Frame control. 80%$ P!(b Widen the Label control. 8 P!!  P!(, The DoEvents allows the UserForm to update.  62 $!% GA@j$load array if user requested BootRaw P!!'Vinitialize for next rep  Z!  ! V 'V !  V+  k *put down results if user requested BootRaw P!! R 6 Z  R Z$ R Z $%( Z Rk#Get Summary stats and put them down 0 > @ B A@ 4 D F H JA@WBasicFour(data() As Double, MEAN As Double, SD As Double, MAX As Double, MIN As Double)d5There is no variation in one of your selected cells, . or  2, so no histogram is produced.H No HistogramJ rLA@Fll you/Copy the Bootstrap sheet to the user's workbook TwoVarBootstrap Bootstrap.xla$%B@ Bootstrap !` (Put down first 100  6d $!% $0  $( $4  $(  > + @ + B +  + D + F + H + J +Dim myMin As DoubleDim myMax As DoubleDim myHistWidth As DoublemyMin = Application.WorksheetFunction.Min(Application.WorksheetFunction.Min(FirstCellArray), Application.WorksheetFunction.Min(SecondCellArray))myMax = Application.WorksheetFunction.Max(Application.WorksheetFunction.Max(FirstCellArray), Application.WorksheetFunction.Max(SecondCellArray))"myHistWidth = (myMax - myMin) / 10Make histogram[SuperimHistoMakerScaled FirstCellArray, SecondCellArray, myReps, 31, (xmean + x2mean) / 2, 0, 0, 0, ActiveSheet, ActiveSheet.ChartObjects("EmpHist"), "Var1", "Var2",' myMin, myMax, myHistWidth, 2 get width %P( %$. 6^ B   6$'Nd B   6$T'Nkx 6^ H J  6$'Pd@ H J  6$T'Pk Label Output P!x!B2$( P!~!C2$( P!x!I4$( P!~!K4$( P!x!AL1$( P!~!AM1$( 0 4 6 6 N P  B J H     A<(values1, values2, noobs, Nobins, Center, manual, minval, maxval, histsheet, histogram, myname1, myname2, histbegin, histend, histwidth, histno)5This is the end of the Two Var Monte Carlo simulationk Find Time elapsed and report it 'L L : Q$( 6$( Return the calculation setting < $(One last calculationA@Put away the form A@r Turn off screen updating $( X There were X_ samples drawn where at least one of the tracked cells could not be evaluated (#NUM or #VALUE).H rLNumber of Bad Samples DrawnJA@FkoH Put away the form PA@ro]d P!x!$%b' n+Your entry for the One Cell to be tracked, P!x!2 , is not a valid cell address. Please try again.HA@F'*|d'*|k+Your entry for the One Cell to be tracked, P!x! , has caused error,  n . Please try again.HA@Fo@0] d P!~!$%b' n.Your entry for the Second Cell to be tracked, P!~!2 , is not a valid cell address. Please try again.HA@F'*|XdP'*|@k8.Your entry for the Second Cell to be tracked, P!~! , has caused error,  n . Please try again.HA@Fo8]xd P!`!$%b' n/Your entry for the Original Sample cell range, P!`!0 , is not a valid cell range. Please try again.HA@F'*|d'*|k/Your entry for the Original Sample cell range, P!`! , has caused error,  n . Please try again.HA@Fo]d P!j!$%b' n3Your entry for the Bootstrapped Sample cell range, P!j!0 , is not a valid cell range. Please try again.HA@F'*| d'*|k3Your entry for the Bootstrapped Sample cell range, P!j! , has caused error,  n . Please try again.HA@FohPrun link checker I1$(x>HA"make a chart on the existing sheet 2B@h   (Plea add a seriesHA@F 2B@h4  (aa1:ab3 % B@C one seriesEmpHistChart 1 %6(apsize itl ]" !'PB@|size it !> %n!: EmpHist %(: %!EmpHist %((:  % B@H B9@ 9Dq  !B@H  !( !<B@HH B@6h  % B@HP !>nk c B9@ 9Dq8:  9F 9H 9Jq:  % !LB@H !B@H,   % en the99q9  % approp99q9 9$ Empirical5&!((dPleaseqP9 9F 9H 9Jq"make a chart on the existing sheet add a seriesaa1:aa3 % B@C two seriesEmpHistChart 1 %6( ~ P$! B@|  % B@He se !> B9@ 9Dq  %!EmpHist %(  9F 9H %!EmpHist %(pHis 9Jq !B@H  !( !<B@H B@6  % B@H !> B9@ 9Dq7  9F 9H 9Jq6  % !LB@H !B@H  % 99q6  % 99qP6 9$ Empirical5&!((dq66 %n!8EmpHist %(8 ~ P$! B@| %n!8EmpHist %(8 %n!: EmpHist %(: %!EmpHist %( B B@H B@H P!x!B2$(A1$B@H P!x!B2$(A1$B@HHCAttribute VB_Name = "BootForm"  Bas|0{AA93DEBC-432D-4A76-B8CD-060F8851F2C1}{B992970F-F3A2T14-81D2-914122F04BAC} dGlobalSpacoFalse CreatablPredeclaIdTru BExpo se0TemplateDerivCustomizD Option 1licit Pub CellV alidSngTest As lean9= Sub CommandBut@ton1_C!k()Dim iLong First0 0 Array()9DoHSepcondQGm`yreps!P@ctdoneSinglDmystarttir1MSP@" C= 18 Too$b$ ).Rows.Cou`12Aj ?m(>Columnj:IsEmptqL2QGQZGo%Pue1CU5%U NI SDWcR`'bFF:awc?G4bbA bob~absSbbt bpb ϖ 142bneAt_F|a'UA{err FbDInP(1,C).0Amuljap(=)Tбp) )i Track the Time elapsedkstartt= Now .Get,calcula se&ng your Applic".C."S"Hanual S!xlM' Turnf screen u\pd3A %SUÄ &Fals@howdProgress Bar Frame .Visible!Tr' general varia s not u in,is hist!-am a `ViewT $AllLong@(gZorigin@sampl@/Aw OS() As Dou!LRange(8RefEditK ).Rows.Count, Column@ ii77!j $GHii,8 j)Is(CN CrclearBT`a she;{a0ll c`t@{'must give co=te n@Pas (in cludi`Eworkbook) to accAX bPe add- in Ws("?strap.xla").S my!Datag" .Cabhand```\r'sque@for@ more ` cHked, l RaIfCheck1c&`.j The`aG"1Act#W@indow.Sheleo+dPecla@aa ! zult*e dcVar]hIntege`t' nF@}a-b F !REach a In .AreaACa.+PA/=+ 1!K C#L ac,> 256+ aMsg`/ prLt:="You haOs*69=COLrecord. can!!Oomo [ on an Ex`\. mayC dvertepntlyǡ8a 7ge r!A^b. POsAb>r! PMy again.",tleToo `y ,a5R"!@Butt@:=vbCrit"Unload ME3@M j=lMD4 h.Ad`'I!o1$L Rb2pbstep@rmak%x=6's$(`-po><$Ec)W"r@*" &> 0&7#M)..NQH9'Is t n>n'oNatBKS;F=& 2Gopcontinxue3suT wasQsow itBm 4'uvWa BwHStrX :37;= MTpu|t L`"sesуf row as1belfsBMc tGotG GllTI s(C )J@(1, i)ZC!. GQ i# J3siziZ5t.ɩԆL`  DRue M"e@ CarlopDetermineh if:O jTHwo pT `RSPAndAB.""Q 'itPma ?rT4brDra wt7p>ř1:ԗ1Sbc'oP !f@[nQ 'k(pdom*ų@ows)…s eq _bqVpickфany'Hse@02 macro*M XModuYmya =nt * z2r 2O0; tP4b2smKbeca=DjdoeDe$@*fun9`TFя3!6_On5)c oooTѫV:fATS'copy fX'Aon|wp} 1"C`/Pa`m method zPaA364UUser0kUU6Td?8O{HtO"Få%}(3(bQ1),3` - Sja)pyTȷooks(UserB).Activate Sheets(Range(ptForm.RefEditi Mod ApplicaL.Wor"kªFunon.Max(1,\_50y0! jsBurnette output' kk1 J2 L'D"#Out").Is(i P,)aELive3374$q Nex '@load aT ifr requexd Ra@"a!&.CheckBox1Fh 0 'inbalizeB n@ p Each` a In c.AreaWCac"_" Fr(i$ $CC C a+gm$i'2 down@sults_#cZ#aii7apRpertycWe F e trol>.(2U.Ci$= ӧ(00 %"y Wide?_ʿSwi5 * #[p- 10 0DoEv7@ll yQ= 2Odi0d Appl۠.@rbFdچx/ 50!C5load a ifg9jqu@xRawƆCheck01s-0S-kQV 0 'in0alize n`pBpEachI n +.AreVa"0 RC@~ a.2Qw @v2A14(jkkNC h Csi na4Ԓiҙresult zṛ1 iƚ#3 ~@@PX N1la¹uzFue = BootRaw(ii,@ j) Next j ii End If 'Get Summary stats a 0 The7aWMsgBox prom pt:=" re we" &+ & " sdr@awn what leone%Gtra/d c!< could `not bCYuatC(#NUM or #VALUE).", Buttons:=0vbIn!-, TitleNumber ` bD" Q!Sub:Priv  CommR 2_C 9k(a' 1 StringTe st On Err@Resu bNex`Dim CA As # 2 = ڳ).Address( ernal:=!>@>@ @1004X=YU}try ,OV! tok!58,>&@E"L ,!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{ a]@ !'he w(get all the links in the active workbookea %'e ,check to see if any links are to this add-in ]X $p n   $ 'are  do nzdk?Warning! This workbook is using formulas available only in the { add-in. If you move the workbook, uninstall the add-in, or otherwise break the link, the formulas will show a #NAME error.' there are no links so do nothing d there are no links so do nothingkiKAttribute 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 ' eIfNext i   < E  rU~| krU $`n-0* pHd VBAProject4@j = r ecD/ J< rstdole>stdole f%\*\G__SRP_eLg_VBA_PROJECT-dirN1__SRP_0 $ 2b=DimDElseUformat_If(RandomresumeThenwidth Excel+ VBAWin16~Win32MacVBA6# VBAProjectstdole`OfficeuMSFormsC RefEdit ThisWorkbook| _EvaluateWorkbook_AddinInstall(AddBootstrapMenuItemMessageWorkbook_AddinUninstallmyTools=' Application*OperatingSystem[LanguageSettings LanguageIDmsoLanguageIDUI5msoLanguageIDEnglishUSuInputBox  CommandBars ControlsKDeletex Sheet1 Sheet4 Sheet5 MainModule  (ShowBootFormXReferenceStyle9xlR1C1pMsgBoxRpromptmTitle~Buttons] vbCritical+} BootFormShow(Log107HXoLogdjaBootstrapIsHereVisibleCount0vCaptionxnewItemICommandBarControlPAddrmsoControlButtonnOnActionErronumber- vbInformationnresponseChrK~(test4(test2c mybootrowyli` (testwriting$P WorkbooksBSheets CellsClear Histogram''(LimitsPminvalpmaxvalxx_oldy$yys_absminxrem$adjustymultoldwidth(HistogramMaker1Varvaluesnoobsr histsheet histRangeO& ViewTableAllLongbinleft;~bincount!MinMaxȲmysheetb Worksheetchoice{colHvalrangecvarnameYcNobinsjmincompgmaxcomptLargeyRangeRange xRangeWleftCVtopC. precision statRangeAxupwxdown myboxRange (NewLimitsl(DriveFormatting TableRange7myWidthoomaxwidth(MakeBins ColumnWidthColumnsp9AutoFit&  tickwidth ActiveSheet%N ChartObjectsHActivate| ActiveChart  ChartType?xlXYScatterLinesNoMarkersv SetSourceDataaSourceGPlotBy$ xlColumnsh ChartAreaAxesb xlCategoryZHasMajorGridlinesHasMinorGridlines)xlValueMainModulej`)Histogram1z445b71a8Histogramx7 RNGandSortModule50446308ea RNGandSortModulehSDModule26445b71c4SDModulez"$BootForm4x446308c9QBootFormRr Sheet223445b71a8 Sheet2۔"LinkCheckerModule5144630917"LinkCheckerModulel 0x`HH/L#v0 !E%ao tq L “@^+Cxx7:Ynn#OC.%EkĦ1+.Mg$[ ~`)M ) myrepsPctdone mystarttime yoursettingFxmeanxSD!xmaxx2mean-x2SD@\x2maxHx2minf myendTimew1t_w2u_ii]jj]kk] myBadSampleso BootRawRange'zOriginalSampleRange!CellStringTestOriginalSampleRangeRefEditOriginalSampleRangeAddressexternalwBootSampleRangeCellStringTestBootSampleRange$RefEditBootSampleRange3$ myrangecheckNHRowsUIsEmpty  vbOKCancelx? continue1pCellStringTestFirstCellRefEditOneCell;FormulaqY vbTextCompare QRefEditTwoCell~CellStringTestSecondCell` TextBoxRepsKoNow% CalculationhxlCalculationManualScreenUpdating!  FrameProgressOriginalSample0 CheckBox1RangeSelection7~BootRawJ myBootRawVarCountmAreasK continue3rmyBootRawSheetName4 Repetition1UserBookeActiveWorkbookCopyƿParent PasteSpecialKPaste xlPasteValues" CalculateIsErrorU LabelProgress}Before continue2qDisplayGridlines Repetition2CommandButton2_ClickUserForm_Click Sheet2Workbookk_B_var_i _Defaultj_B_var_myAddIn(Uniform?O (UniformRNG  UniformRand76(TestUniformRandv(UniformBetweenT(TDraweDF\TRand (TRNGmyzRandDmyyRand myyRandTmpt(ExponentialRNGOExponentialRand0lowert(TestExponentialRNG3? myTestVectorB(Expo{ AddinInstallAddinUninstallItemz _B_var_LeftQ _B_var_Chr\;_B_var_response Module1b LinkCheckerModule# ( LinkChecker myLinkStringaLinks LinkSourceskmyPosѰUserFormNCommandButton1|CommandButton2}Label3,Label4," 1    &#-()+.34/2R,5Label5,Frame1Frame2Frame4Label6,Label7,ClickmyFirstCellInitialValueEmySecondCellInitialValue@myOriginalSheetName$MCFormI_B_var_myOriginalSheetName* _B_var_IfP _B_var_Format _B_var_aLinksg _B_var_On6ChartsmyCurrentMCSimSheetShapes<Top5Height|LegendPBorderWeight1 xlHairline4 LineStylew MajorTickMarkw MinorTickMarkTickLabelPosition~MajorGridlinesW_B_var_myCurrentMCSimSheetĔmyBootsheetNameCxlThinw xlContinuous[ (NormalRNGTestresultur pivotValueOldpmiddleXx3_checkocheck2ptempe(CheckforSmaller_B_var_starttime_B_var_X InternationalxlNonEnglishFunctions_B_var_j (SumStatsN(Example1h(RandomNV"_ #Q   9 ;=!?"$@`@ @`!H@P$HA {00020430-C 0046}#2.0#0#C:\WINNT\system32\c2.tlb#OLE Automation^DOfficDO>fic5DD2DF8D04C-5BFA-101B -BDE5DAA5B42Dgram Files\CommonMicrosoft Shared\@10\MSO`.DLL# 10.0 Ob Library#KMSForms>!SFr ms3D452EE1 -E08F`A-8-02608C4D0BB4R@DOWS\SSFM20L'B @_&/;"1jpAp00}#@8p# 50 AAD148C92-33D8-4C6B-8047-F6F2D8564F5@FDOCUME~1\user\L OCALS@Temp\VBEa6.exdb; .E .`M C1RefEditgRfE@bi3wH2p4517Eй1ŹK\OFFICE11\REFEDITm! " Control44B5a40oD4BBE40O3AC-470C-9126-F5DF4C88A89F}#F44`J @DEaFEA2 a0ThisWorkbookGDThsDWikboPk2UbH1U@","" +Sheet1G Se Gt1 2!  FU 4 4 4 4 a0E= 5 j5 5 5 )(y aMainModuleG,Ma @2=22922=22?@ ;2LinkCheckereEAL2kCbHc=`Pe !62? ? l9rU~~~~~~~~~m          1 1( a8 1H  with columns as numbers. Please execute Tools: Options, click the General tab, and uncheck the R1C1 reference style option. 8Unacceptable Reference StyleļCZ MSqHQh2|ݖ+:W7O~ ֟Tlxrgc;G`Rͥ )H cNdFiHmDAIDFWorkbookWorkbook_AddinInstallWorkbook_AddinUninstall @ ShowBootFormtesttest2 testwriting F WS F Tools Herramientas Bootstrap ... $Worksheet Menu Bar F F FControls DeleteVBE6.DLL i FFY FrameProgressp The 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 TOOLSM ,This add-in will not workFF Cells Clear R X Q x   K  f   BasicFourMeanSDExampleExample2 Hi 2DF must be greater than 0 ValueSumStatsExample1  FF lWriting the Bootstrap Sample in the cell range given, 5 , will write over information in cells, destroying this information. Are you sure you want to proceed? BOriginal Sample Overwrite Warning =ChartObjectsRowsTop Height2 The selected cell does not contain a formula so the cell value will not change. Please try again. Fatal Error RangeexternalAddress OneVarBootstrap Before BootstrapF aa1:ab3 EmpHist Chart 1 ShapesLeft6 The selected second cell does not contain a formula so the cell value will not change. Please try again.ҋB `ƏYl|CҋB ``F BootRawName SelectCopy PastePasteSpecialCalculate 0%pj).`Columnsa |You have selected more than 256 cells to record. This cannot be accomodated on an Excel sheet. You may have inadvertently selected a large range of cells. Please correct this and try again. 0Too Many Cells to Record#=h8+3qG?,2qL@@ widthInteriorColorIndex Border WeightLineStyleMajorTickMarkMinorTickMark "TickLabelPositionMajorGridlines "HasMajorGridlines "HasMinorGridlines EmpiricalxFLimitsHistogramMaker1VarHistogramMaker2VarMakeBins FindPrecisionLog10FormatSumStatsDriveFormatting NewLimits  ZThere is no variation in your selected cell,  <, so no histogram is produced. No Histogram B2 A1 AL1 TwoVarBootstrap aa1:aa3 jThere is no variation in one of your selected cells,   or  C2 I4 K4 AM1 LinkChecker I1 There were 1 samples drawn where at least one of the tracked cells could not be evaluated (#NUM or #VALUE). 6Number of Bad Samples Drawn .  Histogram of  " @@` / p   V  VYour entry for the One Cell to be tracked,  d , is not a valid cell address. Please try again. * , has caused error,  * . Please try again. \Your entry for the Second Cell to be tracked,  ^Your entry for the Original Sample cell range,  ` , is not a valid cell range. Please try again. fYour entry for the Bootstrapped Sample cell range, ClearFormats! ~Warning! This workbook is using formulas available only in the ? add-in. If you move the workbook, uninstall the add-in, or otherwise break the link, the formulas will show a #NAME error.ColumnWidth No. Obs.  to ActivateMinimumScaleMaximumScaleRow Column No. Obs. in  And Show 0 0.NumberFormatFwTJ?@ABCDEFGHIJKLMNOPQ_dUVWXYZ[\]^`gabce*fhjtlmnopqrsiuvwxyz{|}i02* n```m#`m#f}o)+|0CompObjzpVERSION 5.00 Begin {C62A69F0-16DC-11CE-9E98-00AA00574A4F} BootForm Caption = "Bootstrap" ClientHeight = 6675 ClientLeft = 45 ClientTop = 345 ClientWidth = 3465 StartUpPosition = 1 'CenterOwner TypeInfoVer = 124 End ThisWorkbookThisWorkbookSheet1Sheet1HistogramHistogramRNGandSortModuleRNGandSortModuleSDModuleSDModuleBootFormBootFormSheet4Sheet4Sheet5Sheet5MainModuleMainModuleSheet2Sheet2LinkCheckerModuleLinkCheckerModuleID="{BFAB8BED-F766-41EA-BB2A-046A284F3CA2}" Document=ThisWorkbook/&H00000000 Document=Sheet1/&H00000000 Module=Histogram Module=RNGandSortModule Module=SDModule Package={AC9F2F90-E877-11CE-9F68-00AA00574A4F} BaseClass=BootForm Document=Sheet4/&H00000000 Document=Sheet5/&H00000000 Module=MainModule Document=Sheet2/&H00000000 Module=LinkCheckerModule Name="VBAProject" HelpContextID="0" VersionCompatible32="393222000" CMG="7D7F506AD034D434D434D434D4" DPB="43416EA835A935A935" GC="090B2439253925C6" [Host Extender Info] &H00000001={3832D640-CF90-11CF-8E43-00A0C911005A};VBE;&H00000000 [Workspace] ThisWorkbook=220, 290, 1010, 910, C Sheet1=0, 0, 0, 0, C Histogram=0, 0, 235, 511, C RNGandSortModule=154, 203, 944, 823, C SDModule=66, 87, 856, 707, C BootForm=154, 203, 635, 734, C, 22, 29, 503, 560, C    !"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIKMOPQRSTUVWXYZ[\]^_`abdefghijklmnopqrstuvxy{~ Sheet4=0, 0, 0, 0, C Sheet5=0, 0, 0, 0, C MainModule=110, 145, 900, 765, Z Sheet2=0, 0, 0, 0, C LinkCheckerModule=0, 0, 0, 0, C EFOh+'0 PXl  BootstrapBarreto/Howland0Bootstrapping Data Analysis Tool 11 Aug 2005Humberto Barbedded ObjectForms.Frame.19q4("Select a Second Cell to be Tracked 5Times New RomanH$F 5Times New Roman՟8H  /}SOptionalR KQDBTimes New Roman(2\Label4X @%RefEditTwoCelln{Click on a second cell to be analyzed|  DBTimes New Roman8KEFҋB `EF@, #FramePro n``Microsoft Forms 2.0 FrameEmbedded ObjectForms.Frame.19q"1Tahoma<H   }S"Progress BarR KQDBTimes New Roman8.0 20LabelProgress (ProceedqNqTahomad(CanceldENqTahomadLF n``Microsoft Forms 2.0 FrameEmbedded ObjectForms.Frame.19q=>?@H${ 5Times New Roman0( Select Single Cell to be Tracked 5Times New Roman@H,{1000 5Times New Roman8(%Enter the Number of BootRoot Entry F$1 BTvWorkbook?՟_VBA_PROJECT_CUR"=% !q#VBA; !`m#PROJECTlkSummaryInformation(>@DocumentSummaryInformation8,CompObjmstrap Samples|  5Times New Roman8H  $ }S RequiredR KQDBTimes New RomanP @ RefEditOneCelln{Click on the cell to be analyzed(2XLabel3L DTextBoxRepseqEnter the number of repetitionsd(2`Label5  n``Microsoft Forms 2.0 FrameEmretoMicrosoft Excel@5- @#՜.+,0 PXd lt| G DocOneVarBootstrapTwoVarBootstrapmyBootDataSheet  Worksheets F!Microsoft Office Excel WorksheetBiff8Excel.Sheet.89q n``Microsoft Forms 2.0 FrameEmbedded ObjectForms.Frame.19qH${ 5Times New Roman4($Select Cell Range of Original Sample 5Times New RomanD(4Select Cell Range of Output for One Bootstrap SampleN 5Times New RomanH${ 5Times New Roman8H  )}S: RequiredR KQDBTimes New Roman8&@RefEditOriginalSampleRange{('2\Label6((2lLabel74)@RefEditBootSampleRange  #{0| Record All Selected Cells (256 Max) 5