Loh- o

Chatng : Gouade an y satlu qraph, y chart uth to

y- Axes, add eno bav to qo pdot, Geete a

Com binad ion a charls.

.Scttea Goaph
Beloo the steps do inset a scatle plot iy Excel ;
t. Seleet dhe colamns dhat hawe th data' Cezcliding wlumn A)
9. ClecK dhe inet opton
3. Jo thu chart qoup dhe inet Seatteu chaut ieon
4. Click on the Scatles chat" optisn in th chats thats
show uf
5. The abou steps would insut a sctle pdot as shown
balow i the wohshet.

I, Selt a! thuus colwnns dhat have dh data

9. Clk on dhe iset ibbinu and &elet lots Jctles
Ophin from chats qooup
ug the chat
3,Clck anuohu
Bues chat type
eqht c¯cK and g t

4.to dthe change Senies chat type dialogs koN gelt dda
chang
ene Suond avy is. Then you iu +o
uill ave too anin
C
Caxon Bans:
Enor bau in chad s (uate can help you
eo and standad deiations at a glance. lhuy
Cn be shoo On al data point
puint si 0M
o dala markens in a data
dada maukens
Series sundond e o mount , a pucenkagt , o
Standeyd deu'otons. you Can set yous con vaue to
dis play the Exaut eron amounts you want.
. cick anywhu an dha chat.
a. cliek th chat glemut baton net to the chat, and
-they check thu Eshr Bas bo%. (elea dhe boz to sumole
bas)
3.To change the enor amBunt showo, edich thu auow eat
to Ehon Baus, and then pics an option.
pich pudifrud ban option lte sandarJ
peucundag standand Deuiation.
Pick set
to
MOu
optorns you
and dhn ndu Veatica 80n Ba on foxizootcd
Eno Ban, choose dhe
dhe option 8j ou want.This
a o uohue you change shu dtndion, end styla
thu Cele Cestom ho
b.
 Combhaton Chats
Combinadions chauts in y prsert and
Compu two data s that a Jated to ooch
other (iy a Singdi chant).

uohen you checte a suqulan chat in Excd, ualls hau

Cndy One X- anis and-
and nis. ed uith om binatin chats,
cCuy have two Y--ais, ohich alou03 youL
you to have teo
you
elil<en type o data. pointb in the Bame chat.
any oaf the prolit margin bais. This uil sdact

Q. Raht cléck and selat fonat Data Sies.

3.I, thu fomat Data selies dialoqu bo, Seled Seondey

quoup)
Data cçn a Suwnday Azi
This ill plot
Mngn
uold be abtto see a verticd ais on dhd
sethd a ahu chat
Ito Right-clieh and Selet change Senies Chat Type
5, Io ha chang chat Tyee dialau boz, geld in ?th
Mankes op'on.
6. That's you would have tha chat,fomat the
you
Uay you want.
 0.

Funttons
Compating Bum, Avesage Count , Maz and Min
Computing lahsed Avesage Tsugnomtwie fant'ns
Cxponyt'al fandions, Using the CONVERT funtÉon to
Convet nlta.

Count , Maz and Mi

a cel ange a the nenbes as yaes to Pend thu
Sum al! thwe numbrs.
Go tha home sPhh on d The edting gooup we Can
-fad out same Option Ou an min Opton to calal ate the
mlnimun numben o the Option to catuJt the
lanuqe nunber
anothes way to Compate dhu Bun,
aveage, Count ma & min ah attenate optien wing
fo sunuas is shon belo.
= SUM (A9: A1) H uil fead dhu gum the no Ag to
A7.

value
AvERAGE (A2:01)
A2; A7

= COUNT (A2:41) uill dotal ount no's

= MAX ((A9:7) ind thu matimum no. în

Yange
- MIN (A2 01) il ui find ah minimum no, in
!. T3 ind euezhted avexaqe ig the encel u nud to we
Sum PRoDCTobemla n Cxcel
Q. Thi osmula - SUMPRODU CT (valu range, wfads ran)
/su (wight ranse) Thii foamaloe uil qau ihs valin

Thanomede Fnctions;
Theu ae two tranomtuy thal i angles
and Pi member to
foured in hadias
rpometut
athe than
funtns angle
duqtus.To coovet
must

degues to adian Madians o duqxu s Ercel puuid

sinbuil: omula ihe.
=DEGREESC vaue in ra dian )
= RADISNS ( valuis oqe

you
wnt outpt dhe Jin at Cos

i) type uy thu fonta es shoun below

