IGCSE ICT October November 2016 Paper 3 Data Analysis Excel Part 2



hello guys are my name is Evans and up welcome to this video tutorial in this tutorial I'm going to be continuing with a the October November 2016 IG CSE ICT pepper 3 and in the last video we ended one step 19 and it'll either be continuous step number 20 so in cell g6 entire formula to calculate the advertising cost which is the rate entered in step 16 x 125 is the resort as 50 or less rooms 275 is the resort has more than 50 but less than ones or one rooms rooms and 150 is the resort has more than 100 but less than two zero one Grim's and then 200 in the resort has more than 200 rooms now I must say here guys that we'll come back here we're talk about nested if statements by nested we mean one if statement embedded within another statement it means one if statement being dependent upon another if statement okay so when you talk about nested if statements these things are very important images and the line this part one if you've got two options things like a girl or a boy you want to choose and you can only choose one of them then you can only use one if statement okay you don't need to nest these ones and that means that you can say if in if in excel you're comparing a gender you say G 3 if G 3 equal to boy then the true part is that you say hello sir if it's the only alternate that remains if it's not a boy I mean what else can it be if it if it's not a boy I mean what else it has has to be a girl okay so okay so you say hello madam okay so one two options will give you one if statement if you have to if you have got three options such as mango Popo and orange suppose we want to say somebody only loves one of these fruits no other fruit it can only be these roots okay it's either they love mango if they don't mung love mango is that they love Popo is the door laughs Popo automatically does orange mean because that's the only thing that these remaining so it's got three options if you've got three options the thing is that one you have two if statements okay an example is that it would be one if statement will be the outer one in the loop in the nested a condition so it would be if f3 or favorite fruit row F 3 is equal to mango then if this is true then right Wow else test for the two remaining options now for the two remaining options it comes back to this one two options one if statement so you only have one statement that is going to remain so if F 3 equal to Papa which is this one then say okay so if it is not you don't need to test if it is orange because the only thing if they don't love Papa they don't love mango the only thing that remains is they love orange so just say who actually supposed to be ok so right so that is that now you can also have four conditions or four options like we have here we have four options one two three four if you have four options the logic is that you are supposed to have three statements okay generally let me just put this one generally the general rule is that if you have why am i doing that the general rule is that the general rule is that if you have X options then you are going to have if you have X options then you are going to have X minus 1 if statements okay you're going to have X minus 1 each that means so this can be anything X can be 5 then which means equal to a 5 minus 1 which is 4 if statement if X is 7 then you're going to have 7 minus 1 which is 6 if that means ok but at most you guys will be just giving you 4 if that means it becomes test and to write when you have 5 or 7 if statements and oh okay so that is the general rule so let's go back here and do the first part so in g6 the questions 8 in g6 anti formula to calculate the advertising cost which is the red entered in step 16 which is f4 which is 124 point 2 multiplied by 25 if the resort has 50 or less so let's go through that so let's start first of all with a rate so the rate is supposed to be equal to and it's supposed to be F 4 now F 4 please this must be absolute referencing because if you make it as relative referencing what happens is that when you drag when you replicate this formula let's just press enter when you replicate this form in a watch out here it says it's what fo so when you replicate this formula and do this one when you replicate this formula just click this one drag all the way down notice how the formula keeps on Justin from fo it goes to F 5 check here check here okay so from f4 it goes to F 5 to go to f6 f7 f8 and you find that you'll be multiplying these values wrongly okay so what you need to do now is to just make sure that this formula that you select for this cell which is f3 f4 must be absolute so just dub f4 on your keyboard and you make it absolute multiply it by the if statements now so if now we are comparing now okay so what are we comparing we're comparing the number of rooms so if the number of rooms is 50 or less so 50 or less so that means you say F + 6 which is the number of rooms less than 51 now why 51 because the immediate number that is the largest number before 51 is 50 and that is the one that we want which is equal less or equivalent to 50 pocket next if the value is true multiplied by 25 if it is false then go to the second if statement the second if statement if f6 is less if so it says 75 if the result is more than 50 but less than one zero one rooms so which means if it is not this part with if it is not this part then it must be above 50 okay so if it is above 50 then we are saying that it should be less than 1 0 1 so how you do that just say well just make it less than 1 0 1 okay just make it less than 1 0 1 now once you do that the true part is that you multiply it by 75 if there is another force then just say if now this is the last logical test ok remember a test with four options has three if statement so this is the third if statement we are putting in there so if F is more than hundred but listen to 0 1 so again if it is if it is less than 101 then it must be more than hundred but less than two zero one okay which means from 1 0 1 to 200 so we test now for 200 so just say if it is less than if F 6 is less than 2 excuse me is less than 200 or 2 0 1 rather bin x 150 now the first part we have exhausted all the 3 none of them is true the only thing that remains is the last one so we don't need to test it we just put it as that and close for that one close again and close again trap in the key and there you go now the beauty about Excel is that it formats automatically the field it inherits the property from this attitude that was formatted initially and it in here is that so when you replicate this formula all these other fields will be formatted and that's how beautiful Excel can be okay step 20 done let's go to step 21 instead of be 27 entire formula to add the number of rooms for the location in column a so let's decode this one b27 okay we are going to enter the formula so let's go to be 27 between 7 is here we're going to enter a formula and this formula what you would do to add the number of rooms for the location column n so this is this formula is based on a condition been met so you are going to use some is okay so that a range of cells can be added if a particular condition is met okay so what are the cells that are going to be added the number of rooms what's the condition the location that is there so the location should match the one that is in column a so let's come back here so here the location which is this one for example we have northwest should match the ones that are in column here down here okay so what we do we are going to say some if and then bracket and then you provide the range of the cells where you want this student so the range of the cells are actually from this one from this one is six hold Shift key on your keyboard and tap the last one a 24 okay now these guys must be absolute referencing so tap f4 on your keyboard otherwise if you don't have air for this should this they become they've come back so make sure that they are absolutes referencing not like this then make them tap f4 on keyboard and make them absolutely fits in like that next the criteria the criteria is if there is a match what should we compare in be comparing the location values so Northway should be compared with something here so we compare with central ok so just tap on central which is 27 okay and then coma and then the sum range the sum range is actually their rooms so tap on the number of rooms from f6 hold your Shift key and hold the shift key and tap on the last one okay those and then to make these cells absolute there isn't one to make them after differences because we don't want when we replicate this women with drag-and-drop this values to start adjusting like I showed you in the previous section okay so top f4 on your keyboard and you make it absolutely fits in close it and taps in Turkey and that should be it next step replicate the formula entered in step 18 19 and 20 for each resort let's go ahead and do that and we also replicate the formula we just entered in step 21 let's do that so replicate this one step 18 we'll drag it replicate this one but it will drag it and replicate this one drag it okay next step replicate this one bingo okay apply appropriate formatting so let's look at this is any formatting needs to do weight or that should go back only to two decimal places of which it is to treat those four places and it should be of this currency that's what that's that's perfectly fine next we need to we need to let's see where we stepped in for seven printer spread situate the formula make sure that your name Center number ten the number are displayed in appropriate place on your spreadsheet okay so let's put our details there on the spreadsheet let's go to insert and then hidden footer and go to footer and insert that okay so in search Casa Evans zm5 five six and zero zero zero one okay that should be fine next make it into landscape okay so change the orientation to landscape go to page layout come out of here page layout or indentation landscape screw-ups then change the contents of the cells should be fully displayed and oh we need to change make sure the formulas at this plane okay so come back here to formulas and show formula okay make sure that the formulas are displayed next thing that you're supposed to do is what let's see so select this double click okay all the formulas should be displayed should be in landscape and oh I don't know what else we needs to be done Center number candid number I entered an appropriate space so yeah so I think we're done with this patch let's try to see or we need to put their rows and column headings so let's go back here we try to print preview it and assurance shrink fit or not scaling so remove not scaling it shouldn't put fit on one page okay because we are not told to do that so go to page setup to add the rows and column headings go to and then go to row and gridlines add the grid nice and just not that it's going to be like so okay so that is good and then go ahead and print this out don't worry about the number of pages because some of you may tempt to make this fit on one page please don't make it fit on one page unless you're told to do that so go ahead and print this as it is and it will come out perfectly fine next step step 25 print the spreadsheet showing the values make sure that it is imported to orientation and the printer fits on a single page but this time around we are told that it should fit a psychopath sure would you'd attempt to change the scaling okay so the contents of all the cells are fully visible okay so come back here go back and this time remove the formula change the orientation to portrait and select the table and make sure that it fits on one single page now notice how despite they will try to preview it so that you see what I'm talking about if you preview it notice now that the title is kind of not visible fully this other part is okay but the title is not visible to hidden so how you do that and this is principal Microsoft metal surface coverage allows you guys to to do what I'm about to show you okay so you need to select this cell and text wrap it okay when you wrap it then you need to drag it down here so that it is fully visible just drag it around here like that then drag it a little bit further so that it is visible okay I should be fine then resize it again resize the individual cells that if they fit sort of on one page okay besides them so that they fit on one page okay so that should be I should be fine preview it print preview and you notice that it fits on one page and looks good okay so what we're going to do now no it doesn't fit on one page scale it and make it fit on one page okay now it fits on one page okay so the other thing that you notice is that we've not been taught to put the column and row headings so we need to remove them so go to page setup and remove the row and grid nice okay and it should remain like that that looks good and go ahead and print this now okay so once you print that go to step 26 change the data model so that the results and village has an extra fifty percent or fifty of rooms not fifty percent okay fifty rooms extra fifty rooms added so whatever value is existing we add fifty rooms so we are going to resort and then we change value for Sand Village come back here go back there and Resort Sand Village is here so from 25 the rooms change adds 50 that becomes 75 and then go ahead and print go ahead and print this so save and print the spreadsheet change the values make sure that it is important orientation which we've done it fits on a single page which we have done and the contents are fully visible which we have done so come back here preview it and you'll see that all the other properties are fully visible and the should be fine okay so next we going to save and print your if this document okay make sure that you have entered your names internal magnetic number and go ahead and print your evidence document okay so guys this has been Evans and thank you so much for watching this video tutorials I hope you've lit one of two things and this marks I think the end of the paper is for 2016 I've solved all the much february/march papers or the June May June papers and all the October November papers and now we wait for the March 2017 paper so that we can add it to the list as well so thank you so much don't forget to subscribe to like and comment on this channel and I'll see you in the next videos okay see you

