5 Steps to Master Microsoft Excel: Workbooks Johanna Sariov
NetCom Learning
Š 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
AGENDA Calculating mean and median values Analyzing data using variance and standard deviation Visualizing data with histograms and charts Commonly used Z Score
Š 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
CALCULATING MEAN AND MEDIAN VALUES MEAN Function (Average) The mean, also called the average, is a measure of spread in statistics. The mean is calculated by adding up all the numbers in your data set and then dividing by the number of items in your data set. It may seem like an easy calculation, but it can become quite complex with larger data sets (think—thousands of numbers!). If you want to find a mean in Excel, you have two options: 1. Finding the Average (Mean) using Functions =Average(Range) 2. Full statistical output using the Data Analysis Toolpack
MEDIAN function The median is one of the three main measures of central tendency, which is commonly used in statistics for finding the center of a data sample or population, e.g. for calculating a typical salary, household income, home price, real-estate tax, etc For example, in the group of values {1, 2, 3, 4, 7} the median is 3. In the dataset {1, 2, 2, 3, 4, 7} the median is 2.5.
Š 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
ANALYZING DATA USING VARIANCE AND STANDARD DEVIATION Standard deviation and variance are basic mathematical concepts that play important roles throughout the financial sector, including the areas of accounting, economics, and investing. In the latter, for example, a firm grasp of the calculation and interpretation of these two measurements is crucial to the creation of an effective trading strategy. Standard deviation and variance are both determined by using the mean of a group of numbers in question. The mean is the average of a group of numbers, and the variance measures the average degree to which each number is different from the mean. The extent of the variance correlates to the size of the overall range of numbers—meaning the variance is greater when there is a wider range of numbers in the group, and the variance is less when there is a narrower range of numbers.
Š 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
VARIANCE AND STANDARD DEVIATION Standard Deviation • Standard deviation is a statistic that looks at how far from the mean a group of numbers is, by using the square root of the variance. The calculation of variance uses squares because it weighs outliers more heavily than data closer to the mean. This calculation also prevents differences above the mean from canceling out those below, which can sometimes result in a variance of zero.
• Standard deviation is calculated as the square root of variance by figuring out the variation between each data point relative to the mean. If the points are further from the mean, there is a higher deviation within the date; if they are closer to the mean, there is a lower deviation. So the more spread out the group of numbers are, the higher the standard deviation. • To calculate standard deviation, add up all the data points and divide by the number of data points, calculate the variance for each data point and then find the square root of the variance.
© 1998-2020 NetCom Learning
Variance • The variance is the average of the squared differences from the mean. To figure out the variance, first calculate the difference between each point and the mean; then, square and average the results. • For example, if a group of numbers ranges from 1 to 10, it will have a mean of 5.5. If you square and average the difference between each number and the mean, the result is 82.5. To figure out the variance, subtract 82.5 from the mean, which is 5.5 and then divide by N, which is the value of numbers, (in this case 10) minus 1. The result is a variance of about 9.17. Standard deviation is the square root of the variance so that the standard deviation would be about 3.03. • However, because of this squaring, the variance is no longer in the same unit of measurement as the original data. Taking the root of the variance means the standard deviation is restored to the original unit of measure and therefore much easier to measure.
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
SAMPLE DATA IN EXCEL With the Sampling tool that’s part of the Data Analysis command in Excel, you can randomly select items from a data set or select every nth item from a data set. For example, suppose that as part of an internal audit, you want to randomly select x amount of people from the staff list.
Š 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
VISUALIZING DATA WITH HISTOGRAMS AND CHARTS What is a histogram in Excel? Wikipedia defines a histogram in the following way: "Histogram is a graphical representation of the distribution of numerical data." Absolutely true, and… totally unclear :) Well, let's think about histograms in another way.
•
Have you ever made a bar or column chart to represent some numerical data? I bet everyone has. A histogram is a specific use of a column chart where each column represents the frequency of elements in a certain range. In other words, a histogram graphically displays the number of elements within the consecutive non-overlapping intervals, or bins.
•
For example, you can make a histogram to display the number of days with a temperature between 61-65, 66-70, 71-75, etc. degrees, the number of sales with amounts between $100-$199, $200-$299, $300$399, the number of students with test scores between 41-60, 61-80, 81100, and so on.
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
COMMONLY USED Z SCORE
© 1998-2020 NetCom Learning
www.netcomlearning.com
| info@netcomlearning.com | 1-888-563-8266
RECORDED WEBINAR VIDEO To watch the recorded webinar video for live demos, please access the link: http://tiny.cc/9udmnz
© 1998-2020 NetCom Learning
www.netcomlearning.com
|| info@netcomlearning.com || 1-888-563-8266
ABOUT NETCOM LEARNING
100K+
12K+
3500
Professionals trained
Corporate clients
IT, Business & Soft Skills courses
96%
8.6/9
20+
Of customers recommend us to others
Instructor evaluations
Leadingvendors recognitions
Microsoft’s
80%
Top20
Worldwide training partner of the year
Trained of the Fortune 100
ITTraining Company
Š 1998-2019 1998-2020NetCom NetCom Learning Learning
NetCom Learning is an award-winning global leader in managed learning services, training and talent development.
www.netcomlearning.com www.netcomlearning.com
Founded
: 1998
Headquarters
: New YorkCity
Delivery Capability
: Worldwide
CEO
: RussellSarder
|| info@netcomlearning.com || 1-888-563-8266
RECOMMENDED COURSES AND MARKETING ASSETS NetCom Learning offers a comprehensive portfolio for Business Application Courses » EXCEL LEVEL 1 (2019/2016/2013) – Class Scheduled on May 18 » 20778: ANALYZING DATA WITH POWER BI – Class Scheduled on May 18 » EXCEL LEVEL 2 (2019/2016/2013) – Class Scheduled on May 19 » EXCEL LEVEL 3 (2019/2016/2013) – Class Scheduled on May 20 » DATA ANALYSIS AND VISUALIZATION WITH MICROSOFT EXCEL – Class Scheduled on May 21 » 20779: ANALYZING DATA WITH EXCEL » OD20779A: ANALYZING DATA WITH EXCEL MOD+DMOC
Youcan also access the below Marketing Assets » Free 1 hr Training- Present Your Data Effectively With Microsoft Excel and PowerPoint » Free On-Demand Training – Introduction to Data Analysis Using Microsoft Excel » Free Assessment Quiz - Excel Level 1 (2019/2016/2013) Quiz » Free Assessment Quiz - Excel Level 2 (2019/2016/2013) Quiz » Blog - Advancing Excel Data Visualization Capabilities with Power BI
© 1998-2019 1998-2020NetCom NetCom Learning Learning
www.netcomlearning.com www.netcomlearning.com
|| info@netcomlearning.com || 1-888-563-8266
UPCOMING WEBINARS
• • • • • •
Cisco Professionals: How to Add API Skills to Your Networking Toolbox Planning Microsoft Office 365 Workload and Application Deployment Working with Walls, Floors, and Roofs in Autodesk Revit Penetrating Networks for CompTIA PenTest+ The Future of IT Service Management (ITSM) with ITIL® 4 Analytics in the Cloud with Tableau on AWS
& More
© 1998-2019 1998-2020NetCom NetCom Learning Learning
www.netcomlearning.com www.netcomlearning.com
|| info@netcomlearning.com || 1-888-563-8266
PROMOTIONS
Save up to 60% on our Virtual Instructor-Led Training courses! Get the most out of our Virtual Instructor-Led Training (vILT) deals and upskill at special discounts on individual courses. With vILT, make your learning flexible, convenient, and immersive. View Offer
Š 1998-2019 1998-2020NetCom NetCom Learning Learning
www.netcomlearning.com www.netcomlearning.com
|| info@netcomlearning.com || 1-888-563-8266
PROMOTIONS
Buy More to Save More! Now fulfill all your training needs without disturbing your business funds. Choose from the bundle of our Learning Saving Pass (LSP) pre-pay plans and get up to 100% value back on your investment. Unlock Now
Š 1998-2019 1998-2020NetCom NetCom Learning Learning
www.netcomlearning.com www.netcomlearning.com
|| info@netcomlearning.com || 1-888-563-8266
PROMOTIONS
Worry-Free Training with Price Match Guarantee Our Price Match Guarantee ensures that we'll match the offers of any other authorized training provider if you succeed at finding anyone offering the same publicly scheduled class within 30 days of our schedule at a lower regular price. Learn More
Š 1998-2019 1998-2020NetCom NetCom Learning Learning
www.netcomlearning.com www.netcomlearning.com
|| info@netcomlearning.com || 1-888-563-8266
FOLLOW US ON
© 1998-2019 1998-2020NetCom NetCom Learning Learning
YouTube
www.netcomlearning.com www.netcomlearning.com
|| info@netcomlearning.com || 1-888-563-8266
BUILDING AN INNOVATIVE LEARNING ORG.
A NEW BOOK FROM RUSSELL SARDER, CEO AT NETCOM LEARNING
A framework to build a smarter workforce, adapt to change and drive growth. DOWNLOAD e-book
© 1998-2019 1998-2020NetCom NetCom Learning Learning
www.netcomlearning.com www.netcomlearning.com
|| info@netcomlearning.com || 1-888-563-8266
Thank you
1998-2019 NetCom Learning Š 1998-2020
www.netcomlearning.com www.netcomlearning.com
| || info@netcomlearning.com || 1-888-563-8266