= SIN (( RADIANS (A)) = C0s (RO DIANS (A2))
>T N (RADTANs (A2) )

erponerdial Funil'on:
Enponental funtlon that u calulate the eaponnt
haise to the powes o ary poouid1d number the foeue
shown beloo

= Exp( Do)
Convent Fund ion:
liung the Convet funtion n Excel aliow to

Suslen to nothes
mtnti frorn onc system
Convet tuits co miasunenti
lthe deng th volu helght
1 Selat the celu ohee yo wnt to cut p t dhe conveted vau

= CoNvERT numbe ptom nit to unit)

numbe dhe valu you want to onvent

The nit meswLmt you want to cOmet
from.
To enitt, thh wt oh meesument qou want to DOvet t
 Conditional Fundion i
Boolecen feunatns In feention
Cuatäng Quatrati'c £qudt 30 lves Tabe vLoosUP funtin
ANDA nd OR -fnltm

kogial pessfo Baolean funtion

Lin Excel to compaie
Logicd operatoas ae Jometos called Boo luan opexatou
becaese
be either TRUE Ot FALSE.

Sia bgal Opeiatas au avatlable cin Excet

does and
The folloin tabu explaiss what each o thn
Etlusrate the uith fo mula
theoy

Coaditton Operatu Fomula Descaipton

The -fotmela sutuns TRUE
Equa to
to the value uy cel 8I; FALSE
atheuise
The fomla utesns TRUE
NOÍ equd <)
o the vale cell BI:FALSE
odherucst.
1he foumula utuns TRUE Q
Goeate valee n cel ) qecler than
than in cel Bi otheicise
 hhe fosern wa hun True 4
less than
than cel B1: fLSE othexuiz
Thu fosmua utus TRUA

than ot Al-B) qeater than ot to

equal to th Yalus in cell BI FALSe

othenuise.
The foSUn la stur TRU
less than
less than equad to thu.
to value u cel B1 : FL SE.
other nise.

Using dhu Equal to" opeatox tb texait value

oe cin êxcel us case
Equal to logfcul opexal ox
insena tPe meaning aha Case diffeunce
Comp ax'ng tent vales.
fost eram pde if all 1 conteéns dhe wosd OLanqe" and cell
the doumula =AlB1 ultu
BI Contn
40e want to Compde dert vales
dhe &xAT
Qecount Hheis Case
dilens.you shod
furetion sinpi
Exaut 1f Puntion
eom paaon opeaatoas caguill
Excel i unition ohee the and
cto constut a dogiul dest
the 3f foxumula
Lohether thu
eppopcate nesull dupnding
dest ev aleal03 to TRUE o FALSE
 =1f Condiíon
{ox enamp:
Thiy

geat on than ai cqual

otheautse.

Cuding a Quadxatie Euab'on solves as Exd.

To cuate quadaatic equation 8olver is ercd you
Can wse thu qusdate fostmala ., Asuming dhu valuus oh a,
nd C in
n cetls , BÊ ond CI,guspativ dy. you
dhe to cauuwde thu solation.
you

81 +SgRT (8i2-4 Alci)(2) (ox th solition, tle

ah &olutiar

Tabte VLOOKUP
Thu V
LOO5UP feunillon in Excel alous
goa seach fo
Yae i a C0hsponding value
colum. The th VLOOKDP functin
follous
= VLOOKUp (Locfup- value tub-ranqe, olum- in du, nange
Loosop)

AND,OR and xoR tetioDS in Exel:

Excd pxo uidu butt in funitna don logial Oçoati:
AND fundion xammple i
81<10) 1hy foumula chahe thu yalue in cell
stens True looth onditions au tru
othaeise OR quntlon Exarrpe:
= OR

Tha fonala chuhs i thu value in cal 1 is cithe

tiu. otheuntse utens PAL SE.
XOR 7nutin example :The xoR qution outurns tne i the
nambe odd therese it tats
false Bx ample x0R (S>3, 0>20) Ju'l evcaluole to falit.
 Lob-ot
foucast
Trand Line , SLope and Tnlercept , inleupolat ion and
the LTNEST funtion ugrasslon polynomialtoolft
pach,
-fundions , Rahal plat , sldpe and Tanqut analyis
Trend iei A thendline s a din that up xelenls the qunual
dtutton al a sesies al dato poin chart T4 hales
visuale the ole al!
h Olea o. trend in
al patlerny on ahe data, To add a
tend line in Excel nud a chat eaith data puinds
Example
1. Cecte a plot chat in Excel suith youa &afa
Scatl e plot
Rizh clcs any deta pont in thu chat and sle t
9.
any
Add Trend le'
3. choose the desiud dype of thndlne, such as linuan, ex ponnti al
Or podynomial
nire the
4o Cuto ai? dhu display 4uatim
trndtne option, in cu dinq ah
and R- squared value ,i dusirud.
close" to appy the trendLine to the chat

slope and Tntucpt:

The slope and ntecep1 cofents liniar equali
(y=m b) dhat deliaes dha sedatiomghip baluuy thi undupndnt
Nanâablel) and dapendent
model. Excel you can Calaulate ahu slope nd intercpt
ahe LING8T -funcn
ng Eamsple
 Let's sas you have
