ICT IGCSE Paper 3 Spreadsheets Data Analysis — Overview Part 1



hello welcome back to my youtube channel my name is josue ahmed today we're going to be doing a species overview and we will be looking at chapter 20 which is data analysis okay so now let's look at cell references so in Excel each cell has a reference so we first have to look at a column letter and then we have to look at the row number so let's look at this example here this yellow cell the reference for this cell will be first of all a column letter which is B and then row number sort of cell references B to cell reference where this green cell would be the column letter which is D the row number which is free so cell reference is d3 now here we have a range of cells so more than one cell which has been selected is known as a range so the range of this cell of these cells is G 6 : 2 G 11 its G 6 because we start with the range of cells is in column G and we start from number 6 so G 6 and arrange finishes at G 11 so what we have is the first starting of the range and then the end point of the range so a range will be typically used when you are calculating values from a number of cells so for example you could use a range when you're calculating or adding numbers in a range of cells using a song function okay we can also define the name of cells there's two ways of doing this and the first method is if you highlight the cell you want to define a name right click select define name and an answer new name for the cell so if I go to this example here so let's say example I want to call c2 instead of referencing the c2 we want to give it a name so to give it a name you can right click select define name and then we could type in a name for the cell so now this cell will be known as red let's do it again so this cell we want to name this cell as green so right click define named green okay go back to the PowerPoint you can also define a name for a range of cells or a table so let me come to here it's the same method so this range would be c4 : to f4 okay so VA we're going to reference this range it starts off a c4 going all the way to f4 let's say we want to name this range so you height highlight the cells right-click select define name let's call this range yellow okay so now you can see the range of cells is now referenced as yellow let's do it one more time right click define name this time we can call this range of cells blue okay we can also highlight a table okay and we can define the name of the table as well this is normally used when we are doing lookups so the diviner name and we can call this range of cells shopping so anytime you highlight it this range you'll see the name will be shopping okay what we can also do is if I highlight this range here define name and we can call it prices so now this range of cell if you highlight will be called prices okay so that's how you define a name for cell okay now let's look at formatting so formatting is how their cells all spreadsheet appears so in formatting what we can do we can adjust the columns and the rows sizes we can merge cells together we can apply currency settings we can insert border and shading and also text formatting including alignment and wrapping so let me look at this example here okay so we have to apply appropriate format into the spreadsheet the first thing I'm going to do basically let's look here I want to delete any cell any rows which or columns which we don't need okay so to delete rows so I need to delete these two rows well here so you can highlight on a number you can delete you can also insert by clicking on insert ok once I've done that I'm just going to click in between to see all of the information so if you click between DNA we can open up the cells ok to ensure all of the information is shown fully ok so or what we can do we can highlight like this and we can open like this as well and then we can also click on on the rose if you double click between a number to show all of the cells info ok now thing we're going to do go back to my question I'm going to merge the cell so the cell will be merged from A to B and the text will be aligned to the left and also we have to shade the cell so if we go back to here I'm going to increase the size you can also right click and click on row height and type in something specific if you wanted to so if I typed in 30 that would be also increasing the size so now let's merge so I can right click I think the best way to merge would be up here actually merge cells ok I want to increase the size of this title here and then let's shade a cell particular color okay so let's go back to my powerpoint the next thing I'm going to do is there so I've already adjusted the columns and rows to make sure all of the information is shown I'm going to now insert the border and a shading for the top row so if I go back to here so to apply a border we can click here all borders okay I'm going to delete this column here which is not necessary and now I'm going to apply shading to this top row so and if i zoom in a little bit you'll notice the Texas wrapping is coming underneath itself so let's now make the title or the first row bold formatted text so it wraps okay so I'll increase the size as well now to ensure to make the text wrap what we can do is let me just say I'm select Arial drop the size if I right click and select format cells so when it loads up I can click on alignment and I can click on this option to wrap the text it's not going to work unless you include to increase the row size and then what we can do is D crease the column size to ensure the text is now wrapping and comes underneath itself okay and what we can also do is change the formatting of the cell so if I right-click again the alignment vertically or horizontally so we can align the text to be Center and maybe Center horizontally as well so as you can see the text is sent to centrally aligned horizontally and also vertically as well so again let me just increase the size now what we're going to do is format this text is all the same font size so ten Calibri black okay let's have a look what else we can do I don't like you click on that so we adjust to the column size okay we've applied some borders of shading we've merged themselves with formatting of text including alignment and wrapping now we're going to apply the currency to the cell here so if I highlight this cell here we're gonna have to click on here to apply some currency okay or if you need to find something specific we can right click and click on number currency and then you can find a specific currency to apply so you can have a look at the different types of currencies okay you also have codes which you can insert as well if you can't find a currency you're looking for the symbol okay so that's simple formatting I'll show you a few more things if I highlight the titles and if you go to alignment if you can also vertically align the text by simply dragging this box like this and you can see if I increase now the text has been vertically aligned okay we can change it again to change the directional text if I drag this down and you can see the text Direction has been changed as well that simple formatting okay we can also do additional things just before we do move on if I right-click form ourselves we can change the amount of decimal places okay it's days in time formatting as well ok so there's lots of different options we can follow ok so now let's look at some tips so when you are formatting you can format a number in numerous ways including using a method shown on the left so you can format it and set a decimal places you can format a number into a percentage or into a fraction okay when you are formatting and values into currency you might use the option at the top in particular to format into a currency however sometimes what happens is if I go back to PowerPoint is it may display a value as a dash so 0 may be displayed as a dash if you're using this method here so what you need to do is if you right-click and go to currency this will change it into 0.00 so if you select this option here what tends to happen is if we go back to here and let's save our changes to 0 it's a parent as a – that's because I've clicked a pair and a setting has been changed to account him what you need to do is make sure you select currency ok or what you can do click on right click form ourselves and select currency from this option here so if is shown as accounting you might see a dash so the best my advice would be to change up to currency instead ok let's go to next slide so hide and show columns and rows so you can click on a column header and you can click on hide then you can click in between to unhide rows and columns and also insert and it is very straightforward so let me show you so if I want to delete this row here yeah click right click on the number 6 and you can click on delete if I want to insert a row you can right click and click on insert okay I'll just delete that if I want to insert a column I can click on insert and then I can click on delete if I want to hide and show the row and columns what you need to do is a select the columns you want to hide let's say I want to hide at these two columns C and D you right click and you can click on hide if I want to show the columns again I click on the letters in between the ones I've hidden so be an e and I'm right click and select unhide again I can do the same for the rows so let's say I want to hide the row 6 & 7 okay and then if I want to show that row I click in between so 5 & 8 right click and untied okay so you might have to hide and columns of rows if you're printing and you don't want to print certain parts of the M spreadsheets for example the spreadsheet paper will be very specific if they want you to hide any content okay so merging cells we looked at merging cells so you can highlight the cell range then you can click at the option at the top okay so I say example I wanted to merge these cells here you can click here to merge yourself into one cell okay and if you want to unmerge you can click on this option again and now it becomes individual cells instead okay and shading and formatting again to insert a color it's quite straightforward but sometimes I may ask you to insert a pattern and then a color okay so you may be asked to import specific pattern into yourself you can do this by right clicking your mouse and selecting a fill option you can then select the pattern and the color so if I go back to excel so let's say for example we don't want a color here and we want a particular pattern instead so what you can do you can right click format cells if I go to fill I can select a color for the background for example and I'm a color for the pattern or pattern style okay so a background color let me select the background color dark gray and then you can have stripes for example so the pattern color could be set so can you see the stripes of red and then a background color let's make the background color yellow so you can adjust this as well accordingly so obviously what you want to do is make sure if you have text in ourselves you want to choose appropriate colors which will not take too much attention away from the text okay now we're going to be looking at inserting headers and inserting photos so if I want to insert a header and footer okay all you need to do is click on insert header and then this is the header if you click on footer oops if I click on this wall so this option here I go to footer you go to the foot of the page if I click back up here you can go to the head of the page and you can insert your name if you want to do so yeah sir Ahmed need to spell my name right maybe you want to insert the time maybe wants to insert the date and I never go to the footer maybe you want to insert the page number in the center now to escape the header and footer you need to click on any cell then you click on view and normal okay if you go to the top click on file and print preview your show print preview you'll notice your name and the header details appears on every page at the top and then we can see the page number appears at the bottom of every page ok so to show you again if you want to insert a header you've got to insert Edwyn footer this is the header to go to the photo you click here to go back to the header you've got to click on go to header okay so now we're going to be moving on to creating graphs so to create a graph what you'd have to do is highlight the cells that would be required to create a chart okay then you have to select the correct shot so a bar or by a line chart okay then you can add labels to your axes by using the selected data options okay so our graph is used to visually display data which is easy to read and interpret so here's an example of a graph if a zoom in so as you can see this graph is showing mobile phone sales okay so it's comparing mobile phone sales for iPhone 6 and Samsung s5 so this is a legend so then we have the axes labels as well so we have the month along the bottom and then we have the number of sales going along the side here so when you are making a graph a bar chart in particular you need to make sure you have a title you display the AXI labels okay and then sometimes the question says to show the legend alter the to delete the legend so make sure you know which one you're looking at so if I come to excel let's quickly make a graph so I'm going to make a graph to compare the sales from these mums here so first of all you highlight the range if you click on insert column and because we're comparing two values you want to select this option here so what you can do you can click on the bars and if you want to change the colors if you go to design no let's go to home and then we select a color from the paint bucket and then the sex selected this color here okay so this is a legend if you want to delete the legend you can right click and delete the legend okay now what we need to do is just change these values here so the matching of the month so if I right click and click on select data so you can see the horizontal values are 1 2 3 4 5 6 if I click here these value and then click on this option here refer to the months okay so if I highlight the monks and I click okay you can see the numbers have been replaced by the months now okay so now we need to insert if I click on layout if you're using a newer version of Office you can select quick layouts number nine I'm going to insert a title using an older version of Excel so a chart title so what was the title again mobile phone sales and then we want to insert a title for each axis so this axis here will be the month and then I need to show a vertical at label for the vertical axis which will show me the number of sales okay and sometimes you may have to create a graph and you may have to copy and paste it into a PowerPoint presentation or Word document okay I'm going to make another type of chart now so if you're asked to make a chart you show percent to just you normally would use a pie chart so I'm going to create a chart showing the values as percentage so for iPhone 6 we want to compare the sales from January going all the way to June and we want to show the values as a percentage so now we're going to make a pie chart okay so as you can see what the numbers here so what we need to do first of all is right-click select the data and then we want to click on edit and highlight the mumps okay now what I'm going to do I'm going to include in increase the January sales so it's got a line share it's got a majority share of the pie chart okay so as you can see now January sales is the hundred is taking the majority now to add labels through a pie chart you can right click you can click on select or actually let me do that again if I click on a pie chart if you click on add data labels and then if you right click again and select format data labels maybe you can select a percentage as you can see if you click on category name maybe the month as well and sometimes what you may have to do is pull out the biggest chunk so if you double click you can pull out the biggest chunk and again if you want to color in you can color in a pie chart or you can make into a grayscale if you want to do as well okay so the pie chart is normally used when we want to show percentages okay a bar chart would be used normally when we having to compare two sets of values so as you can see this bar has now been increased to 100 because I've changed a value here we change it back to or change it to a lower value you'll see the pie chart has been decreased okay so example is now not the biggest chunk is April so we can double click and we can drag this out if necessary okay so if I go back to my powerpoint okay so let's look look at the use of formulas so formulas can be typed into a formula bar to work out simple calculations so a formula would begin with the equals sign you need to refer to the cell reference before you write your formula okay you can either type the cell reference or click on a cell so you can create simple formulas we can use the add to add the star to multiply the minus sign to subtract and the slash to divide even if you update the numbers in the cell the formula will automatically work out a new value so if I come to here okay let's look at these formulas here so I've done the first one already so it's basically this cell here which is C 15 plus C 16 okay so let's do this one here so you start off equals and then I can have a type in f15 or I can click on f15 and then minus this cell here which is an f-16 okay to multiply again we select the cell or you can type it in and I need to look on star and I multiply Division of donut for some is multi again so equal this cell divided by five okay and I'm plus again I'll quickly do it plus this cell and finally let's multiply this cell by seven now if you click let's say if I click here let's show you this one if you double click on a formula it shows you the cells which have been used and you can see C 15 is blue and outline and this cell is blue C 16 is green and the outline of this cell is green so basically tells you by color as well which cell you're referring to okay so that's using simple formulas okay now this question came up in a recent examination November 2017 we had to use a formula to copy the value held in another cell so we had to replicate a formula for each destination so in this example the destination was shown here and what I did was I type in equals and then enabled the cell that contains the destination so we basically had to use a formula to copy the value held in b4 so the formula would be equal and then the name of this or the cell reference okay so values can be replicated from another cell by typing in equals and NSL reference just before I go back to here just let me go back to the form as I just forgot to mention because we have two formulas in place now if I was changes value to 2 for example automatically the formless would change or work out the new values you know when to enter a different calculation every time if I wanted to divide 200 by 5 it gives me the updated answers that's the advantage of using formulas because you don't have to change the calculation every time if it was done manually the formula will automatically work out the new value ok so let me do this example let me replicate the contents of this cell range here using a formula so we're not going to copy and paste if I click on equal and then click on Samsung which is in n3 and press ENTER this will change Samsung s3 as well if I wish you changes to s6 this automatically changes to do whatever the content is appearing in this cell here and then you can repin if you go to the corner and you see the black cross you can use it Auto fold you drag this down and this is relative cell reference in so basically this is relative to this position here so if I were to changes to just a note 3 obviously this here would change and take up the contents of a cell shown here ok shall see simple formulas that's using a former to replicate contents of a cell now we're going to be using that look like using simple functions okay so when you're using functions you normally refer to a range of cells so the cell range of the total for January sales would be this range here okay so it would be this cell this cell this cell in this cell okay the cell range for the total average okay monthly sales would be this range of cells here so we want to work out the average in this cell here so let's look at the different function so equals so basically it's used to calculate the total sum of the numbers within the range so let's say example this number 44 is all the reasonable I'd look together equal average is or we'll calculate the average of the numbers within the range so less example if you type in equal average and any highlight the range it will work out the average okay the equal min function will find a lowest number in the range so as you can see here we have 5 which is the lowest number within this range and then then the max function will find the highest number in a range so the highest number is Samsung as far which is 18 so functions are predefined formulas and are already available in Excel so that each function has a specific role so if I come back to here again okay I'm just going to delete this pie chart let me just put a board is in and it's let me format this really quick quickly okay so if I want to find that total of this range of cells what I need to do is click on equal sum I'll click in a cell here and type an equal sum adds all the numbers in a range of cells so equals sum and then you want to highlight the range of cells you want to add from so it's going to be from b6 to g6 enter and then what we can do we can drag this down to this point here so this is all relative so if I click on here it's looking up in this range here what I'm going to do I'm she want to increase the size like that so you can see it's looking up this range here just hidden behind here okay if I wanted to work out the average so type an equal average okay and then again we highlight the range of cells we want to work out the average from okay so we're looking in this range here so again starts from b6 call on all the way to g6 and then we can drag this done let's say I wanted to format it to show only one decimal place we can right click format cells number one decimal place or no decimal places we can also use functions to do this later which I'll show you to change the decimal place you can also use this option here but normally when you are formatting decimal place you will use the round and round up and round down in industry functions which we'll do later on again I want to work out a total okay sales in January so if I type in equals so highlight the range so from b6 to b9 press ENTER and then I can replicate this by if I go to the corner I can send drag this across so this is relative so if I click on here it's basically relative to this position officers adding up the values from this range here and then equal max we'll find the highest number we turn the largest value in a set of values okay so equal max we want to look up this range here we can close a bracket if we press ENTER automatically closes bracket for you again we can use autofill function if you go to the bottom corner and you can drag to here again it's relative to its position and then men will find the lowest number within the range so if I type in equal min highlights from here so I'm expecting to see five yeah and then we can drag this across as well okay so now we're going to be looking at absolute cell reference since so we looked at relative cell reference in relative is basically according to his position if I go back to here so this is a position of the range is relative to the position of this cell so when I replicated it and moved across so example this one is now relative dispositions its range of cells as well as relative to this cell here I didn't lock anything into place okay so absolute cell referencing is the opposite is where you have to lock something into place so let's look at this example here so when you use autofill to duplicate a formula into cells below okay then you must use absolute cell referencing because what's happening is former here if I as women we are multiplying the price by this percent to share which is in a cell here so the percentage is in E free okay so b6 is relative so when you do drag it down it's going to change from b6 to b7 b8 b9 b10 what we don't want to change is this cell reference here so if I replicate this formula and I don't use abs cell referencing this will change into e4 e5 e6 and e7 is and it's not going to work so we need to use absolute cell referencing to lock the cell into place so absolute cell referencing will lock up the inner particular cell to absolute cell reference you must insert dollar sign before the letter and the number so some keyboards you can use FN and f4 or shift an f4 function to use absolute cell referencing so if I do use absolute cell referencing all the prices would be multiplied by this percentage which is stored here so the price is relative reference since we need when a form is dragged down the form of references to the next price so this will be reference to b7 and so forth any example to the left if you do not use absolute cell referencing on the 25% then a price would be multiplied together content in cell e4 okay so if this is not locked in it's going to multiply against staff discount which is not a value okay so let me show you an example okay let me zoom in so let me just first of all formatted this into currency so what I want to do I want to work out the discount so to work out a discount I need to multiply at this price by 10% which is held in a cell here okay and c3 I'm purposely going to make a mistake and you'll see what happens so the first cell has worked if I double click it references to b6 and c3 however if I drag this down we have an issue it says value why do we have value so the first part is in the right place is looking at this price now is relative to this position however this cell here has moved on to this cell here which contains text so you can't multiply this to this value so that's why it's give you this error message so what you should do to resolve this issue is lock in a 10% and let me show you again click on B 6 multiplied by 10% so like I said normally on some keyboards you can type in F F n a net full shift in F 4 because I'm recording for some reason it doesn't allow me so you need to put a dollar sign before the letter and then the number okay so now if I replicate this formula down by dragging down on a dark black square and bottom right corner you see it works so if I click on any cell so now b9 is relative to this price here however see free which contains 10% has been locked into place so let me show you again so if I want to work on a staff discount which is not 25% you multiply this price by this and value here if I don't lock into places this will move down which we don't want to we want to keep that percentage in the same place like what dollar sign before the e and before the number 3 and then we can drag this down ok so again the price is relative to this position here okay however the 25% has been locked into place so look at the color so this refers to this cell here which has a blue border and this refers to the cell here which is the green border so that's absolute cell referencing ok we're now we're going to be using our looking at using counts and counts a ok so if you use K equal count this function will count the cells within the range that contains only numbers here so in this example here we have a range of cells okay and we have 1 2 3 4 5 6 numbers within this range so you only use cons when you count in numbers only ok so if I go to the example so if I want to count the numbers I use count so if I click on equal count counts the number of cells in a range that contains numbers so if I highlight this range here so C 3 2 C 14 and close we should see 1 2 3 4 5 6 if I type in a number number that should go to 7 if I type in a letter it will stay still stay 7 because we're only counting numbers ok now the next function is the counts a ok so this function will count all the cells within a range that are not empty so basically counts a cell which contains letters numbers combination of letters or numbers symbols anything so if the cell is empty it will not count so if I go back to here we want to use count a here because we're not counting numbers anymore okay so if I type in counts a count the number of cells in a range that are not empty so if I highlight it this range here whoops let me do that again equal count AIDS if we double click to open a bracket and then you highlight the range and you can close the bracket so I expect c12 so 1 2 3 4 5 6 7 8 9 10 11 12 now if I delete a nationality then you should see it goes down to 11 if I type in a number it still counts a number if I type in a – okay still comes out okay so it's basically counting a cell unless it's empty okay so let's count the fruit so again we can use count a okay because we can't in the cells which contains Texas values and then we can close the brackets so within the brackets you include the range of cells you want to add or account from okay now we're going to be using counts if okay so the countif function has two parts the range and then the criteria so this function will count the number of cells which contains a specific criteria from the range so for example we need to count the Prada bragg's bags from this range here so we have a range of handbags and we're only looking to count Prada so as you can see we've got one two bags for Prada okay so we're going to use a count if sometimes you may have to count all the cells apart from a criteria that's the second part so if I come back to here this example let's do this here so what we want to do we have a specific criteria I want to count in this range all the nationalities which are Qatari so there's two ways of doing this so if I use count if because we're looking for criteria count the number of cells within the range that meets a given condition so count if then you've got two parts so if I click up here the first part is the range so I know like criterias cuz its Qatari so I need to first of all highlight the range and the range will normally include the criteria and then if I type in comma I can add with writing speech mark marks qatari okay so this will work and it should give me one two three four that's the answer okay all I can reference to ourselves so if I click on counts if okay the range is again where you see the criteria so I'll see French within this range here comment and this time I'm going to click on itself as a reference okay and what I'm expecting to see is one two three okay and I'll quickly do Spanish as well so again we counted in four criteria we highlight the range of cells we need to look into , – navin criteria to always follow the formula so now the criterias become bold and it can highlight Spanish so we've got 1 2 which matches up here ok so the second example is sometimes you may have to counter all the cells apart from certain criteria in this example we want to count all the brands apart from Marc Jacobs so what you do you do you type an equal counted highlight the range and then we have to use they're less than modern sign as a not than Marc Jacobs and it has to be within the speech marks ok so now we can't reference to a cell so if you don't want to count something we need to use this information here so in speech marks less than modern then the criteria okay so I forgot to here let's say we want to count everything apart from apples or not Apple so if I type in equal count if we still use an encounter if because we have a criteria we highlight the range of cells comma okay then in speech marks less than more than Apple and then close the speech mode close the brackets before I press ENTER one expecting to see is we expect to see the number of cells which does not contain apples so that would be one two three four five six seven okay so if I delete this cell here and type in so now we have eight cells which do not contain Apple okay so if you are doing a count see if you can either write the criteria within a speech mark or you can refer to a range of cells okay what you can also do is if I look at this example here so let's say we're looking for the year so year which is greater than or equal to 2012 so again we can type an equal count if you can highlight the range hammer and then for the criteria within a speech marks you can say I've been greater than or equal to and then whatever the criteria is going to be so in this example 2012 so okay let's see if it was on your example actually I did that one okay so let's just summarize so equal count this will count a range of cells which only includes numbers so it will not count letters it would only count cells which contains numbers count a okay and we just delete that don't know why I've got that so equal counts a this will count all of cells which are not empty so counter able basically counts cells which contains letters and numbers or symbols okay counts F will count a range of cells which includes a specific criteria you can also use the not function so if you want to count everything apart from something and again we use count if because we're looking at specific criteria if you using a not function as we count everything apart from that specific criteria so here we have some examples don't want to count all the books I can use counter a the book names you'll normally be in texts if I want to count the number of sales from virgin remember we're not adding we want to count so it's a count if and then criteria can be a version okay sales from Amazon it's the same thing but with Carlotta deranged well instead of referencing or writing the name in a speech market we can reference to a cell and then this example will count everything apart from hmm V from the same range of cells so when you reference to a criteria you can either write the criteria in speech marks or you can reference to a cell that contains the criteria okay so how do you know when to use a count function in an exam so sometimes they'll tell you clearly so here it says use a counter if function is cell c7 to count the number of products made by the supplier with a code that matches the contents of cell b7 okay so in this example here it tells you to use a counter if the counter number products okay so that's a range and then we have a criteria which is in a number column okay so this is where we're counting with the criteria which is in a number column so this would be an example of using and accounts F okay this example here use a function account the number of trips to a destination where the D code column matches contents of cell b2 okay again this will be account if because we have a range of cells which has to match up to the criteria which will be in an another column okay in this question here in cell b11 use a function account the number of item codes now you'd have to look at what is in item codes if the item codes are made up just on numbers then you can use equal count if the item codes are made up of letters and numbers you can use equal count a there's no other criteria to look at so in the other cells or questions we had to match the contents to a specific criteria and here we're just counting all of the item codes so the only thing you need to work out is does the item code contain letters and numbers numbers text so if it contains text values then use counter a if it contains only numbers and then you can use counts okay we're going to be now looking and doing this first example so I'll come to me excel here so the question is we're going to be looking at question five use account function so we can use account account a comes if so we need to work out which one in cell K seven which is a cell here to count the number of products made by the supplier of assisi code that matches the contents of cell J seven so basically the contents of this cell has to match your preferred range here so because we have a criteria in a number cell we're going to use accounts if function so by typing counts if now the range is here in supplier okay now if a reader the question okay this function must include both absolute and relative referencing because I'm going to be replicating this function again down I don't want this supply code to move this supply code has to stay in the same position so I need to put a dollar signs in so again you can either use a shortcut keys in your keyboard FN f4 or Shift + f4 so that's the range that's going to be locked into one place comma and the CC code has to match with the supplier so then if I click on here this will be relative referencing because when I drag it down if I'm in this cell we're going to look in this cell for a CC code to see how many times it appears here and then it's going to counter that value in the close the bracket so a must appear six times in this range here okay if a drag replicate this formula down you can see the most popular code is K which appears ten times within this range here okay so that was that question so that was a counter if okay the next question is we want to count a number of trips to a destination again we need to use another column which matches for the contents of cell b2 so now thus this example here okay so slightly different but let's have a look this question here in cell d3 use a function account the number trips the destination where the decode okay it's range here column matches the contents of cell and j-3 or saris matches the contents of cell c3 IJ okay so basically this code will match up to this column range here okay so every time we match this up it's going to count the value so because again we have a criteria we're going to use accounts if okay so the range is basically we want to count the number of trips the destination where the value in decode so that's the range okay again we're going to be using absolute cell reference in here because we don't want for this range to move in any different position so I'm going to type in a dollar sign okay and basically the column matches this contents of cell three so cell c3 we have BR a so anytime you see BR a is going to basically count that value every time it pairs in that range so ever press ENTER so BR a must appear 13 times IND a purse 14 and us a appears eight times in this range here okay now the last question was we wanted to count the number of items so if I go back to the question so the question was in cell b11 use a function account the number of item codes in this invoice you know so if we look at the X so the first thing I said is we don't have a criteria so we know we're not going to use accounts if okay so the item calls it down here now I said to react we first would have to identify if the item codes are a combination of letters and numbers numbers or texts so as you can see here the item codes are a combination of letters and numbers so we're going to have to use a count aid function okay basically counting the number of cells in a range that are not empty so one two three four five so we've got five different item codes if I press ENTER I should expect to see five so that's the correct answer okay so make sure if you are doing these questions you are underlining the key parts of the question so you count or your underlined basically what you count in okay made by the supplier and then also if there's a matching cell you identify which cell would be used as a criteria so in this case it was this cell here okay looking in this range here this example here we were matching the contents of this cell to the criteria here

