Speaker 1: Hi everyone, welcome to this tutorial on Microsoft Excel. Let's quickly see some more examples of doing data analysis using Excel. And for that we can use some inbuilt add-ins which can be added to our Excel sheet. So for example if you would want to do a descriptive analysis on your data, say for example getting your descriptive statistics such as your mean, median, mode and so on. So we can do that and we can use Excel for it. So for example you can, if you are given some data, say I have temperature, price of ice cream, units sold and I would want to have descriptive statistics on this. What I can do is I can click on file and here in file you can click on options and within options click on add-ins. Now within add-ins you have Excel add-ins which is selected here. So click on say go for example and that shows what add-ins are available and you can choose which ones are you interested in. So for example I have chosen analysis tool pack and solver add-in and click on OK. Now that basically should add more options to your Excel. So if you click on data, so here you see data analysis and solver and this is what we would want to use to get our descriptive statistics for these three columns. So for example let's say temperature or you can even give the names later once you get your descriptive statistics. So for example let's go for data analysis and here it says what are you interested in. There is a two factor with replication. You have correlation, covariance, descriptive statistics, you have histogram. So let's click on descriptive statistics, click on OK. Now this one basically asks your input range. So while your cursor is blinking here, also it is said grouped by. So let's give it a range. So for example I will say temperature. Now if I do this and I have selected the heading, just look at that and now you need an output range. So let's just select this and then you can have your cursor blinking here. Let's select say fields here and this is where I would want the output. Now it also said what options do you want. So it has output range. We can then select summary statistics, confidence level. So I will say summary statistics is what I am interested in, say OK. And this says input range contains non-numeric data. Now why is that? Because we chose temperature, the heading also. So click on OK and here we will alter the range. So this one is our range should be only the values, numeric values on which we would want the descriptive statistics. We have output range already selected. We have summary statistics and now you can click on OK and that basically gives your descriptive statistics for temperature. So here I could basically give in a value for this. So I can say temperature and that's my descriptive statistics for temperature. Might be I can just do some formatting and that's it. So that gives me descriptive statistics for the values here. Now similarly we can do it for price of ice cream. So what we need is we need to basically go for data, data analysis, descriptive statistics, say OK. Now you need to give a range. So here I will change my range to these values. Output range is already selected. Now we are interested in summary statistics, click on OK. And this says output range will overwrite existing data. Press OK to overwrite data in range. I will say cancel. No, that's not what we want to do. We need to give a new range. So let's select our new range which is here and now click on OK. So now we get the values which is for your price of ice cream. So again we can basically select this and say price of ice cream and we got our descriptive statistics for price of ice cream and like we did earlier I can select this. I can basically do a merge and center and that gives me descriptive statistics for price of ice cream. So we could also basically change this. Now I can go into data and I can go into data analysis, descriptive statistics. So we know that we had selected this B2 to B8 and this one which is H6 to H19 we would want to shift it might be two columns up. So might be I can just say H5 and I can manually change it to H17 and let's say OK. And we will basically get this and I can get rid of this. So I can have it in the same range. So similarly so this one will have to be renamed and I can basically say price of ice cream and that's basically my descriptive statistics for my price of ice cream. And similarly we can do it for the third column which is units sold. So we would want to have this. Now let's see we can click on data. We can click on data analysis, descriptive statistics. So we need to give the range correctly. So this time our range changes to units sold. Now we can also say labels in first row OK. If we were selecting the heading. So let's do it in this way. So in my range in my range let me empty this. I can basically select this which we know has non-numeric data in the first row. Say for example I'll say labels in first row. I'm interested in summary statistics and this range will now have to be changed from H to basically something like J. So let's say J and let's select these values. So that should take care of things. And now you see you have your units sold. You did not have to manually rename it and you have basically got the descriptive statistics. So this is how you can simply perform analysis using data analysis here. You can basically get your descriptive statistics for your columns and then you can do whatever needed formatting you need to basically make your data look in a good way. Now let's look at one more example of data analysis where we may want to look at the frequency of values or frequency of values occurring in a range of values. So for example if you have been given temperatures you have been given some bins where you would want to identify how many values fall into the range of 0 to 20, 20 to 30, 30 to 40, 40 to 50 and the easiest way to do that would be creating histogram. Now histogram is usually used for data analysis where you would want to look at different variables or say features for example temperature is one such feature might be there might be one more variable or feature such as sale of ice cream and you would want to see if the increase or decrease in temperature affects the increase or decrease in sale of ice cream. Might be sale of ice cream is a response based on temperature so it depends. So sometimes you may want to find a relationship between two variables whether they are positively or negatively related or you would want to do different kind of analysis and in certain cases we may want to first do analysis on one single variable look at the frequency of values. Might be also look at the defects and for which we can use something like Pareto chart. So we can go for histogram and that basically gives us the frequency of values. Now how do we do that? So we have already added the add-in which is data analysis earlier. So we can just use the same thing again. Here we would want to create a histogram so let's say OK. Now I have already selected input range so if you see here my input range is temperature which is also with the headings and I have bin range which is basically the range of values. So for example I can select this and that's my bin range. I am selecting or the option labels because I'm using the first row which has the headings such as temperature and bins. Now we need to give an output range. So for example let's say I would want my data here and that becomes my output range. So you can have a sorted histogram or basically a Pareto chart. So if that's what you're interested in looking at the frequencies for your different ranges and here I'm also selecting chart output because I would want to have a visual histogram which gives us the frequency and it's as simple as this. Just click on OK and now you get your bins. So it basically tells you frequency of values which is basically 20 but that does not mean it is only talking about the values 20. It is basically talking as a range of 0 to 20. So we have 0 to 20 that is 2 so we can basically say there is 120 here with the 20 being the maximum value and then there is one more 20. So that's your 0 to 20 then you have 20 to 30 which shows three values. So might be in that case I can say 26 is one thing then I can say 30 that's the second one and then basically I can look at 22. So basically this one does not select 20 as the lower range but it basically selects 30 as the higher range. So I do see 20 to 30 there are three entries. Similarly we can see values of 40 and 50 and since we have selected Pareto or sorted histogram that shows in a descending order what is the highest frequency of values within a particular range. So that shows me highest frequency is 5 and then you have 3 and 3 and then 2. So this is how we can create a histogram and we can perform an analysis on a single variable. Now as discussed earlier as I said sometimes we may be interested in finding out the correlation between different variables such as say here we have temperature, price of ice cream and units sold and we may want to find out the correlation between one variable to another variable or we would want to find out the relationship between variables. Are they linearly related, are they positively related, negatively related and so on and for that we can use the correlation of your data analysis add-in. So for example you want to find out correlation of temperature and units sold and what we can do is we can find out that using a formula. So for example if I search for something like correlation and let's search. So there is a function called correlation which we can use and we can use this to calculate the correlation of temperature and units sold. So for example let's select this and that's the function. So it says give me a first array and a second array. So we are interested in finding out correlation of temperature and units sold. So let's select the range of values for temperature and then I am interested in finding out the correlation of temperature and units sold. So let's select this and that basically gives me a range of values. It gives me the correlation value which is 0.2859. Say OK and that's your value. So similarly we can do it for temperature and price ice cream. So let's go for correlation. So that's the function we are interested in. You need to give a range of values. So here we are interested in temperature and price of ice cream. So let's select temperature and then the second array or list of values is price of ice cream. Let's select that. Let's close our bracket and here we have the correlation value of temperature and the price of ice cream. Similarly you may be interested in finding out temperature and units sold like what we have done earlier. So we can do the same thing based on function. So this is same as correlation of temperature and units sold. So I can get rid of this one. Now how do I do it using the data analysis add in. So for that what we need is we need to go into data. We need to click on data analysis and here you have the option called correlation. Let's select this. Now that basically needs an input range. So we need the range. Now I might be interested in finding out the correlation between temperature and price of ice cream and units sold. So I've selected all the columns here. We will say group by columns. Obviously we need to select labels in first row because that is basically taking care of the first row as heading. Now output range you can just give one simple cell and that's where your data will start from or you can give a new workbook. So click on OK and that basically gives you a that basically gives you a correlation of your different variables and what are the values and we can check these values based on the values what we have here. So we have basically temperature and price of ice cream and that basically shows me 0.96149. You have temperature and units sold. So you have 0.2859. Now you can also look at units sold and say for example price of ice cream. You can look at these particular values. So if I would be interested in finding out what is the relationship between these variables I can easily find using correlation. So I could be basically writing in a formula here and selecting what are the cells. So here we were selecting A2 and C. Here we were selecting A and B. Now might be I'm interested in price of ice cream and units sold and if that's what I'm interested in then I will give a range of B2 to B8, C2 to C8 and similarly you can get your analysis or correlation values. So it's very simple in Excel and you can use either the data analysis tab and get your correlation or you can use formulas and do that. Now one more important part of data analysis is doing your sampling. Now sampling could be periodic sampling or random sampling. So sometimes you may want to look at a variable and you may want to get some values based on periodic data. That means might be I'm interested in range of values, I'm interested in seeing a sample of values for a particular period which could be basically a range of values or you could just do a random sampling. So for example if I go for periodic sampling so out of these values which I see here might be I want to see say periodic sampling that is a frequency of two values how many times we have these values occurring here or I would go for random sampling. So basically randomly I would want to pick up say three temperature values. Now how do I do it? So for example here I have seven values. Now if I go for periodic sampling the sample or periodic sample value which I need to give has to be lesser than the total input values. So for example we can do this let's go in here and let's go for data analysis. So we can go for sampling here click on OK and that needs a range of values. So we will select A2 to A8. Now I could have selected all the values for this one temperature and in that case I can give labels which is going to take care of the first row. Now here we can go for number of samples which we are interested in or giving a period. So let's go for period and say for example I have seven values. So what if I select five. So for example if I say five that means I could just get one value. So basically when I'm saying five out of seven. So that's just giving me out of five I want one value. So I can then just give an output range. So here I can basically select this cell I'll say OK and now you see it just shows me one value. So out of the first range that is I have said five it has given me the fifth value that's your periodic sampling. So for example we want more values. So let's reduce this period to might be two which basically gives me every second value. So I can basically say for example two and say OK and then say OK. So that shows me 26 then you have 35 then you have 40 and then well this one does not have any more values so that's your periodic sampling. Now if you go for random sampling that's basically randomly picking up values and you can choose how many values you want. So go for data analysis go for sampling I'll go for number of samples how many you want. So for example out of seven values randomly I want three values and I can just give this say OK and then we will do a cancel because we need to change the range. So let's select this and say OK and that gives me random three values from this values of temperature. So we can use Excel to do a simple sampling and we can choose whether we would want to go for periodic sampling or random sampling. Thank you and keep learning.