IGCSE ICT 2016 May June paper 32 Data Analysis Excel



hello guys are my name is Evans and welcome to this tutorial where I'm going to start doing videos on the IGCSE ICT 2016 June pepper 3 and this time around I've decided to start with pepper 3 – I hope I will manage to do a video on pepper 3 1 but if I don't my thought pepper 3 2 would really give you guys some nice concepts that you need for the exam so this video is going to be in three parts the first part is going to do data analysis and the second part Web authoring and then the last part I think should work with training notes and stuff so we'll go straight into Excel Menem of you did complain about Excel that did have some confusing formulas and oh I hope I hope not to take so much time in trying to explain those formulas but here and there I will do explain the formulas for you guys to know what you're doing out love it that you go into the exam with confidence that you're going to use this formula understanding what is going to be used for okay so the scenario is that before I get to the scenario let's deal with the task one evidence document okay so open the file method also that I did download my files already so forgot to mention that I did download my files already what I've done is that I've already prepared this fob June 2016 pepper 3 2002 my Dropbox and I'll put a link under this video where you can download these files and then you can work with it I've also tried to include the Mac scheme and um of you were asking me for the Mac scheme in the last video tutorial that I did for pepper 2 and I've decided to include the Macs came also inside so you find the question Peppa the mad scheme as well as the files that you need for this paper okay so the files that I'll be using here yeah so these are the files that I'll be using for this paper I hope to be quick oh as quickly not as quickly as quick as possible ah alright okay so let's take a dive into what we're supposed to do it for today today or tonight this is tonight this is nine almost 9:20 p.m. my time I hope to be done maybe by 10 p.m. or so okay so open the file 1 6 3 2 evidence dot RTF and make sure that you sent an amp and candid number on every page of your evidence document by placing these details in the header okay so we've been told to put these details in the header and let's go ahead and do just that so we're looking for file code evidence is this one open it and ok so I'm going to let's see I had the file that I was working on initially just seen if this work out and didn't close it so right so let's go and sit into our header blank 3 column and again I cannot emphasize or overemphasize the importance of using the blank 3 : okay Jocasta Evans ZM 5 5 6 and lastly my candid number zero zero zero one so this is done let's go back to the question paper save this evidence document as a word process document in your work area as 1 6 3 2 evidence followed by a candid number so let's go ahead and save that document almost saved apart the PDF file the question paper okay so file and save us into my current folder and then I don't need to change to the current format of the software I'm using so I'm just going to add the content number at the end there and click on save okay right so you won't need your evidence document during the examination to enter answers to the questions and to place your screenshots in when required okay so task to spreadsheet so the scenario is that you're going to prepare a spreadsheet for Goa Elephant Sanctuary to manage employees wages okay so use the most efficient formula guys this is very important there are many ways of doing things here but you must use the most efficient formula for example if you're going to add up sales and range of sales let's say from 812 to – let's say – a 50 you cannot just set up and say in a 12 plus a 13 plus a 14 plus a 15 plus 16 all the way to a 50 I mean even though you will get the true answer but you that is not the most efficient method imagine how long it would take you to do just that by the time you get to a 50 suppose you have over a thousand sales and what are you going to do if you are going to add up like that so you must use the most efficient formula throughout this data analysis section okay so you are going to display all currency values in indian rupees to two decimal places okay for example that's simple there I don't know if it's the symbol for Indian rupee you guys from India I'm sure you know that symbol but I know the I in error it could be Indian rupee just by look it just by looking you can tell this is Indian rupee okay so we're going to do that my currency actually is the symbol it's K and this part is zmk okay so oh yeah so one rupee is equal to 100 paisa I don't know if I'm pronouncing it correctly maybe it's paisa Oh what but could be paisa hundred paisa and so that is the scenario that we have so guys just be kaffir the currency values are supposed to be displayed to two decimal places okay mark these guys very important if I had the highlighter here do I have a highlighter here now it's okay so step one using a suitable software package load the file one six three to shifted CSV okay so the following question is this one I'm just going to double click on it and by default it to open in Excel the first thing that you wanna do when you just load up this file is to make sure that this turbo you select it here and expand the fields like that it's very important guys I cannot overemphasize this it's very important that your fields you are able to see the borders or the boundaries where your big column is your C column is and what is in each of these fields it's very important and as you continue to work through as you progress if you enter something that is long and it's extending to another field click here and double-click to expand you your column width okay so that every data actually fits within the column it's actually one of the requirements before your printer that make sure that your data is actually fitting in each column okay so let's go ahead okay so we have given this and then we need to save this file as a spreadsheet I'm very important but we are going to save it with one six three two and the scroll and then send a number and kind it number for example one three six two and then the center number and the country number as this so let's go ahead and save this file save us in my current folder I'll just remove the sheet there and add the center number zm5 five six and under scroll 0 0 0 1 and we are asked to save it in the format or as the spreadsheet so save it as an Excel workbook and click on save next step place an automated file name which includes file path on the left in the footer okay so let's go ahead on the left in the footer we include an automated filename which includes the path ok so go to inset and then go to header and footer now once you are to head on footer you may try to scroll down here and you notice that you may not see the footer at the bottom there so you don't need to worry ok you don't need to worry so I'll go back here and insert it and footer so how you maneuver around I don't know if it's the same with X I'm in Excel 2013 and 2010 or 2007 to play with what you are using but maybe set it slightly different but on this one how you go to the header and how you go to the footer you just under here where it says navigation click here and you go to the footer click here and you go to the header ok so go here to the footer and then in the left column here you are going to add the file name + path now guys you must be very careful the file name here you don't find quick putts like you'd find in Microsoft Word but they found them and path I hear the time and everything that you need the page numbers everything is here ok so these are the header and footer elements in other words these are the things I can go in a header and in the footer in Excel ok other than that you can just type in whatever you want so if you go here and say file name and then file path this is wrong guys because one you have the file name which is the file here and again you have the file at the end there so this is acted what you are saying this is file name + path + filename again and it's it's not good ok so you don't need this so I'm gonna just I this or delete simply delete this I don't need it okay what I need is just file pass the one that says file pass it is at the name of the current file including the full path to the header or footer if you hover your mouse over found them it says add the name of the current file to the header or food that this one doesn't put the path so the one that you need is this one that will be able to put the path then you'll be able to save path and file okay so when you click outside there you should be able to see your path and then at the end there you will see the file name ok good let's go back to the question paper so we done with this one and you there you go with your first mark congratulations you've got your first mark ok so number 2 insert two new rows between rows 12 and 13 okay my wife is looking at me she's disturbing me though I guys I'm with my wife at the office so she's looking at me and I'm recording something so she's disturbing me all right okay so the next thing that I want to do is to insert two new rows between rows 12 and 13 and so let's go back here rows 12 and 13 so I'll scroll up Road 12 and 13 so this is 12 and this is 13 now guys I must say here that if you have been asked to insert a new row between two existing rows the only way you can do that is when you select the last row that you've been asked for example I've been asked to select to insert between drawer 12 and row 13 so if whatever went to instead is going to go between row 12 and 13 then I have to select the last row which is row 13 and right click and say inset and you see just after row 12 our half a blank row and I'll right-click the same 15 and insert again and just after 12 I have two blank ropes okay so as it where I've inserted two new columns between row 12 and 13 but the one which was 115 is now row 15 and so the two new columns are these ones here okay so this is what we have done next in cell 14 enter this okay now guys again when you've been asked to enter something and that the name has been put in bold there at the examine is actually asking you to type it as it is so the Kawada is capital data you need to enter capital letter with a small attorney to enter smelly that the data should be entered accurately okay the way it appears the way it is don't try to enter it the way you think it would look nice okay enter it the way it is in the equation pepper okay so for the sake of time I'm just going to copy this okay and go back to my excel and I'm entering this into pest innate in 14 okay there you go next question next step step 4 make the cells in 14 to H 14 and then let's just go do that 14 is that one and merge and center go back here and we do some formatting says format this all so that the text is Center aligned of course where we did match cell major center it will center line but we want it to be black with 24 points and it's a cell phone so it is already black and it needs to be 24 points so just go ahead and select 24 and it needs to be let's see the last part needs to be a cell phone okay currently it is a sans serif font because Calabrese a sans serif font and I'm going to select Times New Roman okay that's better go back so it says next step make the contents of the cells in row 1 to 15 and 16 bold and italic okay so 1 2 then 15 and 16 we need to make them bold and italic so I'll select row 1 hold my control key on on my keyboard hit selects row to check how I'm selecting right on the row numbers here okay not here but raw numbers and the entire row is been selected then I'll go to drawer 15 click here and click here and I've selected them so I need to make them bold and italic okay so we've made them both and italic as you can see they're bold and italic excellent so we go back and let's go to steps step 6 instead of this event in entire function to look up from the extent of our 162 jobs dot CSV and the job description of the employees okay so let's go back to the file that we have been asked jobs is this one I'm going to just open it for now and this is the file that we want I'm going to select it and just right click now the ISIS is very important because you've been just asked to use a lookup function they've not told you whether you're supposed to use vlookup or H lookup and in some videos I've stated a difference and when and where you're supposed to use vlookup and H look up now just for the sake of you guys just refresh our minds vlookup is actually used when you are searching for data that is listed in columns ok we are not in necessarily in columns yeah in columns let me say when you have data that is in this direction ok you have multiple rows or let me just say when you have the number of rows are more than the number of columns then you are going to use vlookup to actually search throughout that data ok when you have the number of rows in this case we have got 23 rows and we have got only two columns so when the number of rows more than the number of columns then you're going to use vlookup because you're saying I'm going to search in this data in a vertical way okay so that's why vlookup actually stands for vertical lookup so you're looking at this data in a vertical way from top going down there but when you have data that is arranged in this way when you have just two rows but you have multiple columns like this then you are using actually you're using the H look up okay what you're saying is that you're going to search throughout the data in a whole horizontal way okay from the left going to the right and they're going to look up the data so we are going to loot use H lookup so guys I hope I've made clear in this case we're going to use vlookup to look up this data and now we're looking up the job code and the the the lookup value is the job code and the the the result what we want to display is actually job description so I'll go back here to the first exhale and and then I'll click on the job description let's go back to the question paper it says in cell D 17 n time function to look up from the extent of power this extent of our the job description okay of the employee so we are looking up the job description so the value that is given as the job description if you go back to the other excel one you is that is the job code for example the code when is IDI whatever you are going to see ed D then we know that this is an administrator whenever you see G a we know that this is a get admission how Mercedes a get man when you have a get mad at home okay so ta is transport assistant and HR is head Ranger and so on and so forth okay so in other words the job code is giving you an idea of the job description that you have okay so we go back to our table so in the job description in our table we do have the job code and each job code should give us an idea of what works so what we're saying is I'm to compare this value in this stable compared to so for example SD I'll look it up in this stable find where there is SSD in the vertical here so that when I find where SD is like in this case I find SD here can you retrieve the fact that is corresponding directly with SD in this case can you retrieve sanctuary director okay so this is what is going to happen so for example I'll start here I'll say vlookup okay and then I'll look up see matter on of it see is it c-17 yeah c-17 because this is the job port and then comma now I specify the table that I want to use okay now I've seen some people they go right here and to the table and just rather this table and just specify the table that they want did they just specify this range okay but I'm not so much a fan of doing that okay this just specify this like that and then it's automatically picks up this of course it's okay but I don't need this okay now I'm going to step what I don't want is this part that is put in the brackets it's more like a repetition and you still have this one again put in the brackets I mean this side so I don't want this the easier way that I can do this is actually okay I can cut this I can cut it like that okay it's fine and then I just need to specify that this is my dot CSV file see is we file this is perfectly fine and it will work but just for the sake of illustrating how you guys you need to know your formulas I'm just going to go back let me just remove this go back here let me go back against a vlookup we have C 17.1 coma and I'll type in my value okay so the name of the table is actually this one so you want you can pick up the name of the table is this one so I'm just going to get the name of the table come here and pass the name of the I put my first my cotton colony or if you want just put first single colony of single court in colony single court first single code and then this file I provide the extension for this file this is a CSV file okay and then I end my coats my single coat or sink ' and then for exclamation mark okay and then I need to specify what range I want to access from this table so if I go back to this table it is from Ed – please don't click here because once you click here the formula goes up in there okay so don't click here so the table that I want is from from 8 to 2 all the way to be 23 here okay now guys this I don't want it to change when I come to replicate the formula I don't want it to change remember there are two types of references that you can make to a cell you can make absolute referencing as well as relative referencing when you do a relative referencing when you replicate the formula the cells automatically readjust to suit them the cells that you are trying to refer to when however when you use the absolute referencing even if you drag the cells that you want to maintain as the ones you want to refer to are actually not changed so they add the same cell so in this case whether whenever I replicate this formula I want always the lookup to keep on look into this array or this table ok so what I'm going to do is just to type in the a range that I want here okay so I'll go here and say absolute a absolute a – okay – absolute be 23 be 23 okay so this is the value that is going to so this is my table array okay and I'll put comma and then the column index now guys the column index represents the value that you want to retrieve okay the first column is actually both column 1 and so as you move to the right the vlookup starts to search from the left going to the right and the first value it searches for which is supposed to be the lookup value is actually the the first one the column 1 ok the lookup value is a column 1 and the one that you want to retrieve actually is column number true and number 2 not true column number 2 all right so let's go so what I want is as you can see job description is column number 2 so I'm just going to type in to here hit comma and then I select whether I want true or false okay and true returns an approximate approximate match and false returns an exact match in this case I want exactly when it's SD I wanted to give me exactly what it still presents when FD I want exactly what if d represents so this is going to be false now remember they are boolean values okay true is a boolean value and forces a boolean value and we know true is represented by by one and force is represented by 0 so if I type in 1 there then I'm referring to approximate match if I type in 0 I am failing to exact match so it doesn't matter whether you use 0 or 1 yeah okay I love just to use force okay let it be false okay and I'll close this and that pink eat enter and you see that SD actually Treves sanitary director okay done with this one okay glad you guys if you have any question on this one I'm just going to select this and enlarge it and that's fine if you have any question please go back to provide the video that's why it's a video this is a video tutorial you can always go back and do that but if you do have a specific question you can ask me and the comments he and I have if I do have some chance to look up you know edges up so many things to do nowadays and I'm hardly having time to record these videos but it's a sacrifice as you can see it's almost 10:00 p.m. my time here and my wife just came along to keep me company as I'm so I was going to record this video so I think you shouldn't save a big thank you to my wife and not many wives I do allow their husband to stay up this late at work but yeah I think you should say a big thank you to my wife for allowing me to record this video for you guys okay so um I'm going to go to the next step so we're done with step 6 and I'm going to go into step 7 it says in cell in 17 we're supposed to enter a function to 1 look up from the pay grade table the annual pay rate for this employee and then we're going to multiply this by the value in the wax column okay so again the decision comes in whether you're going to use let me just do this the dicin will come in i we're still looking up using the lookup value but the decision comes in whether you're going to use a vlookup if you look up or you're going to use each lookup or you're just going to use the lookup function okay and each of them has bought something specific that you can go for or that can be returned in our case let's look at the pay grade table that we are looking at so I'm going to just escape from this let's go to the this table so the pay grade table is actually this one here so this is the pay grade table as you can see data is arranged we have more rows than columns so we can use vlookup it can work we can also use just look up okay now explain why the lookup is such a powerful function that you can use guys in this in this in this tutorial okay so maybe let me just type in what I want to type I'm gonna go for the for the lookup function and I'll explain why I prefer looking up the using the lookup function in this case not to say that the vlookup function cannot work it can work because you are comparing the pay grade which is this one comparing with any pay grade here and retrieving the corresponding value here the same thing that you did for job description you can do here and it can work perfectly fine okay so I'm gonna stick just with a lookup function just for a change oh you guys probably you are also familiar and use that arrow and also in that the max scheme also uses the lookup function and it will be wise for me to go with a lookup function and explain to you how this one is working so there are two things that I want to clarify here so let me just highlight pause my screen and just get this section here so I can just talk about these parts iam let me just do this like I said guys I'm using light shirt is a very powerful software I just want to use it to explain some trunks of some things here so let's start with the lookup value this one here now the lookup value guys is a this is something that you guys have been talking about even when you look at the vlookup and the edge lookup it is actually the column which you're going to be comparing with another column in another table and by trying to find if there is a match in that in that column okay so if the value here that is pointed by the red arrow rather matches the value in the table that you are looking into then you can retrieve or do something to perform some action okay now the vector let me just get this the vector is actually the column that you're going to be comparing so to say okay you're going to look up all the values there so the lookup value is this one that you want it's an individual value okay it's an individual value but the vector is a range of values that match this so the lookup value actually is being compared to the values that exist in this vector if there is a match then what is retrieved is the value that is in the same row as the result vector let's give an example we have a one here okay let me just extend this let's do this let me just extend this so that you see we have a 1 here and I want to compare a 1 within this table let me just do that and do that so I want to compare a 1 this is my lookup value and this is my lookup vector okay and I'm comparing this value whenever there is a match in this case it matches there then I pick up the lookup the result vector is this one and then the value that is retrieved is actually corresponding directly where there is a match and that will be the value that is going to be retrieved so in other words this is my lookup value look up value okay and this is my lookup vector okay let me just put an arrow there this is my lookup vector okay and right at this point let me just change my color pardon me guys whenever you're thinking like a computer scientist everything so right at this point you have a match okay so this is where the compare is in the second place so the lookup vector all this values here will be compared to the lookup vector and because there is a lookup that so this part here this is my result vector now the color that I've chosen is crazy it's crazy indeed crazy green let's choose okay let's use a lot of that pink or purple I'm not so good with colors this is my result vector okay and this is the one here this is my result vector okay and this is what we're talking about here so this one is this one guys don't bother me my screen is messed up and this one the lookup value is this one and the lookup vector is actually this one oh this is guys it's like I'm learning how to match things like you know those preschool necessary students and these have these crayons to do some matching and stuff like that all right guys the way I was just trying to highlight some things for you just to make something skin if you hit a scare if you don't serious if you not seen one of that just provide this video and you see that so I can easily go back just right as well and you will see what I have done okay so I'm gonna skip this and go back here so I'm gonna provide the lookup value here which is my pay grade koume and then I provide my lookup vector which is just this one select it and drag all the way down now guys I'm gonna show you a very powerful feature on X in Excel and we're how you can make this actually as absolute reference in okay so you're going to tap f4 on your keyboard just hit f4 and you see that automatically it changes to absolute referencing I know you can't sit down here because there is this statement here but you can see a f3 has been changed to absolute referencing or you can see right from top here every to a dwarf is pinching to absolute referencing so I'm going to type them put comma and this time I'm gonna select a result vector so just click there and drag down this is relative referencing but to change it to absolute receipt I hit f4 on my keyboard and automatically changes to absolutes referencing and that's it those are the values that you supply with a lookup function and close that and hit Enter key and you see that this is going to be 20,000 and you see that a1 actually matches 20,000 guys if you don't understand this kindly rewind this video go over and do it this stuff makes sense to you okay so we're done with this section I'm gonna go to step 8 rather we're not done are we let's see look up from the pay grade step of the idle period for this employee and then multiply this by the value in the column in the wax column so we haven't yet multiplied this by the value networks column so I'm gonna just click on here and go at the end of the formula and multiplied by the value in the wax column just hit this one okay that's f 17 and hit Enter key and that is the result that you have so remember guys don't forget to multiply this by the value in the web's column okay so let's go back now to the question paper step 8 in cell G 17 entire formula to display full-time if the works if the wax column is contains one not studied if the wax column contains zero and part time is the wax column contains zero not if it doesn't contain zero or one okay so I'm going to go back here and in the job type okay I'm going to enter that that is in the job type instead g17 which is that equal to if okay so what am i comparing if works equal to 1 then this job not let me know say this job that's not a good time to use sorry full-time ok this person is actually working full-time mode our base Shama is working full-time if they are not working full-time then let's find out if they are working if they are working part-time which is zero so if it's zero then these guys they are working they are not working actually not part-time let's go back to the question paper and verify they have not yet started so let me just select that copied come back here and paste it there close that they have not started work the only other thing that remains if we could then one its full-time and zero is not started the only other thing then it's not a one it's not a zero so I'm just going to type part-time because the last one was part-time and close that close this and close this hope so yep I hope I've closed all the brackets so this this one this one and that one that one hit Enter key and there you go it displays full time okay excellent next step is this carried seven marks guys can you imagine you cannot afford to lose seven marks adjust with doing this okay one common mistake I've noticed is that students here you have one if statement one if statement here and then you have another if statement and then use that to test again for if the last one here no please don't do that the way the if statement works actually guys is that if you have if you have three conditions okay it's one or zero or none of them then you are going to have two if statements the number of if statements is almost always one less than the conditions that you have what the outcomes the possible outcomes that you're going to have okay so don't don't please don't don't confuse yourself in that regard okay so let's go back here let's go to step nine in cell 817 entire formula to calculate weekly wedge in rupees round it down to the nearest Peyser paisa one rupee is equal to 100 paisa and one year is equal to fifty two weeks so guys remember we just calculated the annual salary we now need the weekly salary we've been told that there are 52 weeks in a year so we need to find if there are 52 weeks in a year how much can somebody get per week I mean the simple mathematics there so you're saying this is going to be equal to the annual salary divided by the number of weeks 52 and give this gives you the wage for the week okay now if you hit Enter key like that you will get a value by the value is going to be a relatively large decimal number and this is why the last part here asks you to round down to the nearest paisa so the nearest paisa is one rupee two pi so it is just a hundred okay so we are rounding down to the nearest hundredth okay and they do bounded down to the ends and we just count how many zeroes that you have so in this case I have two zeros if it was the nearest thousand then I have three zeroes if it was the nearest 10,000 then I have four zeroes okay that's how it is okay so I'm going to get the same formula and before this formula I'm going to put round down round down put bracket and then close this bracket at the close of the bracket for this one just close it out put comma and then the number of digits that I want so we are told to run to the nearest paisa then Aries paisa is this one then there is paisa one rupee 200 paisa so there is pasta a paisa is hundred you count the number of zeros this is one two and so the number of digits that you retain is two and put there and you will see that this one is going to be 0.61 okay so that is going to work out perfectly next replicate the formula that has been entered in step six seven eight and nine for each employee so now has been asked to replicate the formulas that we entered from here so I'll grab lick eight this one just click and hold and drag it down to the last record here do the same thing just drag it across here nope sorry it's this one drag it here I was almost thinking I'm replicating the formula across like that but that's not the way it works out okay so click here and drag it down all the way to this one and lastly get this one drag it down all the way there just up there so I'm going to select this and enlarge my columns okay there you go so now guys what I want to do I want to let me go back here to the question paper so let's see what I want to do in sale it's 48 n time function to add the total weekly wage bill okay the total weekly wage bill in edge 48 so down down down here this is H 48 in other words we're supposed to add entire function which is going to add all these here and guys there's an easier way of doing this I'm just gonna do that what I'm going to do let me just do this there's an easier way of doing this you can actually hold out key on your keyboard hold out key and then plus equal to sign so hold out key when your cursor is here and then plus a cosine okay that is going to automatically put in the formula for you okay so let's go back here so once that box is selected our hold out key and tap on my equal sign and you see that it says some 8 7 – h 48:17 – it's 47 and then hit Enter key and that is the Sun there ok so that is an easier way of doing that if you are not good you can just go ahead and type this okay but you can save some time if you know some of these shortcuts okay so I'm going to so we almost done I see my wife is looking at me we almost dead okay so step two of apply formatting appropriate formatting to Oh okay so let's go ahead now the appropriate formatting is what we were asked in the question go back on top and this is the formatting that was gone so display display rather all currencies in Indian rupee to two decimal places okay so for example you can use this symbol or that so I'm gonna use that because that is easy to find here so I'm gonna format this okay format it this one and select this other one as well hold key control on my keyboard and format this one as well oh go okay this is not good okay sorry about that my computer misbehaved okay so I'm gonna do that one more time I'll hold this drag it down there or alternatively what I can do is just work with them separately so that I don't mean so currency tap here and press I on your keyboard to step I on a keyboard just go to Indian rupee that's the one there select it and make sure it's selected to two decimal places and say okay and then do the same thing for the last one the last one you must make sure that you select all the way to the total and go here currency select tap III and Indian rupee make sure it's two decimal places and that was there okay so that is done so the formatting has been done the only thing that you need to do again is to expand the table so enlarge the table so make sure that the width actually fit in properly okay so what I'm gonna do what I'm gonna do now is to go back to the question paper that was step number 12 so step number 13 sorts the weekly wage table into descending order of annual salary then ascending order of job description and that's just to mark there so first of all descending order of job of salary and no sir and then ascending order job description let's go ahead and do that this is actually easy all you need to do is to select the entire table please make sure you select the table here I repeat don't go ahead and just select this I will select this no no please select the entire temple first of all that you want to sort excluding the last value for the weekly otherwise if you include it it's going to change everything so then go to sort here and go to custom sort now we've been told Festival to sort by annual salary so sort by annual salary and then sort legends to smallest now this is just the first part and then there is another part that you need to sort about the job description from ascend in ascending order okay so add another level and this one sort by job description from A to Z this is very important guys this is a very important if you don't understand what I did here can you rewind this video and have a go at this again okay so I've done this to the best of my knowledge and I'll click on OK and you notice that I've sorted this in first in our priority in descending order of annual salary and in ascending order of job description and that is perfectly done seven printer spreadsheet and then you need to make sure that before it is printed your name sent an abandoned number I entered in appropriate place on the spreadsheet so I'm going to do that so I'm going to insert in my header my name and credit number so I can just put even just Center ok Casa Evans a comma ZM 5 5 6 comma 0 0 0 1 okay that's just that next step okay make sure that the orientation is landscape so we can change that when printing okay and make sure that row and column head in a displayed and all the contents of the cells are fully visible okay that is fine all this could have been done in the printing section so just come here print and then do your page setup before you print okay because you need to change all those things so make sure that is in landscape and make sure that the rows and column heading are displayed we have not been told to show the gridlines so don't bother about that but on the header and footer you can put you can check that the header have already put there I put it there so this is to find the folder contains that that is too fine okay let's go back to the question paper we need to make sure that this is in landscape and that is that so the only thing that we didn't do is to change that this should show the formula so let's go ahead and change that so just say ok and go back and change to formulas and show formulas and then what you need to do is to select this and make sure that you double click so that the tables actually or the fields are actually this visible fully visible okay so that is done so what I'm going to do is to go back to file and print and I'll go to page setup again just to verify that everything is at it supposed to be the page is landscape if I go under sheet grid and thin visible and then and I hate that I have my name in there so this is perfectly fine so just verify that all these for the name is there landscape roll hopefully display and the contest with this is awfully visible and that is perfectly fine so what I'm going to do while I open the browser close it I don't need the browser sorry about that guys okay so so this is perfectly done I can change my page size of my paper size to bf4 because I'm using a4 paper so now don't worry about this space that is going to exist at the end there is because the formula that follows next on to cannot fit now the West kind of mistake that you can make is try to compress this to fit on the page we have not been told to compress this so it's supposed to be printed as it is I know you have four pages the next page is going to appear like this the other page is going to appear like this and the last page is going to appear like this but it doesn't matter because this is what the question asks you to print okay so just go ahead and print this I'm not going to print this I'm trying to save on paper and ink and stuff like that I don't need to print this okay so you go ahead and print this I just wanted to show you how to format this so that you guys can be ready to print it okay so I'll go back here and this is going to be printed that way we can suppose it has been printed and everything works perfectly and printed up number one has been done we've been taught to print the formula of showing the values and we need to make sure that the printout fits on a single page this time around we've been told to change the page that fits on a single page and the contents are fully visible so I'll go back here and we need to remove the formula thin here and go click here and double click later okay to remove the hash tags and oh and I'll go back here to print now I'll go to page setup again now make sure the the original format of this was portrait okay so don't leave it in landscape it was initially in portrait the only question that asked us to put it in landscape was this part and it was under this section here we've not been asked that it should be landscape because the original layout of this page was supposed to be portrait guys that is very important please don't ever forget that so go back to portrait again remove the header and other column and row headings because that question doesn't ask you to do that okay the only thing that the question asks you to do are two things that it's fits on a single page and then the contents are fully visible and also that the candid number and that appear in so this is perfectly fine I've removed that and let's see how it appears so it appears just like that and it's perfect okay I'll make sure it's fo and it is supposed to fit on a single page wide so go to scaling and say fit on one page okay and that everything has been fit on one page then you go ahead and print this I'm not going to print it you go ahead and print this and this is perfectly fine okay so let's go to step 16 change the data so that or Ridgid Duta or data works 0.6 over week ruju rang gran-gran Jane works full-time and prover Subramanyam why didn't work a works 0.8 of the week okay so let's go ahead and change this so what I'm going to do is to just search for where this data is now imagine you have so many records and you don't need to you don't need to punish yourself searching ways such there are ways what what I just love to do is just type out this data okay so what I'm going to do is to just type it out the names that I had there I have my question paper here so I'm just gonna look up my question paper it's or e or rigid or rigid or more say to original supposed to be or rigid or JIT rather or JIT duta okay so for audit Duta is this guy here we need to change the the time okay the number of hours that the guy has worked so 20.6 and then we look for find next we look for the next person is the next person is rule ruju okay Rudo Ranjan okay find and this is Roger Engine or mosses Roger ramjet I mean I've seen the captain Roger ramjet it's an old cartoon of some some some crazy jump we used to we used to to take a pill when it's upset you take a pill and then you become so powerful Roger rounded look it up on YouTube maybe it could be there okay so this guy works full time so full time we were told it's supposed to be one remember when it's one then it's full time so find next so you see that run gene has changed to full time so the next person that we are going to look out for is proven prover and this is soo Subramanyam Subramanyam Subramanyam okay like it's the name of a chemical formula in chemistry yeah okay so this guy works 0.8 so here we change to 0.8 and I think that's the last one okay so close and then let's go back to the question paper and then we say save and print the spreadsheet showing the values and make sure that the printer is on a single page and the cells and things are fully display are visible so I'm going to just just to make sure that you fit on a single page and go to file and print and there is nothing much to change it because the previous setting actually allows everything to be the way it's supposed to be and go ahead and print this okay so that is that so that was the printer number three and suffice to say that is the last part in the excel our data analysis section thank you so much for watching this video I know it has been quite lengthy also because I needed to take time to explain some few things but nevertheless I hope you guys have understood and if you haven't watched it over and over the main June gives you an idea of what to expect in the November the October November paper the one that you're writing Tuesday and I asked you to go through it thoroughly just to make sure that you are well prepared for this paper so the next we took out do probably tomorrow because now I need to go home I think my wife has done a tremendous job allowing me to be here and just waiting for me to record this section thank you so much my wife my baby thank you I love you and thank you yeah I received that same one okay so um what I'm gonna do I'll pause this video here and tomorrow I'm gonna do a video on them on Web authoring and I'll probably finish it up on on 20 knots and tomorrow so I should have a full of three – pepper 3 – I've already downloaded pepper 3:1 and I would want to go through pepper 3 1 probably over the weekend I'll do a video for pepper 3 1 and you guys you can look it up as well so thank you so much guys if you haven't subscribed to my channel I encourage you to subscribe without fail without fail yeah I'm doing a good job just Boston but I'm doing good job just to hit make sure that you guys you you prepared for your exam ok so share this video with your teachers including the principal the princ from your school I like my channel and I'll see you in the next video god bless you tremendously okay thank you so much so I'm going to pause this video and see you