7 Comments

  1. Abdulrauf Abdullahi said:

    Thank you sir for helping me with the igcse ICT my CIE exam is next month and your videos are very helpful

    June 26, 2019
    Reply
  2. Sushila Yadav said:

    Can u pls help.me out I dont know anything about excel and I have my exams day after tomorrow pls help me out

    June 26, 2019
    Reply
  3. Karen Yasser said:

    Those videos really help us like a lot

    June 26, 2019
    Reply
  4. Jahed Alam said:

    Thanks your video helped me a lot Sir.Keep making those kind of video to help us.

    June 26, 2019
    Reply
  5. Amadeus Malonje said:

    Hey ahmad, your videos are extremely helpful, but could you please do videos on A2 9296 ICT past papers, I really need your help with them. My exams are on may 1 and 3rd

    June 26, 2019
    Reply
  6. Phumin Abdul Hameed said:

    Hello sir,
    I know its hard for you maintain the channel and do your work.
    But I am having my exams tomorrow and these overviews help me a ton.
    So this is a kind request, can you please finish the overviews for data analysis today.
    Hope you understand my concern
    Thank you so much

    June 26, 2019
    Reply
  7. lilnimo said:

    Sir, i see that your online and are uploading videos. Could you please do us a favor and do the past paper of A2 9626 March 2017 Paper 4? my CIE exam is next week and i dont understand the normilization part of this? please help us//

    June 26, 2019
    Reply

Leave a Reply

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