MS Excel - Statistical Functions

in this video we'll be looking for statistical functions like sum if count if average if and some more advanced functions like sum if countifs average ifs let's see those one by one on the screen if you see I have some data like name days and numbers these are some data which are written on this data I'll try to get a sum based on some criteria so you can see here what is the criterion some I want some of numbers which are less than 50 so these are the numbers I want some of those numbers which are less than 50 so I can just write a function over here that is some if so I'll just type here sum if bracket open and if you see here there's a range criteria and some range range is this one that's a range criteria is less than 50 in double quotes whereas these some range which is given in the brackets that means it's an optional so I can ignore it right now so I will select here that is the range comma and in double quotes I can just write here 50 and I will press Enter the moment I press Enter I will get the answer S which is less than 50 so 40 plus 30 plus 20 plus 10 that comes as 100 as soon as I press Enter I will get the answer as 100 similarly I have one more criteria I want sum of numbers which are which fall against Monday so let's see what are the numbers so you can see Monday 10:00 Monday 80 and Monday 90 purposely I've written those and have colored them so that easy to understand let's see how to get the answer for this if you observe the sum is in this range and the criteria is Monday but the criteria falls in this range so I have three parameters now that is some range that is the criteria range and third one is criteria that is Monday so let's go with the function I'll just keep my cursor in the cell and I will type the function as equal to some if like and open if you see carefully some range is numbers comma criteria is nothing but double quotes Monday and again I have a range that is the Kriger range so I'll type here this is my criteria range comma in double quotes I will write down here Monday and third one is the some range so I'll select this some range from this option and I will press Enter the moment I press ENTER I am expecting the answer as 10 80 90 which comes to 180 so I will get the answers 180 right similarly I have a function known as count if let's see that I want count of numbers which are less than 50 so in this range there are only four numbers which are less than 50 which is 40 30 20 and 10 let's see that so I'll keep my cursor in this cell and type a function equal to count if bracket open it's similar to what we have seen for the sum if that is the range comma criteria so my range is this one comma and the criteria is less than 50 enter so the answer my expecting is 4 which is absolutely correct next I have something known as average of numbers which are less than 50 so again I will use the same function or the same parameter so where the function will be average if well type equal to average if that is the range comma criteria so I will select the range if you see here that's the range comma criteria and the criteria I will write here less than 50 and I will press Enter so average is less than 50 is 100 divided by 4 so I have got the answer as 25 we can see a 25 I'll give you some other method which will be same function but it will be easy to understand right now if I want to change the criteria which is less than 50 it is difficult so what I'll do is I will try to fetch this numbers less than 50 from some other cell let's say from this cell so if I change the criteria in this cell the answer will be changed so let's see that function so I will write here a function as less than 40 less than 50 and here I will type a function equal to average if that is the range so I will select the range put a comma and my criteria is in this cell so and I will press Enter so let's see what is less than 50 I've got the answer as 25 now if I go and change this to less than 80 NL press Enter so I have changed the criteria here but the answer is change in this cell so this way it is more beneficial to fetch the criteria or giving a name to arrange so that way it will be more beneficial next topic which you have is advanced some ifs count if an average of let's see that one by one what is that it basically is if you have criteria which is more than one that time you have to use this functions submits countifs and averages let's see that so on the right hand side I have written some data if you see on the screen the same data which I've written but now the criterias advanced we just change now only the criteria was only Monday but I have added some more criteria let's see what is that I want some of numbers belonging to Monday and John in that formula it was only Monday but now it is as Monday and John so the criteria is Monday in this column John criteria is in this column okay and I want some of these numbers so you can understand how complex the formula will become let's see here so I will type a function same sum ifs but it is some ifs with multiple condition so I will try to explain you that is my sum range criteria range one criteria one criteria range two and criteria two so let's see now so I will type here equal to sum ifs bracket open first one it says as some range so I will select this sum ridge comma first one again it says as criteria range so I will select this criteria this is my first criteria range comma and I will put here Monday either I can type using double quotes Monday or else I can just select the text from here itself it's one and the same next one is comma again it says criteria - which is this range so criteria range - I will put a comma and criteria range - I will select here and next whis once I put comma it will ask me again criteria - so I will put a comma and I will select this area so now I'm expecting answer those numbers which fall against John and Monday that is 10 John and Monday that is 90 so total answer I'm expecting is 100 so I will just close your bracket and enter so you can see the answer is 100 so this way you can find out these sum if for multiple criterias next we have the countif function that is let's see what we have now I want count of those numbers in which I have the common criteria that is John and Monday so I have John and Monday once John and Monday twice so I have two numbers so how do we how do I calculate that countifs using that so I'll use equal to countifs bracket open now if you see it is asking me for criteria range 1 comma criteria 1 comma criteria range two criteria - so right now I have criteria range one is this criteria range 1 comma and what is my criteria in this column that is Monday so I will select Monday you can see it is selected comma and is asking for criteria range - so I will select here range - comma in that range I am looking for John so where is the John here it is here so right now it will throw me an answer there are two times where both of them have occurred so I'll just close the bracket and press Enter now you see the answer which is displayed here as - okay so you can keep on adding multiple conditions till n number of times so criteria range 10 20 30 as many as you require similarly I have one more function here average of numbers belonging to Monday and John so there are Monday and John belongs to 10:00 Monday John 90 so I want their average let's verbally calculate 10 + 90 100 divided by 2 the average should be 50 so how do I calculate using a function it's a very small table so I can verbally calculate imagine the data is very huge so that time I have to use this function so equal to average ifs and it will ask me for average range to calculate an average I need to select this range that is average range , it will ask me what is your first criteria range so my first criteria ranges this option that is my first criteria range , in that range what is my exact criteria so that is my exact criteria that is Monday , again it will ask me for the second criteria range and second criteria so I will put a comma select the second criteria range and put a comma and that is my second criteria and that's it so once I press ENTER I am expecting the answer is 50 always remember here yeah the answer is 50 which is absolutely correct so this way you can use some ifs countifs and averages so in this video we have seen all the advanced statistical functions some if countifs and averages that's all

Channel: Tutorials Point (India) Ltd.

Views: 306,914

Keywords: MS Excel Statistical Functions, MS Excel, Excel, Microsoft, MS

Id: hKrFDLC5jxo

Length: 9min 34sec (574 seconds)

Published: Wed Jul 13 2016