25 Comments

  1. Ulemu Felista Makolija said:

    Hi Evans am from Malawi and , I just still dont why you had to use the number of rooms by location as the criteria in step 26 for the sumif function ,please help me .

    June 26, 2019
    Reply
  2. Sorry Bro said:

    You are solving wrong!!!!!!

    June 26, 2019
    Reply
  3. GaMeZ AtomiX said:

    My paper 3 exam is tomorrow, I am really thankful for all your help through these tutorials. Thank you again!
    Btw your tutorials are the best out there keep it up.

    June 26, 2019
    Reply
  4. WarBeastGolem said:

    Woooohoooooo

    June 26, 2019
    Reply
  5. eight arms said:

    i have a question mister Evans Chickasa i used 2 if functions and an and function in between, my answers were the same but will i get marks ?

    June 26, 2019
    Reply
  6. eight arms said:

    Thanks for this amazing work appreciate your hard work
    may god give you more and more success

    June 26, 2019
    Reply
  7. eight arms said:

    01:45 there are more than 2 genders 😉

    June 26, 2019
    Reply
  8. touté kalé said:

    love your energy 😀

    June 26, 2019
    Reply
  9. Bilal Irshad said:

    Very Helpful viseo. Thanx

    June 26, 2019
    Reply
  10. eslam madrid said:

    YO evans you are great and i like all your hard work . Keep the heat up.

    June 26, 2019
    Reply
  11. Maka T Vibes said:

    Thank you very much

    June 26, 2019
    Reply
  12. Jayaditya Suvarna said:

    thanks man really appreciate it

    June 26, 2019
    Reply
  13. Ibrahim Abdeljaleel said:

    Hello sir, for the if function part, can we use the and function?It is not written in the marking scheme, that's why i'm asking you about it…. So the formula will be =$F$4*IF(F6<50,"25",IF(AND(F6>50,F6<101),"75",IF(AND(F6>101,F6<201),"150","200")))? It works perfectly fine with me.

    June 26, 2019
    Reply
  14. Mohammad Ayaan Malik said:

    BRRRR drag it
    Lol

    June 26, 2019
    Reply
  15. Rosy Rilwan said:

    Thank u so much Evans sir.These videos really helped me, I AM FROM SAUDI ARABIA, I had my IGCSE may June 2018 p22 on Tuesday, The paper was soo easy and the credit goes to YOU.I am having my p32 tom. plz, pray that it should be easy I am just worried abt excel. But am watching ur videos and understanding everything u explain I hope the paper is easy. Thank YOU so much, Honestly, I don't how to thank You. Special Thanks from me. I appreciate ur hard work just to make sure we all do good in the exam and that's just soOO sweet of u ..u really make it fun to study. I hope u success every minute of ur life. WARM HUGS from ME : )

    June 26, 2019
    Reply
  16. Masir Javed said:

    thank u soo much for the explaination i totally understood nested if function

    June 26, 2019
    Reply
  17. ana navalĂłn villena said:

    How do I put the formula as if it was absoulte??

    June 26, 2019
    Reply
  18. Penguin Fancy said:

    When I press F4, no absolute referencing occur. Instead, projection options appear from the right side of the screen. I am using Windows 8. What should I do?

    June 26, 2019
    Reply
  19. Penguin Fancy said:

    For the formula in step 20, can it be like this? =IF(F7<=50,$F$4*25,IF(50<F7<101,$F$4*75,IF(100<F7<201,$F$4*150,IF(F7>200,$F$4*200))))

    June 26, 2019
    Reply
  20. Anoushka Panchal said:

    Thank you so much! This video has cleared all my doubts regarding if statements and I'm sure it will help me tomorrow in my exams! thx

    June 26, 2019
    Reply
  21. Uchenna Adigwe said:

    you didnt explain this properly

    June 26, 2019
    Reply
  22. aryaman Salaria said:

    Hey Evans I was just seeing your tutorials for the IF function part but I am not able to do it (=$F$4 * IF(F6<51,25,IF(F6<101,75,IF(F6<201,150,200))) this is the formula that I entered but its not working!!

    June 26, 2019
    Reply
  23. Vijay Chandugade said:

    Actually, i was a dumb guy in excel ,thanks to ur goodness of explanation about it. Hopefully, now I am fearless enough to make it.Thank u buddy.

    June 26, 2019
    Reply
  24. basant emad said:

    you are so funny and helpful thank u sooo much i dont know what to say

    June 26, 2019
    Reply
  25. Teo Scapato said:

    You are a god

    June 26, 2019
    Reply

Leave a Reply

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