Spreadsheet Practice Exercises-2016F

1 | P a g e

15CSE100: COMPUTATIONAL THINKING FOR PROBLEM SOLVING

B.Tech I Yea! "01#

E$ce% &ac'(ce a)* 'a+e h,-e e$ec(e

CTPS 201 Spreadsheet Practice Exercises –Lab Session #1 Ettimadai Camps

Department of CSE,

2 | P a g e

T,&(c E$ce% Pac'(ce E$ec(e La La Se(,) 1 Simp!e arithmetic Extending an operation b c!ic$ing and dragging &bso!te and 'e!ati(e referencing Simp!e statistica! fnctions Decision ma$ing sing *+, S-*+,C./T*+ etc, Ta$e home estions – Lab Session #1 E$ce% Pac'(ce E$ec(e La Se(,) " Logica! operations &/D operation .', 3.',/&/D and /.T Logic 4ates Light S5itch Sstem Text fnctions Loo$ p fnctions 7isa!i8ation sing charts Page referencing Ta$e home estions – Lab Se Session #2


Pa/e N,

" % ) 10 12 ,1

2" 2" 2% 2) 26 "0 "1 "9 %0 26,"%,"6,%1

Department of CSE,

" | P a g e

S(-&%e a('h-e'(c P,%e- 1: &dd t5o nmbers

S'e&: 1 .pen .pen a spre spread ad shee sheett and and tpe tpe in the the !abe !abe!s !s ca!! ca!!ed ed n nm mber ber 1 and and nmber 2 in the ce!!s &1 and :1 and add in the ce!! C1 This is ;st for s to identif the (a!es and the are not in(o!(ed in acta! exection 2 *n &2 &2 and :2, :2, tpe in in the nmber nmberss to be added added " *n C2, C2, tpe tpe in 2A"3B" and press enter to disp!a the res!t *n exce! ca!c!ations are prefixed 5ith < sign fo!!o5ed b the operation on ce!!s % The ce!! nmber nmber represen represents ts the nmbers nmbers =+or =+or eg &2 &2 and :2 are sed 5hich denote " and ) respecti(e! 5ith a p!s sign in bet5een as in the case of norma! addition ) To add mo more re than than t5o n nmb mber ers, s, add add as man man co! co!mn mnss =nm =nmbe ber1 r1,, nmber2, nmber" etc, and extend the form!a as <&2>:2>C2>? <&2>:2>C2>?


Department of CSE,

% | P a g e

E$'e)*()/ a) ,&ea'(,) 4 c%(c+()/ a)* *a//()/ P,%e- ": &dd ) sets of t5o nmbers

S'e&:

1 Tpe in as man man nmb nmber er of sets sets of nmb nmber erss o 5ant 5ant nde nderr the co!mns nmber1 and nmber2 and then c!ic$ on the ce!! C2 and mo(e or crsor to the bottom right corner of the ce!! 2 Th Thee crs crsor or chan change gess to > sign sign /o5 /o5 $eep $eepin ing g the the !eft !eft mose mose b btt tton on do5n drag the crsor ti!! the !ast set of nmbers and o can see that the ce!!s are fi!!ed 5ith the res!ts


Department of CSE,

) | P a g e

A,%'e a)* Re%a'(6e e7ee)c()/ P,%e- 8: Consider the prob!em of preparing a stationar order for the month of -arch The item description, @antit and cost per item are a(ai!ab!e The tota! cost per item is to be ca!c!ated and the fina! cost per item in(o!(es a sa!es tax of 2A o(er the tota! cost The gross tota! and the net tota! are to be disp!aed

S'e&:

1 Create Create an exce! exce! sheet 5ith 5ith the a(ai!ab a(ai!ab!e !e data as sho5n sho5n be!o5B be!o5B


Department of CSE,

9 | P a g e

2 &dd the co!mns tota! cost per item, 7& 7&T and Cost > 7& 7&T " +i!! in the the 7 7&T (a!e (a!e =2A in one of the the ce!!s sa 41 so as to not to affect the other ca!c!ations % -!tip! -!tip! @anti @antitco tcost st per item item to obtain obtain the tota! tota! cost cost ) -!tip -!tip! ! the 7 7&T (a!e (a!e 5ith 5ith the tota! tota! cost of each each item to obtain obtain 7&T co!mn a .bser( .bser(ee 5hat happen happenss 5hen o drag drag the ce!!s ce!!s after after o fi!! fi!! in the form!a for the first item b The first item gets fi!!ed correct! bt the other other items sho5 8ero as sho5n be!o5B

c This is becase the form rm !a changes re!ati(e! for the sbse@ent ce!!s as D%42,D)4" etc here as the (a!e is present on! in one ce!! 41 To pre(ent this atomatic changing CTPS 201 Spreadsheet Practice Exercises –Lab Session #1 Ettimadai Camps

Department of CSE,

6 | P a g e

of ce!! (a!es =a!so ca!!ed re!ati(e referencing 5hich exce! does b defa!t, anchor anchor the ce!! 41 as 4F1 4F1 The F infront infront of a ro5 or or co!mn pre(ents atomatic pdation of the ro5Gco!mn (a!e 5hen dragged Here 4 is stationar b defa!t bt ro5s change as 1,2,", etc so 5e anchor the ro5 as 4F1 and then drag the form!a 5hich pdates the ce!!s proper! as sho5n be!o5B

9 Ca!c!ate Ca!c!ate Cost > 7&T 6 Disp Disp!a !a  the the tota tota!! cost cost


Department of CSE,

 | P a g e

Ta+e h,-e E$ec(e

1 Create Create an exce! exce! sheet to print print the m!tip! m!tip!ica icatio tion n tab!es tab!es from 1 to ) 5ith each tab!e ending at its10 th m!tip!ication !imit =ie, 1x10<10 ) 310<)0 2 Io are gi(en the order detai!s detai!s of a compan in the be!o5 tab!e tab!e

a b c d

Ca!c!ate Ca!c!ate the the re(ene re(ene and tax tax on the re(ene re(ene for for each prod prodct ct Ca!c!ate the net come come of each prodct prodct Ca!c!ate Ca!c!ate the tota! tota! re(ene re(ene of of a!! a!! prodct prodctss Ca!c!ate Ca!c!ate the the tota! tota! net net income income of of a!! prodcts prodcts


Department of CSE,

J | P a g e

S(-&%e 'a'('(ca% 7)c'(,) P,%e- 9: Let s consider the prob!em of finding the tota! and a(erage of " sb;ect mar$s for fi(e stdents in a c!ass in the board exam The data is entered into the spread sheet as sho5n be!o5B

S'e&:

1 To ca!c ca!c!a !ate te sm, tpe tpe

Department of CSE,

10 | P a g e

P,%e- 5: Ca!c!ate the -aximm mar$, minimm mar$, mean, median, standard de(iation and (ariance for each sb;ect

S'e&:

1 Tpe Tpe the !abe!s !abe!s -aximm, -aximm, -inimm -inimm etc in the the re@ired re@ired co!mns co!mns as sho5n abo(e 2 T Tpe pe in the re@ired re@ired form! form!ae ae or choose choose the form!ae form!ae b going going to the men *nsert +nction and pic$ from the categor as re@ired and fi!! in each (a!e for the first sb;ect =-aths 


Department of CSE,

11 | P a g e

" +or +or ph phs sic icss and and chem chemis istr tr  ;st ;st c!ic c!ic$ $ and and drag drag the the form form! !aa ce!! ce!!ss of maths hori8onta!! to ca!c!ate each form!a =-ax,min etc


Department of CSE,

12 | P a g e

ec((, ec( (,) ) -a+()/ ()/  ()/ IF I F, SUMIF;COUNTIF SUMIF;COUNTIF e'c.; e'c .; P,%e- <: *n this prob!em o are gi(en the name, gender, attendance, assignment, midterm and fina! grades of fi(e stdents +ind the tota! of the assessment mar$s Stdents 5ho pass need to ha(e a tota! score greater than or e@a! to )0 Disp!a the 5ord KPassK or K+ai!K nder a co!mn ca!!ed Description

S'e&:

1 Create Create a co!mn co!mn ca!!ed ca!!ed descripti description on ". *n the first stdents description ce!! tpe the *+ fnction sing the tota! ce!!s no to chec$ pass or fai! 8. +or eg, in the abo(e fig, in 7ira$s description ce!!, tpe

Department of CSE,

1" | P a g e

P,%e- =: Extend the abo(e passGfai! comptation prob!em to inc!de attendance a!so & stdent passes if he has an attendance greater than  e!se he fai!s e(en if he has a tota! greater than than )0 S,%'(,): -odif the form!a in the pre(ios prob!em as <*+=&/D=C12M,412M<)0,KpassK,Kfai!K

P,%e- 10: *n the pre(io pre(ios s prob!e prob!em, m, 5itho 5ithott consid consideri ering ng attend attendanc ancee as crite criteria ria for for passing, ca!c!ate the grades of the stdents as per the grade r!e tab!e gi(en be!o5B Ma+ Ra)/e

Ga* e & :

ScoreM
Department of CSE,

1% | P a g e

S'e&:

1 &dd a co!m co!mn n ca!!e ca!!ed d grad gradee 2 This prob!em prob!em needs needs to be so!(ed so!(ed sing if fncti fnction on 5ithin 5ithin if fnction fnction as fo!!o5sB +or examp!e if 7ira$s tota! ce!! is 422 then the fnction is form!ated as <*+=422M
P,%e- 11: Io are gi(en a fi!e containing the name and age of peop!e Io ha(e to cont the nmber of peop!e in each categor as minor=O1, ma;or=12), midd!e aged=29%0, abo(e midd!e age =%090 and senior citi8en =91100 sing a sing!e fnction for each scenario and print the cont pertaining to the conditions


Department of CSE,

1) | P a g e

S'e&:

1 There are are m!tip!e m!tip!e form form!ae !ae for for so!(ing so!(ing this prob! prob!em em 2 The most easiest easiest one is to se se contif contif for sing!e sing!e criterion criterion and and contifs contifs for m!tip!e criterion if o ha(e -icrosoft exce! 2006 or !ater " +or cont conting ing minor minor here here 5e 5e se the the form! form!aa
P,%e- 1": Cons Consid ider er a tab! tab!ee cont contai aini ning ng the the cons consig ignm nmen ents ts of diff differ eren entt fri frits ts from from different spp!iers as sho5n in the figre be!o5 Io ha(e the frit names in co!mn &, spp!iers names in co!mn :, and @antit in co!mn C +ind ot ho5 man @antities of each frit is recei(ed CTPS 201 Spreadsheet Practice Exercises –Lab Session #1 Ettimadai Camps

Department of CSE,

19 | P a g e

S,%'(,):

S'e&:

1 To find the nmber of app!es the form!a is ,* ,7 ca'(,) >h(%e >h(%e *a//()/ *a//()/ 'he 7,-%a 7,-%a 7, ,'he 7(': 7(': a h hen en o o  dr drag ag th thee fo form rm! !aa for ot othe herr fr fri its ts ai aim min ing g to ; ;st st chan ch ange ge th thee na nam me of th thee fr fri itt be ca cat tio ios s to s see ab abso so! !te te referencing as the tab!e reference 5i!! change as o drag =de to the defa!t re!ati(e referencing of exce! b *nstead se the form!a as
Department of CSE,

16 | P a g e

2SUMIFS?C":C=; A":A=; @a&&%e@; B":B=; @Pe'e@ N,'e: S-*+S has the formatB S-*+S=smRrange, S-*+S=smRrange, criteriaRrange1, criteriaRrange1, criteria1,criteriaRrange2, criteria1,criteriaRrange2, criteria2,


Department of CSE,

1 | P a g e


1 The next page sho5s the order detai!s of prodcts 5ith nit price, @antit and and disco discon nt t Ca!c Ca!c! !at atee and and disp disp!a !a  the the fo!! fo!!o5 o5in ing g info inform rmat atio ion n sin sing g appropriate exce! fnctionsB 1 2 " % )

+ind +ind the the cheap cheapest est prodc prodctt +ind +ind the the cost! cost!ies iestt prod prodct ct Ca!c!ate Ca!c!ate the tota! tota! @antit @antit of the prodc prodctt 5ith .rder .rder *D<10290 *D<10290 Cont the prodc prodcts ts 5ith 5ith .rder .rder *D<102)) *D<102)) Cont the the prodcts prodcts 5ith .rder .rder *D<102 *D<102)) )) and their their @antities @antities are greater than "0 and !ess than 60 #. Cont the prodcts 5ith their names beginning 5ith QchNCont the prodcts 5ith their their nit prices M %0 and their @antities @antities M"0 6 Ca!c!ate Ca!c!ate the a(erag a(eragee of nit prices prices of prodcts prodcts 5ith 5ith .rder *D *D <102))


Department of CSE,

O*e e'a(% O*e ,a*gec' 1J |PP I

U)(' P(ce

a)'(' (c,)' 4

102%

eso Cabra!es

1%

12

0

102% 10 2% 

Sing Singap apor orea ean n Ho$ Ho$$i $ien en +rie +ried d -ee

J

10

0

102%

-o88are!!a di 4io(anni

"%

)

0

102%J

Tof

19

J

0

102%J

-an;imp Dried &pp!es

%2%

%0

0

102) 10 2)0 0

Uac$ Uac$Vs Vs /e5 /e5 En Eng! g!an and d C!am C!am Cho5der

66

10

0

102)0

-an;imp Dried &pp!es

%2%

")

01)

102) 10 2)0 0

Loisi Loi sian anaa +ie +ier r Hot Hot Pep Peppe perr Sace

19

1)

01)

102)1

4stafVs WnXc$ebrYd

19

9

00)

102)1

'a(io!i &nge!o

1)9

1)

00)

102) 10 2)1 1

Loisi Loi sian anaa +ie +ier r Hot Hot Pep Peppe perr Sace

19

20

0

102)2

Sir 'odneVs -arma!ade

9%

%0

00)

102)2

4eitost

2

2)

00)

102)2

Camembert Pierrot

262

%0

0

102)"

4orgon8o!a Te!ino

10

20

0

102)"

Chartrese (erte

1%%

%2

0

102)"

-axi!a$

19

%0

0

102)%

4aranZ +antZstica

"9

1)

01)

102)%

P[t\ chinois

1J2

21

01)

102)%

Long!ife Tof



21

0

102))

Chang

1)2

