@\ Andre Lehre 8\K ./DNXoeucB d MbP?"*+_%G1 Geneva1 Geneva1 Geneva1 Geneva1 Geneva1 Geneva&?'?(?)?Mz,, g(,, d'h d@# unit_typeV General00.00#,##0 #,##0.00#,##0_);\(#,##0\)#,##0_);[Red]\(#,##0\)#,##0.00_);\(#,##0.00\)#,##0.00_);[Red]\(#,##0.00\)"$"#,##0_);\("$"#,##0\)"$"#,##0_);[Red]\("$"#,##0\) "$"#,##0.00_);\("$"#,##0.00\)%""$"#,##0.00_);[Red]\("$"#,##0.00\)0%0.00% 0.00E+00 #\ ?/? #\ ??/?? m/d/yy  d\-mmm\-yy d\-mmm mmm\-yy h:mm\ AM/PMh:mm:ss\ AM/PMh:mm h:mm:ss m/d/yy\ h:mm abort,I a_high,k  a_low,g  b_high,m  b_low,i  changeover,o   custom_items-eo    date,O  qDischarge_Calculation,Gqendgh,W qendtime,S qmeter,] qnumvert, qoktest,] q qqrating,b  qq Setup_data.DB-Kq qstartgh,U q starttime,Q q streamname,M qunitsftq unit_type,Y cC C  C  C  C  C C C C C C C C C C C  C  C  C  C  C  C  C  (C  (C  (C  C  (C  (C  C  `C  (U } } } } U} } * } U K _D6N rO?=aD67tE8 :uA 7p   B BE)OoJrE9A G'EfAMRODQU2* DISCHARGE V 2.0;3AN EXCEL MACRO TO CALCULATE STREAM DISCHARGE FROM 91 CURRENT-METER MEASUREMENTS USING THE 0.6D METHOD2* written by3+ Andre K. Lehre4, Department of Geology6. Humboldt State University3 + Arcata, CA 95521 > 6 Copyright 1989, 1994 by Andre K. Lehre> 6 DISCHARGE may be distributed freely for A 9 non-commercial uses. It may not be charged% for in any way.KC This macro has been protected in order to prevent inadvertentF> modification. The password is "Leopold".5-TO ABORT PROGRAM AT ANY TIME SIMULTANEOUSLY =5DEPRESS THE COMMAND (CLOVERLEAF)AND PERIOD BUTTONS. C;Unless otherwise indicated, clicking "CANCEL" in a dialog #box will abort the program.A9The program uses the following standard rating equations:IA Pygmy: V = 0.028 + 0.977(R/T) English R/T< 5.0NF V = 0.0085 + 0.298(R/T) metric R/T< 5.0 KCPrice AA: V = 0.02 + 2.18(R/T) English R/T < 1.0ME V = 0.0061 + 0.6645(R/T) metric R/T < 1.0QI V = 0.03 + 2.17(R/T) English R/T > 1.0ME V = 0.0091 + 0.6614(R/T) metric R/T > 1.0!DO"SN#SOB$M %VDA&P'PED)*EAE+E@-F//011/2R4VO5EEB7;8?:";N<K>@AF?GO!GRating equations are of the form: V = a + b*(R/T). To use a different O"Gset of standard formulas, enter the metric and English 'a' values into I#Amacro cells B248 (pygmy), B238 (AA low range), and B242 (AA highR$J range). Enter the associated 'b' values into cells B250 (pygmy), B240 L%D(AA low), and B244 (AA high). Put AA changeover R/T value (value ofL&D R/T for switching from high to low range) in B235. Put maximum R/T'for pygmy in B252A)9If you enter a custom rating (i.e., you don't want to useA*9a standard rating) the program assumes you will enter theB+:rating equation in whatever units you choose at the start.+-#Data required at each vertical are:-/%tape distance: position on tape +0#D: water depthN1Frevs (R): number of revolutions of meter in time interval TR2Jtime (T): time interval in seconds over which revs were countedA49Number of verticals, including LEW and REW, must be known75/Enter data starting at LEW (left edge of water);73In "date of measurement", the macro is set up for 8day-month-year format.J:BAfter the tape distance, depth, revs, and time data are entered inG;?the worksheet, choose "Calculate Now" from the Options menu (or<<4type command-=) to make the worksheet be calculated.C>;To PRINT the results of an analysis, drag with the mouse toD?<select the area of the worksheet to be printed. Choose 'Set@ XODA DNB IBOC G D GADE FG ADEH nI -EAJ @K L M N O P OQ BER S T U V W FAX Y Z kD[ \ >] ^  @_ @@8Print Area' from the "Options" Menu. Now choose 'Page EA=Setup' from the "File" menu and set margins, orientation of CB;worksheet for printing, whether gridlines and column heads CC;show, header, etc. Choose 'Print' from the "Edit" menu to BD:print the document. Check the 'Preview' box in the Print =E5dialog box to preview the document prior to printing.GCommandGDischarge Calculation6G.Calculates water discharge from current-meter HHH measurements.IIOption-command-qI(To run program)IIIIIII I I JJJJ Jitem Jx Jy JwidthJheight J textJ  init/resultJ KK B_KDisplay status barK~ KT@~ K~ K@~@~ K@z@K Gaging Worksheet Set-upK L#L#Al-L%Initialize test for "OK" button pressL~ L@~ L.@~ L"@L  Site name:L M#M# AlMInitialize stream nameM~ M@~ MZ@~ M@~ Mu@ M  M N#N#  AlNInitialize dateN~ N@~ N.@~ NF@ N Date:N O#O# Al OInitialize starting timeO~ O@~ OZ@~ OC@~ O`e@ O  O P#P# AlPInitialize ending timeP~ P@~ P.@~ PU@P starting time:P Q#Q# AlQInitialize starting GHQ~ Q@~ Q_@~ QS@~ QR@ Q  Q R#R#  AlRInitialize ending GHR~ R@~ R.@~ R\@R  ending time:R S#S#Al$SSet default units at EnglishS~ S@~ S_@~ S[@~ SR@ S  S T#T#Al"TSet default meter at pygmyT~ T@~ Tm@~ TU@T  starting GH:T U#U#Al&USet default rating at standardU~ U@~ Uu@~ US@~ UR@ U  U V)V#y&1?Al(V Set default a as English pygmy aV~ V@~ Vm@~ V\@V  ending GH:V W#W#AlWW~ W@~ Wu@~ W[@~ WR@ W  W X)X#$C?Al(X Set default b as English pygmy bX~ X,@~ X2@~ X b@~ X[@~ XR@ X unitsX Y#Y#AlYY~ Y&@~ Y ?Y Z#Z#Al#ZSet default changeover at 5Z~ Z(@~ Z<@~ Zd@~ ZR@~ Z4@Z EnglishZ [[#` PAl j@i@j@i@j@i@j@i@j@i@j@2[*Initialize custom rating input as inactive[~ [(@~ [<@~ [g@~ [@Q@~ [2@[ metric[ \$\CQ%A7\/Begin loop for creating and altering dialog box\~ \,@~ \@c@~ \ b@~ \`@~ \W@\  meter type\ ]oktest$]B@@# A2]*Put up dialog box for entry of set-up data]~ ]&@~ ] ?] ^<^*@C % $j@A5B0^(Bypass rest of loop if OK button pressed^~ ^(@~ ^d@~ ^d@~ ^^@~ ^2@ ^ pygmy^ _6_$@CY  B7B2_*Exit macro if cancel clicked in dialog box_~ _(@~ _d@~ _g@~ _^@~ _2@_ Price AA_ ` lDOa Nb OBc  d DAe f EDg h (AEi @j k l m n o Op EBq r s Tt u kv AFw Fx Gy FDz H{ I| E} 2@ ~ 5 2`B`0@C  #AlB=`5Select custom rating input if "other" chosen as meter`~ `\@~ `d@~ `j@~ `^@~ `2@ ` other` a)a@C  BBa:Activate custom rating inputs if "custom" chosen as ratinga~ a,@~ as@~ a b@~ aa@~ aR@a rating equationa bb@# ` QFAl ,@@ @@ @@ @@ @@ @2b*Set rating dialog items to activated stateb~ b&@~ b ?b cZcH@;Enter a and b of custom rating equation in dialog box belowBv*c"Warn user to enter rating equationc~ c\@~ c0t@~ cd@~ c]@~ c2@c standardc dd@Add~ d\@~ d0t@~ dg@~ dR@~ d2@d customd e)e@C  BBe:Loop for deactivating custom input if "standard" re-chosene~ ej@~ e2@~ ep@~ e`{@~ e^@:e 2custom rating equation [ v = a + b*(revs/time)] e fBf0@C  # 1AlB?f7Change meter from custom to pygmy if standard re-chosenf~ fi@~ f?@~ fq@f a (low range):f gg@#O ` QAl j@i@j@i@j@i@j@i@j@i@j@&gDeactivate custom rating inputg~ gj@~ gb@~ gq@~ gR@g y&1?g hh@AhEnd looph~ hi@~ h?@~ hs@h b (low range):h iiA:i2Continue while loop until "OK" button (37) pressedi~ ij@~ ib@~ is@~ iR@i $C?i jjBwj New worksheetj~ ji@~ j`o@~ jq@j a (high range):j k$k Helvetica Bk Set font sizek~ kj@~ kv@~ kq@~ kR@~ k k l2l   %?=B/lSet column width to 11l~ li@~ l`o@~ ls@l b (high range):l mmBW mTurn off screen updatingm~ mj@~ mv@~ ms@~ mR@~ m m n*n g$=Bm!nSelect cell to hold unitsn~ ni@~ n?@~ nu@,n $low -> high changeover (revs/time) :n ooB+o Left aligno~ oj@~ or@~ ou@~ oR@~ o @o pJp8Cw EnglishB`metricB`B)p!Set unit type into worksheet cellp~ p?~ p@l@~ ppx@~ pP@ p OKp q!q unit_typeB='qName worksheet cell "unit_type"q~ q@~ qt@~ qpx@~ qP@q Cancelq rrB.r Protect cellrr scsQ C=metric unitsmBXunitsftBXB-s%Set unit type: true if m, false if ftss t-t %=BmtTitling sequencett uuB+u Left alignuuuuuuu u u vvC2Bmvvv ww 0.0B*www xxC3Bmxxx yy 0.00B*yyy zz C4:C5Bmzzz {{0B*{{{ ||C6Bm|}} 0.000B*}~~C7Bm~ 0.0B*lOD5N5BOB JADB4DEBJEAB@9B;B7BO9BEB6B6B<FAE13DE15E @13 C8:C9Bm 0.000B**#$=Bm2 Discharge Computations forB`*u$=Bm DateB`*$=Bm! dd-mmm-yyB*Format for date*%$=Bm! GH start:B`*$=Bm# Time start:B`*$=Bm GH end:B`*-$=Bm! Time end:B`*$=Bm Units:B`*$=Bm Meter:B`*/$=Bm$ Rating Eqn.:B`-%=BmB+ a =B`-%=BmB+ 0.000B*-%=BmB+ b =B`* $=BmlDO1N<OBE 1DA57ED1BAE;@B5B4B4OBEB1B2B2BAF2B9DB9B3@ B@B+$ for (R/T) < B`-%=BmB+ 0.000B* R10:R12BmB+*%$ =Bm# Tape. Dist.B`*y$ =Bm DepthB`*$ =Bm RevsB`*$ =Bm TimeB`*m$ =BmVB`*$ =BmWB`* $ =BmAB`*]$ =BmQB`*$ =Bm!CB`* $ =Bm!CB`*_$ =Bm secB`*$ =Bm(C/secB`*$ =BmlOD9NBBO@ BADGYDEcREA\@KhUIaJOTBEPfUI_JFAR^DRe3B @3B!CB`*s$ =Bm(sq. C B`*$ =Bm/cu. CO /secB` $Bq( Scroll to left edge of worksheet*W$=Bm'Select cell to hold stream name!&C{B`Put stream name in cell*&$=Bm Select cell to hold date!C B`Put date in cell*)$=Bm,$Select cell for starting gage height 0.00B*'Set display to 2 decimal places!'C#B`Put GH in cell*'$=Bm%Select cell for starting time'0B*Set display to integers!f(CB`!Put starting time in cellf( BqScroll left two columns*f(A$=Bm*"Select cell for ending gage heightf( 0.00B*'Set display to 2 decimal places!)C MB`Put GH in cell*)$=Bm#Select cell for ending time)0B*Set display to integers!)CB`Put ending time in cell*&*$=Bm"Select cell for meter typem[C D pygmyB`8C'  AAB`B`@"B0(Put name of meter type in worksheet cell meterB=#Name worksheet cell "meter"*$=Bm)!Titling sequence for rating eqns. a =B`*%$=Bm b =B`*&*$=Bm&*B+lDO<NZOB1 4DAphEDhBAEq@khhiOEBjjMhAF__Dc|@ 1-$&* for (R/T) > B`B&*0))$=5$=Bm&*B+&* 0.00B*6$ changeoverm $=B=( Name worksheet cell "changeover"2 a_high $=B=$Name worksheet cell "a_high"2 b_highu $=B=$Name worksheet cell "b_high"*"Entry of standard rating equations)C @ B6.Begin loop to put standard rating on worksheet)@C  B0(Begin standard rating for Price AA meter'@# Al/'Set standard changeover R/T value = 1.0Q?@C= ))$='$=Ba/'Put changeover value in worksheet cells.@$=Bm( Select cell to hold low range'a'dR@ ]C v=metric HPx?B`{Gz?B`B<4Insert low range 'a' value for AA, metric or English.@ $=Bm)!Select cell to hold low range 'b'dR@ C =metric $C?B`q= ףp@B`B<4Insert low range 'b' value for AA, metric or English.@!/$=Bm*"Select cell to hold high range 'a'dR@!C =metric +?B`Q?B`B=5Insert high range 'a' value for AA, metric or English.@!$=Bm*"Select cell to hold high range 'b'dR@"C!>=metric |a2U0*?B`\(\@B`B=5Insert high range 'b' value for AA, metric or English@A"Go to computation sequence)@C  B-%Begin standard rating for pygmy meter.@"$=BmSelect cell to hold 'a'dR@#%C!=metric  rh?B`y&1?B`B5-Insert 'a' value for pygmy, metric or English.@#Y$=BmSelect cell to hold 'b'dR@#C"=metric On?B`$C?B`B5-Insert 'b' value for pygmy, metric or English'@#k Al*"Set max. R/T value for pygmy meter9'@C# $6$=B`1)Put changeover value in worksheet cell G8H6@))$J $=$$=Ba/'Clear out high range labeling for pygmy@AA( Custom entry of rating equationslODmNBO ADDEEA 1@ - 4 ? JkO]BE3c3NcFAXc^D|\aO @`D%C B6.Begin loop for entering custom rating equation9'@C# %2$=B`80Put custom changeover value in worksheet cell G89'@C %r$=B`>6Insert low range custom 'a' value in worksheet cell C89'@CO %$=B`>6Insert low range custom 'b' value in worksheet cell E8<*@C%  C B$B3+Test to see if there is a high range rating9'@C$ &4$=B`=5Put custom changeover value in worksheet cell G9 also9'@C% &t$=B`?7Insert high range custom 'a' value in worksheet cell C99'@C% '$=B`?7Insert high range custom 'b' value in worksheet cell E9@A  A   Calculation sequence   ffffBW  Update screen  ffffBW Turn off screen updating numvertV D0Enter number of verticals. Include LEW and REW. verticalsBh# Prompt for no. of verticalsB0C $A5#A5BAbort on CANCEL*)i$ =Bm!Select cell for LEW label LEWB`0R[C'} ]C1Bm!Select cell for REW label REWB` R13C6BmSelect first cell for V3!RC:R[C( ]C6BmSelect cells to hold V=IF(AND(RC4>0,RC5>0),IF((RC4/RC5)0,b_high>0),R9C3+R9C5*(RC4/RC5),"beyond rating")),0)Ba Calculate V*$ =BmSelect first W cell,=ABS((R[1]C2-RC2)/2)B`%Compute W for first vertical5#R[1]C:R[C(]CBmSelect W cells.=ABS(R[1]C2-R[-1]C2)/2Ba<4Compute W for second throgh next-to-last verticals /R[C) ]CBmSelect last W cell+=ABS(RC2-R[-1]C2)/2B`$Compute W for last vertical R13C8Bm Select first cell for W3!RC:R[C*C ]C8BmSelect cells for A =RC7*RC3Ba Compute A R13C9Bm Select first cell for Q lDO!`N"DOB#t $dDA%5&4ED'5(4AE)5@*8+5,8-9.P/4O0rEB1M2}3M4v5Q6|AF7]8Q9|D:Z;9<V==@ >G?5 3 !RC:R[C* ]C9Bm Select cells for Q! !=RC6*RC8Ba! Compute Q"0"R[C+V ]C2Bm2"*Select cells for sum and mean value labels## Q =B`6#.Labeling sequence for Q, A, W, V mean, D mean $$ R[2]CBm$%% A =B`%&& R[2]CBm&'' W =B`'(( R[2]CBm() )Mean V =B`)** R[2]CBm*+ +Mean D =B`+,!, R[-8]C[1]Bm,- -RC:R[8]CBm-Select cell to hold Q.. 0.00B*./;/) = SUM(R13C9:RC+ C9)B`%/Calculate discharge: Q = Q00 R[2]CBm0Select cell to hold A1;1) = SUM(R13C8:RC- C8)B`01(Calculate cross-sectional area: A = A22 R[2]CBm2Select cell to hold W3;3) = SUM(R13C7:RC.  C7)B`)3!Calculate channel width: W = W44 R[2]CBm"4Select cell to hold mean V5=5+C  0.000B*B-5%Set to three decimal places if metric6&6=R[-6]C/R[-4]CB`%6Calculate mean velocity = Q/A77 R[2]CBm"7Select cell to hold mean D8=8+C. 0.000B*B-8%Set to three decimal places if metric9&9=R[-6]C/R[-4]CB`"9Calculate mean depth = A/W:!: R[-8]C[1]Bm:; ;RC:R[8]CBm$;Select cells for unit labels<<B+< Left align=/=cu. C /secB`=>> R[2]CBm>?(?sq. C0A B`?@ODA5NB9BOC5 D@ADE5F9DEGaHEAId@JQ@@ R[2]CBm@A!AC0B`ABB R[2]CBmBC(CC0/secB`CDD R[2]CBmDE!EC13B`EF'FC.r Bp(F Scroll to top of discharge tableGWGE "