Statistics
Elaboration on each of the mentioned statistical topics with examples:
Basic Math
Arithmetic
Arithmetic involves basic mathematical operations such as addition, subtraction, multiplication, and division.
Example: Calculate the total sales for the week.
Total_Sales = Sales_Monday + Sales_Tuesday + Sales_Wednesday + Sales_Thursday + Sales_Friday
If Sales_Monday = 100, Sales_Tuesday = 200, Sales_Wednesday = 150, Sales_Thursday = 250, Sales_Friday = 300:
Total_Sales = 100 + 200 + 150 + 250 + 300 = 1000
Weighted Average
Weighted average takes into account the relative importance (weight) of each value.
Example: Calculate the weighted average score of a student with different weights for homework, midterm, and final exam.
Weighted_Average = (Homework_Score * Homework_Weight + Midterm_Score * Midterm_Weight + Final_Exam_Score * Final_Weight) / (Homework_Weight + Midterm_Weight + Final_Weight)
If Homework_Score = 85, Midterm_Score = 90, Final_Exam_Score = 95, and weights are 20%, 30%, and 50% respectively:
Weighted_Average = (85*0.2 + 90*0.3 + 95*0.5) / (0.2 + 0.3 + 0.5) = 91
Cumulative Sum
Cumulative sum is the running total of a sequence of numbers.
Example: Calculate cumulative sales over days.
Day 1 Sales: 100 Day 2 Sales: 200 Day 3 Sales: 150
Cumulative Sum:
Day 1: 100 Day 2: 100 + 200 = 300 Day 3: 300 + 150 = 450
Percentile
Percentile indicates the value below which a given percentage of observations in a group falls.
Example: Determine the 90th percentile of test scores.
Scores: 50, 60, 70, 80, 90, 95, 100
Using Excel formula PERCENTILE.INC(array, k)
, where array
is the list of scores and k
is the percentile (0.90 for 90th percentile):
=PERCENTILE.INC(A1:A7, 0.90)
Assuming the scores are in cells A1 to A7, the result might be 95.
Basic Statistics
Mean
Mean is the average of a set of numbers.
Example: Calculate the mean score of students.
Scores: 70, 80, 90Mean = (70 + 80 + 90) / 3 = 80
Median
Median is the middle value of a data set when it is ordered.
Example: Calculate the median score of students.
Scores: 70, 80, 90 Since the scores are already ordered, the median is 80.
Mode
Mode is the value that appears most frequently in a data set.
Example: Determine the mode of the test scores.
Scores: 70, 80, 80, 90
Mode:The mode is 80 as it appears twice.
Standard Deviation
Standard deviation measures the amount of variation or dispersion in a set of values.
Example: Calculate the standard deviation of test scores.
Scores: 70, 80, 90
Standard Deviation:
Using Excel formula `STDEV.P` for population standard deviation or `STDEV.S` for sample standard deviation: =STDEV.S(A1:A3)
Assuming the scores are in cells A1 to A3, the result might be around 10.
Normal Distribution
Normal distribution is a probability distribution that is symmetric about the mean.
Example: Analyze test scores assuming they follow a normal distribution with a mean of 75 and a standard deviation of 10.
Mean = 75, Standard Deviation = 10
You can use Excel to find probabilities or generate random data points:
Using NORM.DIST to find the probability that a score is below 85: =NORM.DIST(85, 75, 10, TRUE)
This might return approximately 0.8413, indicating that there is an 84.13% chance a score is below 85.
Visual Examples in Excel
- SUM:
=SUM(A1:A5)
- Weighted Average:
=SUMPRODUCT(A1:A3, B1:B3) / SUM(B1:B3)
- Cumulative Sum:
=SUM($A$1:A1)
- Percentile:
=PERCENTILE.INC(A1:A10, 0.9)
- Mean:
=AVERAGE(A1:A10)
- Median:
=MEDIAN(A1:A10)
- Mode:
=MODE.SNGL(A1:A10)
- Standard Deviation:
=STDEV.S(A1:A10)
- Normal Distribution:
=NORM.DIST(85, 75, 10, TRUE)
By using these examples and explanations, you can effectively apply basic math and statistical concepts in Excel to analyze and interpret your data.