How to Calculate Quartiles Using Excel: =QUARTILE.INC and =QUARTILE.EXC

how to find quartiles in Excel using the quartile function using the quartile function in Excel is very simple let's take a look at this data set I have with student grades and column B suppose I want to calculate q1 q2 and q3 the first second and third quartiles Excel has a quartile function to do this and it's very easy to use they have two types of quartile functions one is quartile diancie ANC means inclusive and then they have portal dot exe meaning exclusive so let's see how to use these functions and what the difference is between dot I NC and exe let's use the great data I have here in column B I've made a little table here to show the results of using the quartile function and so we can compare the results between the dot exe and the dot IMC let's start with quartile 1 for the grades first I place my cursor in east sevens since that's where I want the result for the first quartile to be now I type in the function remember functions always start with an equal sign so equal then Q you a r TI l e and as i'm typing you can see the choices for quartiles are to use dot exe and dot i NC let's start by using the quartile dot int function so I type in dot IMC open parenthesis and now it wants us to enter the array of numbers comma and the quartile so the array of numbers is in column B the grades so I select the entire column of grades by putting my cursor in the first cell with gray data b1 click into that cell and then select the entire column by pressing control shift and the down key you can also select the data by clicking and dragging down the entire column so now I have B 1 through B 51 selected as the array of grades by the way B 1 is just a label so the quartile function will ignore it sometimes Excel doesn't like when you select non numeric values but the quartile function doesn't seem to mind okay so now we have B 1 through B 51 selected now type comma and then the quartile you want I'm going to type in 1 for quartile 1 the first quartile now I close the parentheses but before I press ENTER I want to fix the cell references so that they are absolute cell references rather than relative cell references I do this by selecting the range b1 to be 51 you can see it is highlighted now in blue and then I hold down the function key the key that says FN is the function key and then I press f4 this will place a dollar sign before the row and column references so it now reads dollar sign B dollar sign one dollar sign B dollar sign 51 okay now I'm ready to press enter and we get seventy one point two five now I want to calculate q2 and q3 I could type the function over again into the cells e8 and e9 and that would work but since I fixed the cell references I can simply drag down to autofill the cells so place your cursor in the lower right hand corner of cell e7 and drag it down now we get seventy one point two five for q2 and q3 which is obviously not correct because that's for quartile one so let's change the number in the function from portal one to quartile 2 click in to e8 and you can see it says B 1 through B 51 which is correct and then comma and the number one so we need to change that to a two since we want q2 quartile two so let's change the 1 to a 2 and press return and we get seventy seven point five for q2 now let's do the same for q3 click into e9 and then change the one to a three for q3 press enter and we get 87 so now we have q1 q2 and q3 now for the min and the max you can type in a zero and a four let's start with the max the largest number in theory that would be the fourth quartile so let's drag down the formula in cell b9 and change the 3 here to a 4 and we get 99 so 99 is the largest grade and finally let's go up to where we want the smallest number that would be the min we can autofill in to e 6 from a 7 so let's drag the autofill handle up yes that works then we need to change the portal from a 1 to a 0 for the minimum and press enter and we get 55 so 55 is the smallest number or the min now what about the quartile dot exe function what is the difference between that and the int function well first off you cannot get the minimum and the maximum from it you can only put in quartiles one two and three so let's see what happens let's click into f7 and type equal portal dot exe then open parentheses and you can see it is asking for array and portal so let's put in the array which are the grades and column B so I go back to cell b1 click and then press control shift and the down key to select the entire column of data B 1 through B 51 is highlighted in blue so it's selected so I can actually fix the cell references now or I could do it before I press return like I did before let's do it now press the function key marked FN and then press f4 and you will see the dollar signs place before the letters and numbers and now these are absolute cell references rather than relative cell references so I will be able to copy the formula down without the numbers changing now type a comma and now you can see the choices are just for quartiles one two and three it does not give you 0 and 4 as choices for min and Max like the dot i NC function does so let's type in 1 then close parenthesis now I'm ready to press enter and we get 70 point 5 0 which you can see is different from the number we got using dot ANC or inclusive function now let's do the same for the median q2 let's copy the formula down to cells f8 and f9 now let's click into f8 and then change the portal from a1 to a2 press enter and we get 70 7.5 now let's do that for f9 let's change the quartile from a1 to a3 so we get the third quartile and press enter and we get 87 point two five the difference between the two is that portal I NC is inclusive so it calculates the quartiles on a percentile range of 0 to 1 inclusive of 0 and 1 the dot exe function is exclusive it bases its calculation on a percentile of 0 to 1 exclusive I hope this helps you calculate quartiles and I hope you learned something

Channel: Learn Something

Views: 59,322

Rating: 4.6842103 out of 5

Keywords: Calculating Quartiles, How to Calculate Quartiles, Quartiles using excel, Quatiles in Excel, Excel's Quartile function, =Quartiles, =Quartiles.inc, =Quartiles.exc, Excel function, Quartile function in excel, how to use the quartile function in excel

Id: UseEZnBYHEY

Length: 7min 52sec (472 seconds)

Published: Thu Sep 26 2019