valw un chs. BI:BIO
tONLRonding
To caleulate dhe slop. , use thu foenula. :
= TNDEX(LTNEST(BI:BIO, :Alo),,)
Te Caluw cte the intercept, the fosenula;
= INDEx (LINEST(BI;B10 l:910), ,2)

Toteupolation and fosuceest ?

o
known dlata pointo. Fotu castiag on the othes hand, ánvolve
puding valu autsideetslde the ranqe bnown a doda point, Th
Exed, you can cuse the INIERPOLATE funttn fo ntepod atir)
and thu foRECPST function fon fooucat
Eromplei
let's say you lave o Broon x-valus in cls Al:Alo and
.valus in celes B1;B10 To intespol ale o fo ecast

Vale
fon intepolatior, = INTERPOLAr[ ( Snoon y's, knoun-X5,x-vabu)
for foucesting , = FORECST (X-Value, 5no)o - y's , bnown -X's)

Thu LIN EST Punt'n

sto cal
Thu NEST funchon in Exced is ud to aul ale vaigu
cedulale

a diea olaqrassion model. It etns an

valees thal indudus dhe glope, anteacept mltiple carrelato
efRcent, stand asd @nr0 and moe.

= LîNESr (Bl;I0, 1:9lo, TRUE, TRUE)

Malt:inas qraston:
Maltiuaa regradsion dl krtersion o Jrau regtossian that
involves mali'yals Podupen dunt vaiablu . In Exe, you Con us the dat
Analysis Too) Pack to peufon rnuliia regaas'or.
ex ample
Go to thu "Dala" tas in Exed and elch on" Doata fralys's" ig thu
group
Selet eqrassion" frorm dh list ond Cick'O5'
&peifs dha inpal and ol put ronges , chech dhe 'Lobels box i appirade
and selt dh duiud opti'¡n3.
-clen os to pufon dh malt'linues regrssion onalssis.
PoLynomial fil funtion:
pou noniad fit funtions allbus you to fit a pono mia) eq
to o st o dato pointb . In Excl, you can we the built in poly
nomia trende optlon or dhe LInNEST funetÉon uaith an array tomul

Destduala plot:
A uduala plot uiu wed
wed to visuatiz dho diffeence btween th. obseu
and Pse dited vaus án a reqrassion onalysis, I, Ext eld, you con
Geet susiduads pdo t by follousng these bteps:
have the
fosum ing you hawe pea foned a reqas sior analysis and hau
obses ved ad pudícded valus counss andB, ropatialy
Calulae the uzidual by glebshaliny dhe poadited values from the
obsesred yaues. place dhu usiduals in column C
Cuate satleu plot chart uith the residu alsC Column C) On dhe.
y -Qi and th observed valws Ccolumn ) on the x-ais,
fnalyz dhh suatten plot to obsenve dhe patten al residuals ancd
Yduntif any potenftal isus, duch teso sce dasteuity or
gut liess.
Slope And Tangent :
In Excel, qou can caleelale dhe lope o
poant ohich oupusents h tonqent dina to th mve t fhat

pount.
Assume yoa
yoa uave a set ol
o *xvaees in cells Al:Alo and Correponding
y-valus in cels BI:BIO
Use. thu Slope funetion to calelate dhe lope betwen two poi
Ex am pe : SLOPE BIB2, 91)

Analysis Toolfak 9
The Aralysis Tool Pak iy an Excel add.in dhat pxouides addito
na
analysis toods and fundion. at alas vaxiaus btatstical
dato

and anadyttcal fate, lncla ding sagrassfon Tbanalysis, dusuragtia

enable and
Stat's tics corolecetlom,t-tests and moe
dhe analysis 7ool Paks.
"Go o the Fle' tab in xcel and select
Options'
Tn t h
Exce Optons' dialo box, choose "A dd -Ins
. In th Manage drapdoun, sebit Sxcel Add- ins" and c°cs Go"
chek the " Anaysts Tool Pak" bor and clies 'Ok!