20

0

102))

Pa(!o(a

1"J

")

0

102))

*n!agd Si!!

1)2

2)

0

102))

'ac!ette Corda(a!t

%%

"0

0

102)9

Perth Pasties

292

1)

0

102)9

.rigina! +ran$frter gr]ne So^e 10 10%

12

0

102)6

Schoggi Scho$o!ade

")1

2)

0

102)6

Chartrese (erte

1%%

9

0

102)6

.rigina! +ran$frter gr]ne So^e 10 10%

1)

0

CTPS 201 Spreadsheet Practice Exercises –Lab Session #1

102) Ettimadai ChangCamps

1)2

)0

02

102)

Chef &ntonVs 4mbo -ix

16

9)

02

102)

-ascarpone +abio!i

2)9

9

02

Department of CSE,

20 | P a g e

2 Constrct the be!o5 tab!e in exce! and 5rite appropriate fnctionsGform!ae fnctionsGform!ae for the @estions gi(en be!o5B Na-e E)/%( h

C,-&'e 

7ira$

)9

6

Ma'h Pa 8 ec'  %)

Soa

%)

6

6

7ibo!

96

6

10

Pa a' %ea' 1 ec'

Pa " ec' 

a

*n the co!mn nder Pass " sb;ects, 5rite a form!a to dip!a T'E or +&LSE The res!t is T'E if the stdent pass a!! the " sb;ects b *n the co!mn nder Pass at !east 1 sb;ect, 5rite a form!a to disp!a T'E or +&LSE The res!t is T'E if the stdent passed at !east 1 sb;ect c *n the co!mn nder Pass 2 sb;ects, 5rite a form!a to disp!a T'E or +&LSE The res!t is T'E if the stdent passes 2 sb;ects

