hello guys my name is Evans and welcome to this continuation of this video in this series we started looking at the 2017 main June ITCC ICT paper 3-1 in the last video we were looking at sections on Web authoring and then we pick up with data analysis in this in this section so test3 spreadsheet Tara web use a spreadsheet to record the hours worked each week and calculate each employees pay okay so our currency values are in dollars with two decimal places and use the most efficient methods to create this spreadsheet okay so we're going to come to step the Matane with things open and examine the file 173 one imp dot CSV in a spreadsheet package okay so let me just open Excel okay okay so what we're going to do is to just open this file go to browse then the tutorials should be in here okay CCS we found so just say all files so that it's C good so we have this file at hand okay and so we need to save this far as a spreadsheet with file name one seven empty and you can't it number for example one seven imp then nine a name so let's go ahead and save it as okay so go to file save as in the current folder save it as a web book and just add your candidate number at the end okay next step place your name Center number and candidate number should be center-aligned in the header of the page okay so insert header footer in the center of the page you're going to insert your name because Evans candidate number cerium rather Center number seven five five six and cut that number zero zero zero one okay next step on the left in the footer add the text last edited by or on followed by today's debt which is automated and time automated so you're inserting both date and time okay so in the left in your footer so insert header and footer then you go to footer in the footer left aligned you're going to insert last edited on and then it's supposed to be dead and time okay so let me just see if there is a full colon at the end there okay so it's just supposed to be last edit on just that okay so what you need to do now is to insert if you want you can set the current date and time because that's the format you've been given so in followed by today's date and time okay so instead she one can go to insert let's see no no you come back to where we were what was that okay here put the time okay add the current debt to the header or footer and then put the time also just that okay so and debt and time and then just click away and you see this debt and time although it is not showing this space so you need to put some space there so that when you click away it shows this the debt and the time that you've added this okay so that should be perfectly fine okay so next step merge the cells a1 to g1 okay and format this mid cell so that Texas Center aligned with a white 24 points and cell font and it has a dead green background okay so let's go here so a1 to g1 we merge the cells so this is go to the cells in question a one is this one that's a one to g1 this cells okay merge them merge and center make sure that they have a dark green background so that is that is that green white lemon just come back to the question paper it's supposed to be first for 24 points so 24 points and then so 24 points and then the text centered is supposed to be white and supposed to be sense of text okay so let's go back here so change the texts to be white text should be white and then I don't know if there's any other thing that we've left out so the text is sent aligned with the white 24 points and cell phones yeah so it just needs to verify that the font actually in San serif so calculates unsafe so that should be fine okay next step use cells f5 to be 14 to create amend range code Jessie okay so f5 to be 14 and take a screenshot evidence of the creation of this named range and place this in your evidence document so f5 to be 14 so f5 this one to be 14 yeah f5 to be 14 this one let's just right-click and define them check that it is f5 to be 14 just verify also in the question paper you don't want to create cell that is different so f5 to be 14 that's the one we have and we need to call this name range as JC okay so call it as Jessie and it refers to f5 this is the absolute references remember we talked about absolutely free seen in the past and relative okay so the dollar sign refers to absolutely free sin and that's the sale in question which is been highlighted over there okay so what you need to do is to take a screenshot of this okay take a screenshot of this this one shows you that you've created a named range so since the evidence of named range being created okay and say ok ok so that's the named range so what I'm going to do again is to select this and just double click on one of the boulders just to enlarge it so that it looks somewhat clear okay so going over this document and pest evidence of the named range here okay so that is the evidence so come back also to the question paper and yeah so insert D 18 anti function to look at a function to look up the job description for this employee okay so in cell D 18 we enter a function to look up the job description for this employee so let's go to D 18 so Saudi 18 D is this one here and 18 is somewhere around here so what we want is to use the pay code to look up to look up the job description for a for a given so the job description in question is here okay so depending on the pea coat so the job code you have let's see what are we using okay we're not going to use the job code message job description the job code is here let me just go to the question again so that I just get exactly so it's a intern fashion to look up the job description so the job description if you come back to to the excel sheet you have pay code which is routine maybe their salary and stuff like that then you have the job code which is actually looking up the job code is actually match in the job description so for the job code what you're going to do is to compare this job code that you have with what you have in your named range and if there's a match you retrieve the corresponding job description and that is what it's going to be displayed here okay so what you need to do is to adjust as you say in equal to you can insert your vlookup formula if you want the lookup value is actually supposed to be this one okay eighteen okay that's your lookup value and your tempo array is your named range okay which is Jesse in our case so named range is Jesse and then the column index is what is supposed to be retained in the in the in the in the table array so the first column here which is the java code is 1 and the second column is 2 so you return you're going to return to column index supposed to be 2 and then you're going to return rather you're going to the range lookup is actually boolean variable which is supposed to return false for exact match and true for an approximate match so in this case you can type force or you can type 0 that's too fine okay so I'll type force and that should be fine okay so you can verify this the lookup function is a 18 and comma Jesse comma 2 comma force and that should give you the value for the job description so not this just select this notice that when it is X it is Junior web developer go to X and you see that it is junior junior web developer okay so that is done next step in cell G 18 enter a formula to calculate the pay for this employee okay so this will look up the rate of pay from the pay rates table and multiply it by the number of hours worked okay so this formula must include an absolute range and two relative references and must not include a named range okay so what you want is an absolute range that means that you're going to select the cells and give them the dollar sign symbol so that they do not change the relative references which means the cells will be adjusting on themselves when you replicate the formula and also the named range you must not include the name range in this case so Jessie is not going to be included so the pay actually you're going to use mrs. Scott back here okay so they in g18 I think let me just verify it's supposed to be in G 18 in G 18 was supposed to retrieve the pay so let's look at the pay rate we retrieve in the pay rate what is in this table okay so come back here okay the pay is supposed to be that so scroll up so I'm trying to just look at the the pay that we have okay so let's find out someone face there's some information that I'm missing because we need to find some this one so this will look up the rate of pay from the pay rates table okay so we just need to retrieve the rate and then multiply it by the number of hours worked okay this is what I was looking for so the number of hours worked actually we have it here this is the number of hours worked and then the pay rate is supposed to be here okay so what we need to do is to compare now the pay codes okay so compare the pea coat so this pay code compared with this pay code if there is a match retrieve the pay rate and then whatever you have retrieved multiply it by this one okay so the first thing that is supposed to do actually is to retrieve the pay rate okay so let's retrieve the pay rate so equal to then go ahead select your vlookup formula and then when you do that the lookup value is actually the pay code which is C 18 the table array in this case we just get in this one just from here to there okay to e8 I don't know ii seven this is e8 that would be your yo-yo table but you need to make sure that this one is absolute reference in okay so I'll show you how you can make this one absolute reference in just by clicking when when it's like this just press on f4 on your keyboard and it makes it absolutely frets in okay y absolutely Francine because we want because this range that we've selected all this is this table array that use licked it it remains the same it will not change even if when we replicate the formula it still remains the same again throughout throughout their working of them this excel sheet okay we are referring to the same portion of the excel sheet okay so this is why we make it as absolute referencing okay now to the column index is what column I will trip in the value from so the first one is 1 and the second one is 2 so we're driven from the second column and we want the force right which is an exact match okay now this one just retrieves they the pay rate so if I click on OK you notice that the pay rate when the pay code is a the pay rate is 15 okay let me just close this so the pay rate is 15 now what we are told is that this formula the pay rate must be multiplied by the how has worked so this one should be multiplied by hoursworked which is that one F 18 and hit Enter key on the keyboard and that is the case now if you notice this formula we have an absolute referencing and a relative referencing absolutely freezing which is this one with the dollar sign and relative referencing which is C 18 and F 18 what happens when you drag or copy this formula and drag and drop and o C 18 and F 18 will be adjusting depending with a raw number that we are on but D 5 and D and E 8 will not change they will remain the same so this is what you referred to as relative and absolute referencing okay so that should be perfectly fine hit Enter key on the keyboard and you should see that there okay so now we're told that all currency values should be formatted to two decimal places we come back here we're told that all currency value should be formatted two dollars and there should be two decimal places so let's format these well we can format them here or if there is another portion of the question that tells us to format them then we can do the for my okay so we're going to do the formatting data because I see step 18 access alter the assets to format so that should be fine okay so okay I think I'll end on step I don't know how much time I've used here but let me do let me do step 15 okay it says replicate the formula from steps 13 and step 14 for all employees and so come back here and we need to replicate these formulas so just drag and drop this one to the last value which is here okay and you see that and then also get this one drag and drop it on the last value which is this one okay so just select the table and double click just to adjust the field so we done the replication in CF sickest e n type function to calculate the average number of hours worked by the employees and display this to the nearest whole number okay so this is in sale so f 60 so we want to find the average number of hours worked by the employees and display this to the nearest whole number so in cell f 60 so f is this one and F sickest is this one the number the average number of hours worked is supposed to be here so we're supposed to display this to the nearest whole number I think let me just verify so it's supposed to be displayed average worked displayed to the this to the nearest whole number so what you need to do first of all is to get average okay and get now the sales involved now the sales involved remember this is F the first one we had was F 18 remember we just used f-18 and the last one we have is this one okay if 58 and do close it and do that okay now we need to display this one to the nearest whole number okay to the nearest whole number now there are number of ways that you can do this you can either use this one here or you can use the round function okay but because we have not been told to use that one function you can just simply remove knock these out and display it as a nearest whole number like that that should be perfectly fine okay you notice it's 22 which is a whole number alternatively you can use the round function and supply 0 number of decimal places so instead of saying instead of knocking the zeros out you can just get this average and pass round round and then number of digits that you want I think like this and what did I just do that okay so let me see is there something I can use is there something like int okay int okay runs it to the nearest integer okay so let's use int okay and pick it up I think we use the decimal places there in some way I don't know but it shouldn't give us the point 0 0 there let me just leave it let me just undo this it shouldn't give us the 0 0 maybe it's because we put in some decimal places initially but it shouldn't give us you shouldn't give us this one so let me just do that I think it's because we did this initially but what should happen is that whether if you use int value or int average whatever it's supposed to be just giving you the integer number without the decimal places okay because integers don't support decimal places okay so that should be perfectly fine unless we are told to specifically use the round function or the in function then we can go ahead and do this that okay I think I've done some mini minutes in this video what I'm going to do is that in sale let me just check the time guys I don't want these videos to be very long some people tell me that the videos sometimes they tend to be very long okay so I think I will end this video here and in the next step we're going to pick up with step number step number 20 rather step number 17 and 17 will take us all the way to September 22 which would be the last one in this video so thank you so much for watching and I'll see you shortly in the next video

"Exel shit"

🙂

MY MAN HELPFUL!

thank you so much!! You are very sneaky

i searched so many videos for these exam, but yours were the best!!!

plz don't stop making these videos.

21:00 Just do this =ROUND(AVERAGE($F$18:$F$58),0)

thank you Mr. Evans. you were really helpfull

Evans you the best <3

pro tip: it saves time to switch between tabs by using alt+tab, hope this helps! thankyou for solving this paper, it really helped 😀

You have helped out a lot! God bless you. Please never stop making these videos.

Sir, could you tell me if we can use round function for q16? If not, why?

Thank you so much.It really means a lot to me and i just hope i pass my ICT practical exams.

hello sir, your tutorial have helped me alot in IGCSE 2017 just because of you i got an A, i was wondering if you could do past papers for AS LEVEL IT please it would mean a lot thank you…

when will u do oct/nov paper 31

can you make paper 32 may june 2017 please i have external ict on tuesday paper 3

Sos mi ídolo

can you put the link to download de paper. please