let's
the Analyats ToolPah to penfosn Bom la
onalydis
Asuming you uave
coM sponding y B) BIO
Go to the 4ab i X and click On Data
uy dhe
Data
Analyats'
tmaysis graufrorn
&lt '-Rquesston hist and cl'h'
thh ús pt and oupitt rong selett thu desiu
opton
Exce ceill pea fo sum the tugaassion analysis and poid yoce
uith in cludin slope, indeact
 Lab 05
Tteatiw Solurtions Excel
ing Goal Beeh is Excel, ng ihe solue To -find
Poots ,finding mutiple toots
bos Optmizaliog iing
stng the bolves
Mánimization -fnalysis, Non -Linear Regxassion Anaysis.

Ustng Goal Seeh ag Excel:

Remen beu that Goal seat may Goal Suek i a pouenfal tool
in Minosofd Excel dhat allos you to And dhu inpt vau
uquired to achieue a Spesfe qoal steselt u a founula,
TI is patilasy wefl ohun ou hnow the desiad outpat Lut
nud to dduminu the coonesponding foput yale.
Example:
Sppose you ahe plannng ptojut and wanl to datemin
uow mabu uit youyou neud
need to ge to each ceatai
taugt. you lave -fo llouina data

Jelling icepei wnit , $so

Vaiab cost pes enit : $30
-fined Costs
Desie d Pseof°t $ 1o,00

Btep 1:
Entes the fom da
B5 * BI
3tep3:
cel B4 BS +B2
83 +84
Cel B9 = B6- B8 - B4
JAep 3 : ung qoal Seat
Step 4 i eiew th Jeswlt
 Osing ihe olves To find Roots:
Sotven ç an Sxtel add -in that coo be used to -fndJ
Soludionu for Conmplez mathmat al problens, iocluding tnding
T1 Ccu handle both inuas nd non lintas
¬quations. To Lse Jolea to frd seoots in Excd.follow thw
8ieps.

Step 1: Enable the Bolv es tdd- In

set up you ofo Spuad shet.
Add a cell the Objutie.
2 Acces the &olwen -Add- in
&t up the Sowe Pasametes
6. up Ths
1u dolver option
Confraue dolves O;ton &
kun olve
8. viu Rus ults.

" Finding Muttiple Hoots ao a funct'rn un Excel: saqiru

-finding multipu soos aal a -functton in Excel
method beccuse nast equcetlon8 do not have
itehatiue
using olons that Can be diuety caldated. One po putas
ex ut
uthod to -find dhe Newton Raphsoo method' or
Nton's mthod'. Excel pouides built-i feunctio Caled
wsed ato perfosen dhis der ali
Goal es ohich
psoess.

dt up you sp ruac sht

Ad a cell for the objetiue
-Add
3 Acees theolvea -Add. ins
Pau arntes.
8t up th doa
dohe Pau
5. (on fiquu Bolvex Options
G. Run dolver
Feuiuw Resuk

Cotimizaton stng 1h solvea : in Exel allouu you to

lha solweA too d) xcel tooJ allous
peafon Optimtzaton by fnding th but olion fo
paxtialas pooblem, ubjeut to Certais conghainds. Tt cas
be cused fos Vaxity o Op timizaion poblend, buch
maimi? nentmizia a pefe fanct'on aloceting
cel esindly, on Anding ahe optinal valu oß
vaables giaey 'Con detion
Uatng &xcedl's Jolves foa op ti¡mizalionPmvohes fndeng the
miinike
lopt valey that maxirnie

Coostrains,
phie Satsfying cutain

1. yous Spad shet

Set up fosmula
3. Acess he Bolvea -Add.
the solyea fararm des
S. Con ehue solves Optors
6. Run olyes
Revleew Resle
Mintmizaton analyais o Exel
i in volves m vaeu ah a Sperf
finding the minimun
Ceatai constrarpts. Thio
objutiue Rntion whide sidagpooblem
Cons
di's ays ed eatie
dinilas to the opttniation
but nOw thu qo to minike uhe obyeutiu fuation instad
olves tooJ n xcel n also be
The
o marlonizing it
 cseel fo minimiZation analysis.
Minmioation analy sts iy beed
Excel inulves -fndling aho Smyat vales
that nininize a Sp eai fic objetie ohile aduing
adheninq to celain
Contain b.

Sa up yowr Spxu adshut

St up fosmulas
3. tceess the olver Add-in
St up the solv es Pauamaley
S.
Con-fiqure cdolver Options
6. Ruy &olves
7. Peviuo Rests.

Non- Lintau teqesston anayais:

Pufosminq lineax eeq rassion anaysts lo Exce
non

Rovolves Atting linaan equetion to you data