" sing the fo!!o5ing data set in exce! so!(e the be!o5 @estions B 60

)9

)"

%"

%)

%)

)"

"%

)9

6

)%

96

9

)9

a

rite a form!a to ca!c!ate the a(erage of the data set

b

rite rite a form!a to ca!c!ate the (ariance of the data set

c

rite a form!a to ca!c!ate the standard de(iation of the data set

% Io manage in(entor and orders of a compan Io can not honor orders if o do not ha(e the f!! amont re@ested So o create an *+ fnction that 5i!! chec$ that o ha(e the @antit in or stoc$ *f or stoc$ is !o5er, o can not de!i(er the amont o ha(e +or the second part of the CTPS 201 Spreadsheet Practice Exercises –Lab Session #1 Ettimadai Camps

Department of CSE,

21 | P a g e

exercise, o initiate an order 5ith or spp!iers if o co!d not f!fi!! the entire order Io can 5rite the form!a in different 5as for an e@i(a!ent res!t & samp!e 5or$ sheet is gi(en for or reference

) Io ha(e a stoc$ portfo!io *n co!mn C o ha(e the prchase price and co!mn D the !ast price rite the res!t in co!mn E if o 5in mone or in co!mn + if o are !osing The res!t sho!d a!so ta$e into accont the nmber of shares o o5nThen o ma$e the sm of co!mns E and + and then in ce!! EJ, o ma$e the difference in ce!! E and + to see if o gain or !oss !oss & samp samp!e !e com comp!et p!eted ed 5or$ 5or$ bo boo$ o$ is gi(e gi(en n be!o be!o5 5 for for refe refere renc nce e 'ecreate the same 5or$ boo$ b app!ing appropriate form!ae for each n$no5n that is to be fond


Department of CSE,

22 | P a g e

L,/(ca% ,&ea'(,) P,%e- 1: 1 *n(o$e the &/D= fnction to find the otpt of =3 &/D I in the examp!e be!o5

*npt

& 0 0 1 1

: 0 1 0 1

.tp t & &/D :

StepsB 1 T Tpe pe K<&/D=C12 K<&/D=C12,D12 ,D12K K =5ithot =5ithot the @otes @otes here and drag drag to the next three ro5s


Department of CSE,

2" | P a g e

P,%e- ": 2 Simi!ar to the pre(ios exercise, figre ot the trth tab!es of .', 3.', /&/D, and /.T gate b sing their their respecti(e exce! fnctions

*npt

& 0 0 1 1

: 0 1 0 1

.tp .tp .tp .tp t t t t & & .' 3.' /.T /.T : : & :

.tpt & /&/ D:

StepsB 1 to find & .' : se the form!a .'=&,: 2 To find & 3.' : se the form!a 3.'=&,: " To +ind /.T & ,/.T : se the form!a /.T=& CTPS 201 Spreadsheet Practice Exercises –Lab Session #1 Ettimadai Camps

Department of CSE,

2% | P a g e

% To find & /&/D : se the form!a /.T=&/D=&,: /.T=&/D=&,: 

P,%e- 8: Sho5n be!o5 is a simp!e !ogic circit 5hich containing three tpes of !ogic gates Deri(e the trth tab!e for the otpt of the circit /ote that the circit circit does no ta$e the (a!e of the inpt inpt V&V or V_&V at a!!

*npt & 0 0 0

: 0 0 0

C 0 0 1

D 0 1 0

_:

_C

.tpt _: .' _ C _:>_C>D


Department of CSE,

2) | P a g e

0 0 0 0 0 1 1 1 1 1 1 1 1

0 1 1 1 1 0 0 0 0 1 1 1 1

1 0 0 1 1 0 0 1 1 0 0 1 1

1 0 1 0 1 0 1 0 1 0 1 0 1

StepsB 1 +ind /.T b /.T C 2 To +ind _:>_C do the .' fnction =ie .'=/.T=: ,/.T=C " To +ind _:>_C>D se the form!a .'=/.T=: ,/.T=C,D


Department of CSE,

29 | P a g e

P,%e- 9: Consider the design of a !ight s5itch sstem that can trn the same !ight on or off in three different p!aces .ne s5itch =& is insta!!ed in the ha!! on the first f!oor ¬her s5itch =: is !ocated on the pstairs !anding and the third s5itch =C is !ocated on the grond f!oor as sho5n in the figre

Each of the s5itches has 2 states =on and off hen an odd nmber of s5itches are on, the b!b remains off and in a!! other cases, the b!b g!o5s Design a trth tab!e and find the (arios states of the b!b for (arios combinations of the " s5itches S,%'(,):


Department of CSE,

26 | P a g e

S'e&: 1 rite rite the (arios combinations combinations of the three three s5itches in three co!mns 2 rite rite the trth trth (a!es (a!es for the conditi conditions ons 5here 5here the s5itch s5itch is on as 1 and others as 0 " Consider Consider the conditio conditions ns 5here the the s5itch is on and combine combine the three three s5itch states sing the !ogica! operators &/D,.' and /.T +or eg, for combination 2 in the abo(e figre 5hen s5itch & and : are on and s5itch C is off 5e get the form!a as :2 =for s5itch &,C2 =for S5itch : and not D2=for s5itch c 5hich is 5ritten as the exce! form!a &/D=:2,C2, /.T=D2 % Li$e5ise Li$e5ise the form! form!aa is formed formed for a!! the condit conditions ions 5here 5here the s5itch s5itch is on and a!! sch on state state form!ae are combined combined sing .' operator as fo!!o5sB 2OR?AN?B";C";NOT?";AN?B";NOT?C";";AN?NOT?B"; C";";AN?NOT?B";NOT?C";NOT?"D1

) This fina! fina! form!a form!a can be dragged dragged throgh throghot ot the state state co!mn to fi!! fi!! in the other states 9 The form form!a !a gi(es gi(es T'E T'E or +&LSE +&LSE res!ts res!ts 5hich 5hich can be con(ert con(erted ed into :oo!ean (a!es b m!tip!ing b 1 CTPS 201 Spreadsheet Practice Exercises –Lab Session #1 Ettimadai Camps

Department of CSE,

2 | P a g e

Ta+e h,-e e$ec(e 1 &s a part part of his mathem mathemati atics cs home5or home5or$, $, Uohn is re@ired re@ired to create create a natre of roots indicator for @adratic e@ations 5hich 5i!! ta$e in the three (a!es a, b and c of some 20 @adratic e@ations and indicate the natre of the roots as fo!!o5sB – *f a and b are 8ero then x1 and x2 dont exist

– *f a is 8ero, .n! x1 exits and there is no x2 – *f 5ithot rea! roots

is nega negati ti(e (e then then imag imagin inar ar  root rootss exis existt

– *n a!! other cases there are t5o roots x1 and x2 He!p ;ohn in creating this so!(er b creating a samp!e data sheet 5ith 20 a,b and c (a!es and the fo!!o5ing heads $1 a)* I-a/()a T>, $" $" 4 ,,' *(c(-()a *,)' *,e)' ,,' e$(' a  c )' e$(' e$(' e$('  &nd indicate nder each heading the existence of a partic!ar tpe of natre of roots sing :oo!ean (a!es

2. Three Three sens sensors ors are attac attached hed to a prin printi ting ng devi device ce,, with with three alarms attached to the sensors. The frst sensor, "A," detects i the device needs ink. The second sensor, "B," detects i the device needs repair. The third sensor, "C," detects i the device should am. ! the device ams or needs repair, alarm  sounds. ! the device ams or is short on ink, alarm 2 sounds. ! two or more pro#lems occur at once, alarm $ sounds. %esign a truth ta#le invo involv lvin ing g $ sens sensor ors s and and $ alar alarms ms and and fnd fnd out out the the various com#inations o sensor outputs that result in the ringing o the $ alarms.


Department of CSE,

2J | P a g e

Te$' Te $' F)c'(,) F) c'(,) P,%e- 18: 4i(en the be!o5 5or$sheet rite appropriate text fnctions in exce! to ca!c!ate first name, !ast name and emai! id

S,%'(,):

S'e&:

1 The form!a form!a for ca!c!atin ca!c!ating g !ast name is

Department of CSE,

"0 | P a g e

L,,+ U& F)c'(,)

P,%e- 19: G(6e) 'he e%,> 'a%e; 1. Ue h%,,+& h%,,+& 7)c'( 7)c'(,) ,) ', *(&%a4 *(&%a4 '*e)' '*e)' )a-e S,+ Pa/a) Pa/a) 2 Ue 6%,,+& 7)c'(,) ', 7()* 'he c,-&'e c,e ,7 'he '*e)' )a-e* Kh,) Cha))a a)* Che)/ S,+) G(6e) 'he 'a%e:

S'e&: 1 H!oo$p H!oo$p is is app!ied app!ied sing sing the form!a form!a ; 7e'ch S'e6e -a+ () E)/%(h  CTPS 201 Spreadsheet Practice Exercises –Lab Session #1 Ettimadai Camps

Department of CSE,

"1 | P a g e

S,%'(,): *n an one of the empt ce!!s app! HL..WP=QSte(eN,&1B*%,%,+&LSE 5hich retrns 91 Here Ste(e is the !oo$p (a!e,&_B*% is the rangeGtab!e of search, %th ro5 5hich is the Eng!ish mar$s mst be retrned and 'ange !oo$ p is fa!se as 5e re@ire an exact match Hence the otpt 5i!! beB

P,%e- 1#: G(6e) 'he e%,> 'a%e; e 6%,,+& ', 7()* 'he &(ce ,7 a &h,', 7a-e


Department of CSE,

"2 | P a g e

S'e&: 1 *n a ce!! sa E2, tpe tpe in the form!a form!a <7L..WP <7L..WP=KPh =KPhoto oto frameK, frameK, &2B:19, 2, +&LSE 2 hen o press press Enter, Enter, it sho!d sho!d gi(e gi(e o the ans5er ans5er,, 5hich 5hich is JJJ

P,%e- 1: I) 'he a,6e e$ec(e; Le'  a4 4, ha6e a 'h(* c,%-) 'ha' ha 'he ca'e/,4 7, each ('e- a h,>) () 'he e%,> 7(/e. Th( '(-e; ()'ea* ,7 7()*()/ 'he &(ce >e%% 7()* 'he ca'e/,4. ca'e/,4.

S'e&: 1 To find the categor, categor, 5eV!! need to change the second and third third argments in or form!a 2 +irst, +irst, 5eV!! change change the range range to &2BC1 &2BC19 9 so it inc!des inc!des the third third co!mn CTPS 201 Spreadsheet Practice Exercises –Lab Session #1 Ettimadai Camps

Department of CSE,

"" | P a g e

" /ext, /ext, 5eV!! change change the the co!mn co!mn index nmbe nmberr to " becase becase or categories are in the third co!mnB <7L..WP=K4ift bas$etK, &2BC19, ", +&LSE % hen o press press Enter, Enter, oV!! oV!! see that that the 4ift 4ift bas$et is in the 4ifts 4ifts categor


1 *n the the abo(e abo(e tab!e, tab!e, find find the fo!!o5in fo!!o5ingB gB a The price price of of the the cof coffe feee mg mg b The categor of the the !andscape painting c The price price of of the the ser( ser(ing ing bo5! bo5! d The catego categor r of the scarf scarf 2 Consid Consider er the the fo!!o5 fo!!o5ing ing tab! tab!eB eB

a rite rite an exce! exce! fnction fnction to come come p 5ith 5ith the price price of a prodct prodct 5hen the prodct id is tped into a gi(en ce!! . rite rite an exce! fnction to come p 5ith the a(ai!ab!e stoc$ of a prodct 5hen the the prodct id is tped tped into a gi(en ce!! 8. 4i(en the t5o tab!es be!o5, app! a HL..WP form!a and pop!ate the Histor mar$s in the first tab!e sch that o get the second tab!e as the res!t


Department of CSE,

"% | P a g e

% 4i(en 4i(en the be!o5 be!o5 E!emen E!ementt tab!e, tab!e, find find the &tomic &tomic -ass -ass of :oron :oron sing sing !oo$ p

) Consid Consider er the the be!o be!o5 5 tab!e tab!eBB


Department of CSE,

") | P a g e

Disp!a the name of cstomer 5ho prchased prodcts 5ith .rder*D<10""2 Disp!a the name of emp!oee 5ho processed this order Disp!a the date of this order Disp!a the .rder*D in 10th ro5 of the tab!e Disp!a the cstomer cstomer name and re@ired dateof this order V(a%(a'(,) ()/ Cha' P,%e- 1<: G(6e) 'he e%,> *a'a 7, 7(' a%e; Cea'e a %()e cha' a h,>) e%,>:


Department of CSE,

"9 | P a g e

H()': 1 4oto *nsert *nsert Chart Chart and choose choose !ine chart chart 2 Se!ect Se!ect the data data from from &2 to *1% *1% " 4i(e ne next next and finish to disp!a the chart 

P, P,%e %e- 1=: 1=: Ue Ue 'he 'he S'a' S'a'(, (,)a )a4 4 O O*e *e F, F, Th Thee M,)' M,)'h h O7 Mac Mach h &ea*hee' () P,%e- 8 ', cea'e 'he P(e Cha' e%,>.

H()': 1 4oto *nsert *nsert Chart Chart and and choose choose pie chart chart and choose choose a sb chart chart tpe tpe 5ith "D (isa! effect 2 Se!ect Se!ect the data data from from &" to :21 :21 " 4i(e ne next next and finish to disp!a the chart 

Ta+e H,-e E$ec(e 1 se the +rit +rit Sa!es Sa!es spreadsheet spreadsheet 4i(en 4i(en in the pre(io pre(ioss prob!em prob!em to create the Pie Chart be!o5 CTPS 201 Spreadsheet Practice Exercises –Lab Session #1 Ettimadai Camps

Department of CSE,

"6 | P a g e

Create simi!ar Pie Charts for the other +rits, months, tota!, anna! tota! in this Spreadsheet HintB se the CT'L $e to high!ight or se!ect se!ect the ce!!s or co!mns o need to create these charts 2 4i(en 4i(en the be!o5 spread spread sheet sheet for a fi88 drin$ drin$ sr(e sr(e,, create create the pie chart gi(en be!o5B


Department of CSE,

" | P a g e

&!so create simi!ar pie chart for 2 nd preference


Department of CSE,

"J | P a g e

Pa/e Re7ee)c()/ P,%e- "0: C,)(*e 'he 7,%%,>()/ ce)a(,: 'he e-&%,4ee h,%4 a'e ,7 &a4 ae ',e* () ,)e &ea*hee'; a)* a %(' ,7 h, >,+e* *()/ 'he %a' -,)'h; ae ',e* () a ec,)* &ea*hee' a h,>) () 'he 7(/e e%,>: H,%4 Pa4: Sa%e Tea- H, J,+e*:

C,-&%e'e 'he @Sa%e Tea- H,@ &ea*hee'; , 'ha' (' h,> 'he &a4 ,>e* ', each a%e 'ea- -e-e; 4 %,,+()/ & each &e,) a'e ,7 &a4 7,- 'he @H,%4 Pa4@ &ea*hee'. S,%'(,): The fo!!o5ing figre sho5s the 7!oo$p fnction, in ce!!s C2C" of the KSa!es Team HorsK spreadsheet, sed to !oo$ p the hor! pa rates for each each of the sa!es sa!es team team member members s hi!e hi!e 5ritin 5riting g the (!oo$ (!oo$p p fnct fnction ions s second (a!e name! Hor!Pa_&B:, Se!ect the sheet in 5hich the hor! pa is present and se!ect that tab!e and press enter and exce! 5o!d fi!! the form!a in the Sa!es team hors sheet


Department of CSE,

%0 | P a g e

The res!ts of these 7!oo$p fnctions are sho5n in the spreadsheet be!o5 &s expected, each team memberVs hor! rate of pa has been inserted into the corresponding ce!! in co!mn C

+o!!o5ing the abo(e step ca!c!ate the pament de as a m!tip!ication of hors 5or$ed and hor! rate Ta+e H,-e E$ec(e 1 Crea Create te an exce exce!! shee sheett ca!! ca!!ed ed orde orderr 5hic 5hich h cont contai ains ns prod prodc ctt *D and and @anti @antit t so!d so!d Create Create anothe anotherr 5or$ 5or$ sheet sheet ca!!ed ca!!ed in(ent in(entor or  5hich 5hich con onttain ains prod rodct ct*D and and nit nit pri price ce +i!! i!! the the t5o t5o shee sheets ts 5ith 5ith appropriate data and then in the order sheet fetch the price of the items ordered and create a tota! bi!! inc!ding taxes and disp!a the bi!! for the cstomer


Department of CSE,

Spreadsheet Practice Exercises-2016F

Recommend Documents