Stats and Maths

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

  1. SUM:=SUM(A1:A5)
  2. Weighted Average:=SUMPRODUCT(A1:A3, B1:B3) / SUM(B1:B3)
  3. Cumulative Sum:=SUM($A$1:A1)
  4. Percentile:=PERCENTILE.INC(A1:A10, 0.9)
  5. Mean:=AVERAGE(A1:A10)
  6. Median:=MEDIAN(A1:A10)
  7. Mode:=MODE.SNGL(A1:A10)
  8. Standard Deviation:=STDEV.S(A1:A10)
  9. 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.

Scroll to Top