nhon-ineon
and inding dhe lbest- f pasamtes that minimize thi drffenute
betoee the mo del and thi atual dota Excel poouides tool
ton per fornîng dhes type of analysts thuougb 4s buelt.,
funtions and the solven! add-in
Example
ttal Non-ineas Ree
Exponyt
OrqantKe Datc
auale a dcatlea plot
pdd Tundline
pay tquud 'on andR- Squancd
Use solvea -fo Noo 'an -eqassio
 Lab-os06
Mabrix Operal ions Uaitng bxcd:
Adding Two metnies, muliplying a mauin by a
Lcetar, mulliplying Teuo matu'es, Tnan spo 3ing
Tavating Mat'xmatnir and dolung dystem cal ntaa

Adding Two matices:

Adding two matxices in excd inuolves checeting tuo
eperate matHí Ces md dhe peufoming lemnt-atee additon
Example
Mat ex Matix B

ddition 6

|to 12
Opy Jpuadahet.
J tnie the vclues ay Matuix 9 in cel Al: B2 and the valuw
matix
3. in ao thes celeClet's ay Gi) qou' dd calelate dhe
Jun o} oepondiny element trom matix A and Matx
, ente th tomula nte.thi
will qu yow the um aymarix &B

a MatK by Beala:
Mutfalying exced Bpad shet
3. En ten the valees o mai ix A in clls Al:B2 you Spxuad
sht uil loob ihe
3. In anothe cell Cel's ey D ), enten the sadas valu tou
wn

nothen cel! (.tat's F) yo

mulipicution oh dhi matiy by the dalaa, Tn cel
J , ntex dhi foun wa. = A1*$D$J and pre En tea
Thu foamta malfiplis he top- Lat etamns'ol the by
the Scalar

Mat iy Mullipleoton
Bcal as Pesult matix

Maltiplying Tawo madices:

To matix, the MMULT
-funetion Thiu funtion uill outuy dhu matx podut ot
two as Th qenea) dyoto ah -fun tionfom ula
as show blow:
MMULI

wheu
ahe mat six euucy tomultipdy
muliply
Matsix B Pesult matix
3
- lG
3
Ineansposing a Matix:
TRANSPosEC) tuntion
etuns a yeutical cells as

und ice Ven. TRANS POsÉ mwt be en lucd au an

-fosnula aucuy fon ulo that pertons mutie
Cclcelation andthun
HLtuns stngle mulliple usults

A Transpose o mad ix
2 2 6
6

Trvesting
ie
a Matix :
thu mati A Exce shat
2. -fad th lovese matnix by tlhleqhting the
cella whs you want to place the sesleng matx A
3. Ooce
you awe highiighd th seulting matix nd hil,
tigh leyhtud ner the fonule
=MINV ERSE (B9:bu)
etersd pres dhe Ctd Fay and the
Biilt huy nutanwusly thun pess th Ente hey
Toyerse malnix
-1-q23 0.385
1.161 - 0. (5

3 0.308
 (2 So
ada 4 &NTER. the
nset
oh matix
B nsa MINvÆRJEanut
1hh
have
we
both matseix
+
mllipy
o SHIFT
al
dysten
sleps. ivese Nest,
produt
dole
touow CTRL+
the
-9 1 thieodlsuing
dh nst dheG9
erampe cos
etun prusing
6 G6:
S
we
solve utn
Net,
for exists, the the
Aange
how equctions
Thes
&rample To Exeuteto by to to
unction
:D8 -fintshe
fnishe
bulofuton
Exce ) B
in col =Å B6 ffut
lt
linear x Excel MINVERSE
invElse tnge MULT on.
cquetm tuncti
MMULT
tainn
M
o CthuGb uatíons thu
the th
System
linear
to Selet and B
Use Use
"ilA eq
1.
 Saluig thes Luoo to sole a sqslem o
Tha &xample houu ou low
linean equationa in Excel. fon example have thu follouing
System o linear equcctions
S

{2

6x t7y t4 = S0 6 So

" ) (ahe inverse ol ) exists, we cas mlipdy both ada by

to obtain X = K' B To solve thi dysten co inear
equatons cën Excel Exeawte
Execute the
the odlaung sleps

1. Use dhe MINVERSE nction to deteun dh ivese matix o A

sclet the ena. B6:D8 Nezt, nst the MINVERg funcki
hown belo
Use dhe
fneshe by pausing CTRL+ SHIPT 4 ENTER.
MMOLT fenetfon cto xutun dh psodel al matix A!
and B. fst Jeleit the G6: G9 Nesct, anseut the
MMULT tunctio.