25 Comments

  1. Klim R said:

    Thank you very much for the video and files that you left in the description!

    May 22, 2019
    Reply
  2. J Bint Ali said:

    The exam was really good!!!

    May 22, 2019
    Reply
  3. aleja posada said:

    saved my life luv u

    May 22, 2019
    Reply
  4. Wexyhh said:

    CBS STUDENTS GET READY FOR TAKI TAKI RUMBA

    May 22, 2019
    Reply
  5. Nada Ahmedin Salih Ahmed said:

    I just finished doing my paper 2 yesterday and it was super easy and my teacher said i did very well , i will be doing paper 3 tomorrow , pray for me 🙂

    May 22, 2019
    Reply
  6. J Bint Ali said:

    Is it allowed to use highlighter on the exam paper??

    May 22, 2019
    Reply
  7. Cannie Lau said:

    without all the talking you might finish it in 15min

    May 22, 2019
    Reply
  8. Rashan Akarshana said:

    My teacher taught me a easier and diffreny way of doing vlookup

    May 22, 2019
    Reply
  9. Rakeeza Mughal said:

    hey why did you use lookup and not vlookup?

    May 22, 2019
    Reply
  10. Matab Jamal said:

    The way you say black tho

    May 22, 2019
    Reply
  11. Grade 11 Girls said:

    you are amazing chikasa !! , keep saving our lives

    May 22, 2019
    Reply
  12. Draw My Opinion Filippo Bertone said:

    Not all heroes wear capes…

    May 22, 2019
    Reply
  13. Mussy gm said:

    Well my exam starts in two hours pray for me paper 3 variant 1

    May 22, 2019
    Reply
  14. Pranai Suwan said:

    Av Exam Tomorrow, Many Thanks Evans. I won't fail as badly now thanks to you.

    May 22, 2019
    Reply
  15. Karan Wagmar said:

    It was showing me an error when I put the vlookup function

    May 22, 2019
    Reply
  16. Maria Kiasoloka said:

    THX

    May 22, 2019
    Reply
  17. David kot said:

    Beast!!!

    May 22, 2019
    Reply
  18. Randomness said:

    May God bless you!

    May 22, 2019
    Reply
  19. Abdulsalam Ali said:

    Thank you

    May 22, 2019
    Reply
  20. Grimity Gaming said:

    Can’t u just type it in?
    Ike his salary will be 2000 instead of adding all those formuale

    May 22, 2019
    Reply
  21. Evelyn P. said:

    thank you so much sir !

    May 22, 2019
    Reply
  22. aaa aaa said:

    thank you so much evans

    May 22, 2019
    Reply
  23. AGT AlienGamingThailand said:

    Best ICT teacher helping poor students suffering from ICT

    May 22, 2019
    Reply
  24. Rocket 7354 said:

    16:00

    May 22, 2019
    Reply
  25. Denzyl 711 said:

    I consider you, Evans Chikasa, a god in the capsule of a human body, if the world ever came to an end, my ice exam, then u would be there to help the people, me, of the world rejuvenate and pass the almighty's test that was supposed to be our dooming fate. Again I thank you for allowing me to not kill myself in despair for my ICT practical exam.

    May 22, 2019
    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *