Full Project in Excel | Excel Tutorials for Beginners | student Grade Analysis

Welcome to the Channel!
Full Project in Excel | Excel Tutorials for Beginners | student Grade Analysis
We will find the Minimum score, Maximum score, Average score[mean], Quartile 1, Quartile 3, IQR[Inter Quartile Range], Outliers, Standard Deviations, Draw the Box Plots, customize the Box plots Charts, compare the Box plot charts to analyzed data, calculate the z-scores, and provide Inferences for the Research question.
Learn Data Analysis using Excel to Analyze these recent Trends

#DataScience,#SpreadsheetGeek,#ExcelForBusiness,#ExcelReports,#ExcelGraphs
#ExcelAnalytics,#Office365,#MicrosoftOffice,#SpreadsheetManagement
#FinancialModelling,#ExcelTemplates,#ExcelMacro
#DataEntry,#ExcelSkills,#SpreadsheetTips,#ExcelBasics,#AdvancedExcel
#ExcelShortcuts,#ExcelFormulation,#ExcelAutomation,#ExcelSolver
#ExcelLookup,#ExcelTraining,#ExcelWorkbook,#ExcelWorksheet,#ExcelHelp,
#health,#fitness,#technology,#travel,#food,#fashion,#beauty,#lifestyle
#music,#art,#photography,#nature,#wellness,#education
#business,#entrepreneur,#marketing,#finance,#investing
#realestate,#sports,#gaming,#diy,#home,#gardening,#recipes
#parenting,#pets,#motivation,#inspiration,#mindfulness,#mentalhealth
#selfcare,#humor,#news,#politics,#environment,#sustainability
#climatechange,#crypto,#blockchain,#ai,#machinelearning,#datascience
#programming,#software,#design,.

You can use our, ExcelQuickGuide.com, lesson notes, to perform, analyze and study the data from these industries and adopt and implement development and growth Strategies
Health
“Best health tips for everyday living”
“How to maintain a healthy lifestyle”
“Health benefits of regular exercise”
Fitness
“Home workout routines for beginners”
“Best fitness trackers of the year”
“Fitness challenges for weight loss”
Technology
“Latest tech gadgets in 2023”
“Top technology trends to watch”
“Technology tips for efficient work”
Travel
“Best travel destinations on a budget”
“Travel packing essentials”
“Tips for solo traveling”
Food
“Healthy meal prep ideas”
“World cuisines to try at home”
“Foodie guide to [specific location]”
Fashion
“Latest fashion trends for [current year]”
“Sustainable fashion brands”
“Fashion tips for [specific season]”
Beauty
“DIY beauty treatments at home”
“Top skincare routines”
“Makeup hacks for beginners”
Lifestyle
“Balancing work and personal life”
“Minimalist living tips”
“Lifestyle changes for better health”
Music
“New music releases this month”
“Best playlists for relaxing”
“History of [specific genre] music”
Art
“Famous art exhibitions to visit”
“Beginner’s guide to painting”
“Art therapy activities”
Photography
“Photography tips for beginners”
“Best cameras for photography”
“Photography composition techniques”
Nature
“Nature hikes near me”
“Benefits of spending time in nature”
“Wildlife photography tips”
Wellness
“Wellness practices for stress relief”
“Holistic wellness routines”
“Wellness retreats worth visiting”
Education
“Online learning resources”
“Educational apps for kids”
“Scholarship opportunities for [specific field]”
Business
“Starting a small business”
“Business growth strategies”
“Effective business management skills”
Entrepreneur
“Entrepreneurial success stories”
“Tips for young entrepreneurs”
“Innovative business ideas for startups”
Marketing
“Digital marketing trends”
“Social media marketing tips”
“Content marketing strategies”
Finance
“Personal finance management tips”
“Saving for retirement”
“Understanding stock market basics”
Investing
“Investment strategies for beginners”
“Real estate vs. stock market investing”
“Cryptocurrency investment guide”
Real Estate
“Buying your first home”
“Real estate market trends”
“Tips for real estate agents”

Please click the link down below for a full payroll spreadsheet!

▶ SPREADSHEET!-

Follow Me!
▶ YOUTUBE-https://www.youtube.com/@excelquickguide

This is a full fledged PayRoll spreadsheet prepared using Excel
Visit: https://excelquickguide.com

Welcome students in today’s class we will be learning how to perform the data analysis now the data given here is the class performance is of grade 10 and we got four classes here grade 10 a 10 B 10 C 10 D and they have apparently given a test in physics and

Their scores are being recorded now we will have to perform the complete analysis is individually class by class and also we will have to perform an analysis that would give us the best class so we have to judge the best class from this performance so there are two

Important things as I mentioned to you that is the objective so this is the data you can watch this data let me explain to you so grade 10 is given and we got 25 data points that means 20 25 students have taken this particular test and this

Is the data so let me minimize it so that I can squeeze in the total data points so this is the total number of data points 1 to 25 and these are their scores now foremost I would do individual analysis class by class so first I would be taking

Class or in this case it’s going to be grade 10 a so let me just start off I want to find out the minimum score that this class has got so just Place minimum there and then click and enter over here place an equal to sign and enter

Min and select the cell place or press control shift upward Arrow then you you press shift and deselect that heading and then click enter so this would give you the minimum score now probably you can bold that thing out and then Center it and similarly this one you can Bol it

Out and Center it so you can have that clarity now having got that minimum entry let me find out the maximum so this is going to be Max and the formula is equal to Max open the paranthesis click the cell press in control shift and then upward arrow

Press the shift key and deselect that heading and click enter so this is going to give us the maximum value as I always tell you make it Bolder and center the value also make this value Boulder and center the value so you got the maximum and the minimum now now after you have

Got that you need to get the average what is the average score so for that the formula is a you got the average there click on that and then select the cell control shift upload arrow press the shift key and deselect it and click enter so now you got the average score also

And we make it Bolder and we Center it out so we got the minimum score maximum score and the average score now after obtaining this we need to get the coiles so this one is going to be the first coile Formula q a r t Le you can

Just place a parenthesis and mention q1 there so that would give you the cotile you can make it Bolder and how do you find it place an equal to press Q you get the first option select that and then select the data control shift upward Arrow shift

Downward arrow and then come back to the data point and you will have to place a comma and since it’s first code I’ll enter one there that’s how you need to do that if you don’t do that you will get an error so Center that data and now you need to get

Q3 now Q 2 is already got Q2 is the average right so kti Q3 is equal to you can just uh make it Bolder and the formula is equal to Q you get the first option select that select the datar control shift upward arrow deselected and then come back here place

A comma and enter three because it’s quile three and click enter so now you got q1 Q2 Q3 now you need to get the IQR IQR as I’ve already explained to you stands for intertile range so make it Bolder and you Center it so the formula

Is Q3 minus q1 so this value minus q1 so that’s the intertile so make it Bolder and Center it so you got that IQR now after getting IQR you need to spot the outliers so outliers so that’s the outlier number one so how do you get

That for that we need to use this all important formula which is q1 equal to where is q1 q1 minus IQR multiplied with 1.5 so any value that is lesser than this is considered an outlier apparently we don’t have any out layers we don’t have a negative

Outlier I I would say a negative value now there is also a maximum outlier or positive value we need to obtain that so let me change this to two outlier number two this is found using the following formula which is Q3 this Value Plus IQR this value multiplied with 1.5

That’s a formula any value greater than this so in this case there is no chance of having a value greater than this because the maximum is 50 so we don’t have to actually calculate outliers now with this information I’m going to draw the box plot so for that you click on

Insert and go back and select this one click on this it will tell you box and whisker so click on this box and whisker you write click and then you say select data and then over here you say add for the first one you add grade 10 a and

Then for the second icon you select the data points control shift download Arrow all of this thing would be selected click enter and click okay and then okay so so change the name of the chart so in this case this is going to be grade 10

A grade 10 a so that’s going to be the box plot so clearly the box plot will tell us the minimum the minimum is lying between 0 and 10 so clearly it says five that’s fine and then you have the the cotile one the cotile one is

17.5 17.5 is this line this line is 17.5 that’s the first cotile and the average is given by 27.0 27.0 is lying between 20 and 30 so that’s this center line that is the mean and then Q3 the value of Q3 is 37.5 37.5 you see that how beautifully it’s

It’s been plotted so this is between 30 and 40 and close to 40 so that is the value of Q3 and then you got the maximum which is heading towards 50 so the maximum value that you see here let me show you is 48 so clearly it’s plotted

Here close to 50 so there aren’t any out layers if there is an out layer it will be plotted somewhere over here we don’t have an out layer because the maximum Mark is fixed and as I always mentioned to you the first 25% will be located between minimum value

And the q1 and subsequently the next 25 percentage and the next 25% and next 25% so youve got 25% of the data points lying between Q3 and the maximum so this would comprise of that 25% of the data points so every part is actually divided

Into 25% so having got that let me just take this one off I don’t want this one to be here that goes off so you right click and you press the delete key so that is about the box plot for class one now this does not give us any

Information about comparing the classes okay so we need to compare the classes so for that we need to go for Z scores so what I would do is I would just to complete the box plot for grade 10 great 10 C uh I would say 10 B 10 C 10 D so

That has to be done so let me just uh quickly do that so just get the minimum for this so you got q1 Q2 Q3 now we don’t need to look for outliers okay so just we will draw the Box on visco plot for this so just click

On this cell insert go to this icon select the whisker box and whisker plot right click select data click on ADD click on the first cell click on grade 8B click on the second icon and select the data control shift download arrow and click enter and then you click

Okay and click okay so this is the the second box plot I need to place that grade the grade grade 10 B and then I will remove this thing off and then you can right click and you can delete that one out now if you want to compare you can actually take this

And place it side by side and see how the data is behaving right so this is the utmost comparison visual comparisons we can get using uh using the box plot now clearly you can go back to the analysis that we have got the minimum is five the minimum is two

Here the maximum is 48 the maximum is 49 the average is 27.8 the average is 25 5.72 the quiles are different the values of the Q3 are also different now you can get one more data point IQR you can get IQR now IQR is equal to Q3 minus

Q1 okay so that is the IQR intertile range so even the IQR there is a huge difference in the IQR okay so what we can do is we can just highight them all make it Bolder you Center Center the data and you you get that information so now comes the next box

Plot so let me let me get the minimum minimum is equal to Min off select this control shift upward Arrow deselected you get the minimum then get the maximum then you need to get the average you got the average now you need to get q1 that’s for the first cotile and next

Q3 play come back to the data point and place three there and then you can also get IQR this is equal to Q3 minus q1 so that’s the IQR so you can just select all of this make it Bolder and center the data I will draw the box

Plot once I finish this so this is the minimum entry so this is equal to min so you can you can select all of this and make it Bolder and then Center the data items okay that’s got probably you can put all of them in a table the

Completion of the value so clearly you can see the minimum is five minimum is 2 year minimum is one year and minimum is one year also then the maximum is 48 49 47 and 48 so the average you can clearly see the average entries okay so we can

Draw the box plot for the class grade 10 C so just go here Now using the box plot we can actually perform individual summary we can give individual summaries right but Our intention now is to judge the best class so for that what I’m going to

Do is we will have to extract what is called a zcore so how do we calculate the zore there is a Formula I think I will remove this off what is the formula the formula is uh this is the formula that I will be utilizing zco zco is equal to Observation x minus

Mean and this has to be divided by standard deviation so let me just place that xus mean divided by standard deviation STD so that is the formula we will be utilizing so in that case we need to extract the standard deviation for each of these cases so let me go up here and

Study or I would say the grade 10 a that is present here so I will have to have this so for that what I need to do is I need to get the observation from the observation I need to subtract the mean and I need one more column so I

Will insert another column and this column would be X x- mean and I have to raise it to the power of two so this is equal to the observation minus the mean the mean is this value now when you go up over here you need to make sure that you’re

Placing a dollar before b and a dollar after B this is called as absolute referencing if you don’t do that you will get inaccurate results so just click enter of course it’s a negative value don’t worry about that just pull it down and the very next

Column you’re going to square that up so that will be the neutralizing factor so this is equal to this and in Excel we place the carrot sign and we erase it to the power of two that’s the way you square quantities so you click enter this is

Squared and all you need to do is just pull this down so you get the you get the data here now you need to get the total of this okay you need to sum this up so this is equal to the sum of click this control shift upward Arrow deselect it

And click enter so that is the sum now this sum to get the standard deviation we need to divide okay to get the standard deviation we need to actually divide this by nus1 so I’m exactly going to do that so STD standard deviation is equal to so this is equal

To I need to take after dividing I need to take the square root so this is the way the formula works sqrt of this there are 25 entries so I don’t need to place n minus one I just Place 24 so this is the value so the standard deviation is calculated like

This this so you give an enter so you are getting a lot of decimals so I don’t want this many decimals so what I will do I will just go over here over here I will just say rounded so I will utilize this round function equal to

Round and then I’ll take this entry I want only two decimal places so place a comma and give two decimal places so this is the standard deviation now after getting the standard deviation what we need to do is we need to get the zcore so click on click on here give an insert

And this is going to be my zcore so Z score so how do we calculate zcore as I told you this is equal to the observation minus the mean the average entry is over here go back here place a dollar and a dollar after B and complete the parenthesis and you

Divide it by the standard deviation so the standard deviation should also be accompanied with absolute referencing so that is how you get the zcore for the first observation okay so you got that now what is the meaning of this this means this score is 1.2 * the standard deviation below the mean

Because the student has just go 12 but our average is what 27.8 right so definitely the student has not gone towards the accepted average I cannot use the word accepted average the average that’s a best way now having got this what we need to do is all we need

To do is just pull this down that’s all we got zcore for everything that’s a beauty of excel so pull this down you will get zcore for all this what I will do is I will have to do all of this for grade 10 so uh 10B

So let me just move up first and I I’ll go up here right click I will just insert and this is going to be xus mean X min mean and go up over here this is equal to you need to be bit careful when you’re choosing the mean because this

Minus we need to go and choose the mean over here this is the mean that we need to choose then you place in the dollar signs okay so that’s exactly what we do and you close the parenthesis and you click enter we got the we got the me mean pull it

Down and go up over here right click insert and this is going to be x- mean Square so this is equal to click on this and you raise it to the power of two and then pull this down up until here and take a sum of this so this is equal to

Sum of control shift upward Arrow deselected you get the sum now to get the standard deviation SD okay STD is equal to the square root of the summed up value divided by 24 so you got the standard deviation so now you need to round this up so round it this is equal to

Round and I want only two decimal places click on this place two and you got the rounded value so now you need to get the zcore click on this insert so this is going to be my zcore and that is calculated at equal to the x minus the

Mean xar this is the average so make sure to place the dollar before and after close the parenthesis and you divide it by the standard deviation this value and make sure to place the dollar before and after so that’s perfect click enter and pull the Z score down

Okay so the Z score is got so now we need to calculate for GR 10 C so insert this is going to be xus me and this is equal to this observation minus the mean so where is the mean for 10c this is the average and make sure to place the dollar

Sign so that is got click on it and you pull it down so that is done we need to square that value so insert xus mean and then you place the carrot sign and you come over here this is equal to this entry raised to the^ of two two and you pull this

Down pull this down and you take the square of this take I mean I would say take the sum of this sum of all the entry so control shift upward Arrow so you get the sum of all this now you need to get the standard deviation so STD this is equal to

Sqrt this sum is divided by 24 and then you round this up rounded value this is equal to round this entry I need only two decimal places fair enough so now we need to calculate the zcore enter here this is the zcore how do we calculate this is is equal to

Observations minus the average you place the dollar before and after and you divide it by standard deviation where is the standard deviation this is the standard deviation place the dollar before and after you click enter and you are getting this as the Z pull it down okay so far every thing is on

The Perfection scale okay so now let me move on to the grade 10 I want to insert I will insert one more column and I want to calculate xus mean so just calculate x- Mean so this is equal to this one minus my mean so mean is nothing but the average entry go up over here place the dollar sign click enter and you pull it down and again I want to enter a column so this is going to be xus mean and then you

Just so come over here this is equal to this you need to square it up so give an enter okay so pull it down up onto here just extend this a bit and then take the sum so this is equal to sum of this control shift upward Arrow

Shift downward arrow that is a sum now you need to get the standard standard deviation so I will just say STD and then this is equal to sqrt of this divided by the total entry that’s going to be 25 minus 1 that’s going to 24 so that’s a standard deviation okay

Fair enough and you round this off rounded this is equal to round of this entry I need only two decimal places so that’s fair enough so that is got now you need to get the zcore so I will just insert one more column and then write zcore there Z score so the

Formula is observation observation minus the average place the dollar sign there and that has to be divided by the standard deviation so place the dollar sign there so click on it and pull that down so you got the zcore for all of the classes now comes

The all important St step now the all important step is to get the sum of the Z scores okay so you need to get the total sum okay a very important observation is that now if you were to come to the grade 10 a the class here the zcore you

Can clearly see after this you got multiple digits now don’t try to add up all these things if you were to try to add up like the the data point points that you see here you are getting a quantity in this in exponent manner so that would really complicate the

Situation now what you need to do is just go and use the round function so over here what I will do is I will go and enter the round round there open a parenthesis and I will settle with four decimal places okay I hope you’re catching the point

And generate uh manageable entry now this is nothing but netive 1.26 just pull the data down all the way up to number 25 and this would give you a zcore you know if you were to observe the zcore the zcore is netive 0.1 that means it’s close to zero now the interpretation is

That the class performance is close to the average and and the Very fact that you have a negative sign there it means that it is not on the positive side of being average but on the negative end of being average that means the class as a whole is trying to move towards

Achieving this average so that is the interpretation that you need to get here because 0.001 is very close to zero and from zcore a value like that means it is actually on the average but from the left hand uh left hand side it is not from the right side that means it’s

Positive in this case is negative okay let us see the performance of grade 10B what is their part so now what you need to do is go over here and round it off I’m going to utilize the round function so this is the score take the sum let’s see how this class has

Performed so control shift or you can either use control shift over here or you can start from the top control shift download Arrow so you got everything place the parenthesis and click enter so you got zero zcore of zero means on the average so that is the

Inferences that you should have here the class and has an average performance so that means it is not on the negative end nor on the positive end and it also indicates that there is no deviation from the norm in other words if you are talking about standard normal distribution the zcore of zero

Corresponds to the highest point of the curve now if you were to ask me between Class A and Class B which is better definitely we have got the winner here Class B is above Class A okay with that being being said let me just move on

To grade C or I would say grade 10 C so over here I’m going to round this value up pull it down let us see what we are getting I’m going to sum it up so this is equal to sum of control shift downward Arrow okay everything is selected click

Enter this is also average so this is also an average performance and going to class D 10 D so let me just uh round you take the the sum of it control shift and give an enter and you get average so all of these classes have an average performance except for class A

So this is how we actually perform the zcore and we perform the analysis so that would complete our study on box plots and on Z scores thank you students please like share and subscribe to our Channel until we meet again