bus598 excel functions handoutr2.docx
TRANSCRIPT
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
1/46
Goldey-Beacom College
Quantitative Foundations and Business Applications
BUS598
Excel Functions andout
By
Fatma A!del-"aou#
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
2/46
Special $%an&s to Step%anie Foste' #o' %e' elp in Editing $%is
andout
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age 2 o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
3/46
Table of Contents
at% and $'ig Functions.................................................................................................................. /
SU("01UC$........................................................................................................................... /
"A21........................................................................................................................................ 3Statistical Functions........................................................................................................................ 4
C0U2$F................................................................................................................................... 4
C0U2$FS............................................................................................................................... ,,
20".26............................................................................................................................... ,*
A!solute and "elative "e#e'encing................................................................................................,5
7%at-# Analysis............................................................................................................................ ,4
Scena'io anage'...................................................................................................................... ,4
Goal See&................................................................................................................................... **
1ata $a!le.................................................................................................................................. *
Solve'............................................................................................................................................ *8
"e#e'ences.................................................................................................................................... +
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age 3 o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
4/46
Math and Trig Functions
SUMPRODUCT
"etu'ns t%e sum o# t%e p'oducts o# co''esponding a''ay components=SUMPRODUCT ([array1], [array2], [array3],...)
!a"#le$G'ades #o' a student in a class a'e 8+ in exam : 9+ in exam : 85 in t%e ;nal exam: and 9* #o'
t%e %ome
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
5/46
/. Clic& 0&.
. n A''ay, put t%e cells t%at %ave t%e g'ades
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age ' o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
6/46
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age 6 o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
7/46
5. n A''ay* put t%e cells t%at %ave t%e
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
8/46
R%)D
"etu'ns an evenly dist'i!uted 'andom 'eal num!e' g'eate' t%an o' eual to + and less t%an ,. Ane< 'andom 'eal num!e' is 'etu'ned eve'y time t%e
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
9/46
Statistical Functions
COU)T+F
Counts t%e num!e' o# cells
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
10/46
*. Clic& on nse't Function in t%e Fo'mulas ta!: and in t%e Sea'c% #o' a #unction dialog
!ox: type C0U2$F and clic& Go.
/. Clic& 0&
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age -. o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
11/46
. n "ange put t%e cells t%at %ave t%e g'ades
5. n C'ite'ia put you' c'ite'ion.
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age -- o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
12/46
3. ou' ans
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
13/46
COU)T+FS
# you %ave t
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
14/46
)ORM/+)0
"etu'ns t%e inve'se o# t%e no'mal cumulative dist'i!ution. NORM.IN"(#r$%a%&'&y,*an,+andardd*-)
!a"#le -$
$o uali#y #o' a police academy: candidates must sco'e in t%e top ,+= on a gene'al a!ility test.
Assume t%at test sco'es a'e no'mally dist'i!uted ?Sou'ce Bluman ?*++9@ Elementa'y Statistics 4t%
edition. cG'a< ill.@
%ns&er$
,. Clic& on t%e Fo'mulas ta!: Function Di!'a'y g'oup: o'e Functions catego'y: Statistical
and clic& on t%e a''o< and sc'oll do
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
15/46
/. n t%e ean put *++.
. n t%e Standa'dLdev put *+.
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age -' o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
16/46
5. $%e ans
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
17/46
ou' ans
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
18/46
!a"#le$
ou
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
19/46
At cell C5: B and A a'e 'eplaced !y B5 and A5
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
20/46
hat+f %nalsis
By using
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
21/46
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age 2- o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
22/46
Fo' eac% scena'io: calculate t%e "" !ased on t%ese CFs: sta'ting
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
23/46
*. Complete as #ollo
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
24/46
3. Clic& on Summa'y: in t%e 'esults cells: type B4.
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age 24 o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
25/46
4. Clic& 0& and you
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
26/46
5oal See Goal See&s allo
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
27/46
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age 2( o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
28/46
. n By c%anging cell clic& on B. $%e By c%anging cell ;eld tells Excel a!out t%e cell t%at
%as t%e va'ia!le you
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
29/46
Data Table1ata $a!le is simila' to Scena'io anage' !ut you can only va'y one va'ia!le and see its eNect on
you' outcome. Fo' example: you a'e getting a mo'tgage loan and you
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
30/46
%ns&er$
,. ig%lig%t t%e columns t%at %ave t%e values you
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
31/46
/. # you' data is in column #o'm: in t%e Column input cell ;eld: clic& on t%e cell 'e#e'ence
#o' t%e ;'st su!stitution. n ou' example: clic& on cell A5.
. Clic& 0W.
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age 3- o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
32/46
5. Excel displays t%e 'esults in t%e second column. ou can 'educe t%e num!e' o# decimal
points to t
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
33/46
Sol1erExcel solve' is a ve'y use#ul #eatu'e. ou can use it to solve #o' optimiXation p'o!lems suc% as
maximiXing p'o;t: minimiXing cost: maximiXing expected 'etu'n on t%e po't#olio: minimiXing 'is&:
allocating 'esou'ces among diNe'ent uses: allocating adve'tising expenditu'es among diNe'ent
sou'ces to maximiXe 'evenue: uantities o# output t%e ;'m s%ould p'oduce to maximiXe p'o;tgiven its const'aints: and muc% mo'e. t solves linea' and non-linea' euations.
Add Excel Sovle' to you'
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
34/46
*. Clic& on Add-ns
/. Clic& on Go next to Excel Add-ins.
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age 34 o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
35/46
. C%ec& t%e Solve' Add-in !ox. ou may
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
36/46
!a"#le -$ 7inear Progra""ing$
A pe'sonal compute' manu#actu'e' p'oduces t
?Sou'ce Samuelson and a'&s ?*+,*@ anage'ial Economics. 4t% Edition. 7iley@
%ns&er$
$%e manu#actu'e' needs to dete'mine t%e num!e' o# standa'd (C ?S@ and t%e num!e' o#
economy (C ?E@ to maximiXe its p'o;t ?Z@. $%e o!Vective #unction is Z I 5++ S /++ E. $%e goal
is to maximiXe t%is #unction su!Vect to t%e manu#actu'e' const'aints: a'e
S [ *++8+ S + E [ *+:+++
5 S 5 E [ *:+++.
,. 0n t%e sp'eads%eet: put any num!e' #o' S and E ?say , and *@ and de;ne t%e p'o;t
#unction as 5++TS/++TE ?in cell B,*@.
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age 36 o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
37/46
*. 1e;ne you' const'aints.
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age 3( o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
38/46
/. Clic& on Solve': in t%e Set 0!Vective ;eld: clic& on t%e cell t%at %as t%e #o'mula #o' p'o;t:
cell B,*. n t%e $o ;eld: c%ec& ax .
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age 3* o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
39/46
. n t%e By C%anging 6a'ia!le Cells clic& on t%e cells t%at %as t%e num!e's #o' S and E:
cells B,+B,,.
5. n Su!Vect to t%e Const'aints clic& on Add !utton.
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age 3, o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
40/46
3. n cell 'e#e'ence: clic& on cell B,+ ?to c%oose S@: in t%e next ;eld: c%oose JI: in t%e
Const'aint ;eld put *++.
4. Clic& Add: and sta't you' second const'aint n cell 'e#e'ence: clic& on cell B,5: next ;eld
JI: const'aint ;eld type *+:+++.
8. Clic& Add: and sta't you' t%i'd const'aint n cell 'e#e'ence: clic& on cell B,3: next ;eld JI:
const'aint ;eld type *:+++.
9. Clic& 0W.
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age 4. o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
41/46
,+.2o< you %ave you' optimiXation p'o!lem set up: clic& on Solve
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age 4- o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
42/46
Solve' al'eady solved #o' S and E and put t%e ans
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
43/46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
44/46
/. Clic& on Solve
ou' ans
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
45/46
BUS598 Excel Functions andout('epa'ed !y Fatma A!del-"aou
)uly *+,*(age 4' o# 46
-
8/9/2019 BUS598 Excel Functions HandoutR2.docx
46/46
References
C'onan: )o%n ?*+,+@. ic'oso#t 0\ce Excel *+,+ Quic& Steps. *nd Edition. cG'a< ill.
Et%e'idge: 1enise ?*++4@. ic'oso#t 0\ce Excel *++4 1ata Analysis. 7iley (u!lis%ing.
F'ye: Cu'tis ?*+,+@. ic'oso#t Excel *+,+ Step !y Step. ic'oso#t ('ess.
F'ye: Cu'tis ?*+,+@. ic'oso#t Excel *+,+ (lain and Simple. ic'oso#t ('ess.
)elen: Bill ?*+,+@. ic'oso#t Excel *+,+ in 1ept%. ,st Edition. Que.