general excel tips 3
TRANSCRIPT
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 1/40
EXCEL IN ME - PART 3
Keith A. WoodburyMechanical Enineerin!ni"er#ity o$ Alaba%a
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 2/40
EXCEL MATRICE&
There i# a built in #ol"er in Micro#o$t E'cel that can(roduce al%o#t all the nece##ary %atri' calculation#that Matlab i# ca(able o$. tran#(o#e
%%ult %in"er#e
Na%in %atrice# are di)erent than na%incell#*colu%n# becau#e the na%in %u#t be done by
hihlihtin the entire %atri' and oin to the+or%ula# Tab. &elect ,ene Na%e/ and enter thena%e o$ the %atri'. Thi# can be #een on the ne't#lide.
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 3/40
EXCEL MATRICE& 0 EXAMPLE
Three indi"idual# are connected by bunee cord#.!#in Ne1ton2# #econd la1 1e et the $ollo1in$orce balance and $or each u%(er
4567589'6 0 58'8 : %6
-58'6 7 4587539'8 0 53'3 : %8-53'8 7 53'3 : %3
The table o$ (ro(ertie# $or each u%(er i# a# $ollo1#
;u%(er Ma##459 &(rin Con#tant 4N*%9
To( 469 <= >= Middle 489 ?= 6==
@otto% 439 = >=
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 4/40
EXCEL MATRICE& 0 EXAMPLE
No1 let# #et u( our #y#te% o$ eBuation# in E'cel by#ub#titutin our i"en "ariable# into the initialeBuation#
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 5/40
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 6/40
EXCEL MATRICE& 0 EXAMPLE
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 7/40
EXCEL MATRICE& 0 EXAMPLE
&ub#titutin our (ara%eter# 1e et the $ollo1in%atri' eBuation
6>= -6== = '6 >.<
-6== 6>= ->= '8 : <<.? = - >= >= '3 ?.
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 8/40
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 9/40
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 10/40
EXCEL MATRICE& 0 EXAMPLE
In order to #ol"e $or our lenth# 4'9 1e 1ill needto di"ide our #(rin con#tant by our $orce#D
' : 5*%
!n$ortunately there i# no %atri' di"i#ion in E'celthere$ore 1e %u#t %ulti(ly the in"er#e o$ 5 by %
': 5-6%
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 11/40
Make sure to holddown Ctrl+Shiftbefore pressing
enter in order forthe matrix solution
to work
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 12/40
Make sure tohold downCtrl+Shift
before pressingenter in order
for the matrixsolution to work
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 13/40
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 14/40
EXCEL ITERATIFN
Micro#o$t E'cel iteration can (er$or% a G$or-ne'tHloo( 1ith no (rora%%in at all.
In the ne't e'a%(le 1e/ll #et u( a #i%(le counterthat 1ill #ho1 ho1 E'cel/# iteration and circular
re$erence 1or5#
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 15/40
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 16/40
EXCEL ITERATIFN - EXAMPLE
Clic5 on the Microsoft Oce Button 4Circle @uttonin To( Le$t9 and #elect ,E'cel F(tion#/. Fnce that i#done o to ,+or%ula#/ on the le$t colu%n. Ma5e #ure,Manual/ i# #elected under ,Wor5boo5 Calculation/.
A$ter that chec5 the bo' that read# ,Enable iterati"ecalculation/ and #et your %a' nu%ber o$ iteration#to 6. Then #elect ,FK/. Thi# 1ill return you bac5 tothe #(read#heet. ou can then continue the iteration
by (re##in the ,+J/ button.
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 17/40
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 18/40
EXCEL ITERATIFN
Hit the F ke!to continue
the iteration
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 19/40
I&!AL @A&IC MACRF&
&o%eti%e# it i# ea#ier to (lace a $or%ula in#ide a"i#ual ba#ic %acro to #ol"e a (roble%
!#in the %acro %a5e# it ea#ier to identi$y the$or%ula in#tead o$ ha"in to co(y it nu%erou# ti%e#
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 20/40
I&!AL @A&IC MACRF& - EXAMPLE
The Redlich-K1on EBuation o$ #tate i# i"en byD
1here R i# the uni"er#al a# con#tant T i#ab#olute te%(erature ( i# ab#olute (re##ure " i#the "olu%e o$ a 5 o$ a# and a b arecon#tant# 1ith the (ara%eter#D
T bvv
a
bv
RT p
)( +−
−
=
c
c
p
T R
a
5.22
427.0=
c
c
p
T
Rb 0866.0=
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 21/40
I&!AL @A&IC MACRF& - EXAMPLE
The (ara%eter# $or the eBuation areD
(c : <== 5Pa Tc : 6J6K
R : =.>6 5;*45-K9
ou are a#5ed to deter%ine the a%ount o$ %ethane$uel that can be held in a 3%3 tan5 at a te%(eratureo$ -=C 1ith a (re##ure o$ <>=== 5Pa.
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 22/40
I&!AL @A&IC MACRF& - EXAMPLE
The r#t #te( in the (rocedure i# to #et u( a %acrou#in Micro#o$t i#ual @a#ic.
ou %ay need to add the e"elo(er Tab on the,ribbon/ in E'cel 8==?.
ou can do thi# by oin to the Microsoft OceButton 4Circle @utton in To( Le$t9 and #elect ,E'celF(tion#/. !nder ,Po(ular/ on le$t colu%n chec5,&ho1 e"elo(er tab in the Ribbon/ under ,To(
o(tion# $or 1or5in 1ith E'cel/. Then #elect ,FK/.
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 23/40
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 24/40
How to "nd
#isual Basic$Foundunder
%e&eloper
'ab(
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 25/40
I&!AL @A&IC MACRF& - EXAMPLE
Fnce you #elect ,i#ual @a#ic/ $ro% thee"elo(er Tab a ne1 1indo1 1ill ,(o(u(/. Then #elect ,In#ert/ ,Module/.
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 26/40
Select)*nsert and
then )Module
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 27/40
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 28/40
I&!AL @A&IC MACRF& - EXAMPLE
No1 that 1e ha"e our %odule let/# 1rite our $unctioninto "i#ual ba#ic
Fnce 1e ha"e our $unction #et u( in "i#ual ba#ic 1ecan #et u( our (ara%eter# to #ol"e $or ,(/. &ince 1e don/t 5no1 ,"/ at thi# (oint let# %a5e u( a "alue o$
=.>%3*5. Al#o don/t $oret to na%e your "ariable# in E'cel.
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 29/40
Make sure that
the module isin the modulesfolder and notthe workbook
folder
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 30/40
,o here to return toMicrosoft -xcel $.ote/
0our macro does not needto be sa&ed indi&iduall!12s long as the module isshown in &isual basic themacro will appl! to !our
spreadsheet(
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 31/40
Make sure !ou do.O' name this cell
)p as it willinterfere with thename of the macro
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 32/40
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 33/40
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 34/40
I&!AL @A&IC MACRF& - EXAMPLE
No1 that 1e ha"e our $unction #et u( 1e can u#eOFAL&EEK to nd our #(ecic "olu%e by #ettin our(re##ure eBual to <>=== 5Pa
Fnce 1e nd our #(ecic "olu%e at <>===5Pa 1e
can nd the a%ount o$ %ethane $uel 459 in#ide the3%3 tan5
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 35/40
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 36/40
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 37/40
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 38/40
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 39/40
7/23/2019 General Excel Tips 3
http://slidepdf.com/reader/full/general-excel-tips-3 40/40
I&!AL @A&IC MACRF&
NoteD !nli5e Matlab all i"en "ariable# in Micro#o$tE'cel %u#t be 1ritten in the actual #(read#heetin#tead o$ the $unction. oin other1i#e 1ill not i"eyou a re#ult $or a $unction