DataWorld

EXCEL DATA ANALYTICS

Diagnostic Data Analytics: Hypothesis Testing in Excel

(Brief Student Notes)

1. What is Diagnostic Data Analytics?

Diagnostic data analytics focuses on finding the reasons behind outcomes.
It answers questions like:

Why did sales drop?

Why is performance different between groups?

Is the difference statistically significant or just random?

A key tool used is Hypothesis Testing in Excel.

2. What is Hypothesis Testing?

Hypothesis testing is a statistical method used to make decisions using sample data.

Key terms:

Null Hypothesis (H₀): No difference or effect exists

Alternative Hypothesis (H₁): A difference or effect exists

p-value: Probability of results occurring by chance

Significance level (α): Usually 0.05 (5%)

👉 If p-value < 0.05, reject H₀
👉 If p-value ≥ 0.05, fail to reject H₀

3. T.TEST in Excel

Purpose:

Compares the means of two groups.

Excel Formula:

=T.TEST(array1, array2, tails, type)

Types:

Type 1: Paired samples (before/after)

Type 2: Two-sample equal variance

Type 3: Two-sample unequal variance

Example:

Compare exam scores of Class A vs Class B.

4. Z.TEST in Excel

Purpose:

Used when:

Sample size is large (n ≥ 30)

Population variance is known

Excel Formula:

=Z.TEST(array, x, [sigma])

Interpretation:

Returns p-value

Helps test if sample mean differs from population mean

5. F.TEST in Excel

Purpose:

Compares variances of two datasets

Excel Formula:

=F.TEST(array1, array2)

Use case:

Check if two groups have equal variability before performing t-test.

Interpretation:

Small p-value → variances are different

Large p-value → variances are similar

6. ANOVA Test (Analysis of Variance)

Purpose:

Compares three or more group means

Types in Excel:

Single Factor ANOVA (most common)

How to run in Excel:

Go to Data → Data Analysis ToolPak

Select ANOVA: Single Factor

Input data range

Click OK

Output includes:

F-value

p-value

Between-group and within-group variation

Interpretation:

If p-value < 0.05 → at least one group differs significantly

EXCEL DATA ANALYTICS

Predictive Data Analytics: Correlation Analysis & Probability Analysis (Brief Student Notes)

1. Predictive Data Analytics (Overview)

Predictive data analytics in Excel uses historical data to forecast future outcomes. It relies on statistical techniques such as:

Correlation analysis

Regression (trend prediction)

Probability analysis

What-if analysis (scenarios, goal seek)

2. Correlation Analysis in Excel

Correlation measures the strength and direction of relationship between two variables.

Value ranges from -1 to +1

+1 → Perfect positive relationship

0 → No relationship

-1 → Perfect negative relationship

Excel Tools:

=CORREL(array1, array2)

Data Analysis Toolpak → Correlation

Interpretation:

Strong positive correlation → both variables increase together

Strong negative correlation → one increases while the other decreases

Weak correlation → little or no relationship

Example:

Advertising spend vs Sales revenue

Study hours vs Exam scores

3. Probability Analysis in Excel

Probability analysis helps estimate the likelihood of an event occurring based on data patterns.

It is widely used in:

Risk analysis

Sales forecasting

Decision making under uncertainty

Key Concept: Conditional Probability

genui{"probability_statistics_learning_block":{"type_id":"CONDITIONAL_PROBABILITY_DEFINITION"}}

This helps determine the probability of an event happening given that another event has already occurred, which is important in predictive modeling.

Excel Probability Tools:

COUNTIF() and COUNTIFS() (frequency-based probability)

Basic formula:
[
P(A) = \frac{\text{Number of favorable outcomes}}{\text{Total outcomes}}
]

Example:

Probability that a customer buys a product given they clicked an ad

4. Relationship Between Correlation & Probability

Correlation shows relationship strength

Probability shows likelihood of events

Together, they improve prediction accuracy

Example:

High correlation between income and spending → higher probability of purchase prediction

5. Summary

Predictive analytics in Excel combines:

Correlation → understanding relationships

Probability → estimating outcomes

Together → better forecasting and decision-making

EXCEL DATA ANALYTICS

KPIs in Descriptive Data Analytics (Brief Student Note)

1. What are KPIs?

Key Performance Indicators (KPIs) are measurable values used in data analytics to show how well a process, business, or activity is performing. In descriptive analytics, KPIs help summarize past and current performance using data.

In Excel, KPIs are usually built using formulas, PivotTables, charts, and dashboards.

2. Purpose of KPIs in Descriptive Analytics

KPIs help to:

Measure performance over time

Track progress toward goals

Identify strengths and weaknesses

Support decision-making using data

Simplify complex datasets into meaningful insights

3. Common KPIs in Excel Data Analytics

(a) Sales KPIs

Total Sales = SUM(Sales Range)

Average Sales = AVERAGE(Sales Range)

Sales Growth Rate = (Current - Previous) / Previous

(b) Financial KPIs

Profit = Revenue − Cost

Profit Margin = Profit / Revenue × 100

Expenses Ratio = Expenses / Revenue × 100

(c) Operational KPIs

Productivity Rate = Output / Input

Efficiency = Actual Output / Expected Output

(d) Customer KPIs

Customer Count = COUNT(Customers)

Retention Rate = (Returning Customers / Total Customers) × 100

Satisfaction Score (Average rating)

4. KPIs in Excel Tools

(a) Formulas

Used for calculating KPI values:

SUM, AVERAGE, COUNTIF, IF, VLOOKUP

(b) PivotTables

Summarize large datasets

Group KPIs by category (month, region, product)

(c) Charts & Dashboards

Line charts for trends

Bar charts for comparisons

KPI cards for key values

5. Example KPI Dashboard Elements

A simple Excel KPI dashboard may include:

Total Sales Card

Monthly Profit Trend Chart

Top 5 Products Table

Customer Growth Chart

Performance Indicators (green/yellow/red status)

6. KPI Indicators (Status Colors)

🟢 Green = Target achieved

🟡 Yellow = Warning / near target

🔴 Red = Below target

7. Importance of KPIs

Improves business performance tracking

Helps managers make data-driven decisions

Makes reporting simple and visual

Identifies trends early

EXCEL DATA ANALYTICS

Measures of Spread in Descriptive Data Analytics (Brief Student Notes)

1. Meaning of Measures of Spread

Measures of spread (also called measures of dispersion) describe how data values are spread out or scattered from the central value (mean or median). They help us understand data consistency and variability.

2. Key Measures of Spread

(a) Range

The simplest measure of spread.

Formula:
[
\text{Range} = \text{Maximum value} - \text{Minimum value}
]

In Excel:

=MAX(A2:A20)-MIN(A2:A20)

Meaning:
Shows the gap between the highest and lowest values.

(b) Variance

Measures the average squared deviation from the mean.

Types:

Sample Variance: VAR.S

Population Variance: VAR.P

In Excel:

=VAR.S(A2:A20) (most commonly used in class data)

Meaning:

High variance → data is widely spread

Low variance → data is closely grouped

(c) Standard Deviation

The square root of variance. It is the most commonly used measure of spread.

In Excel:

Sample: =STDEV.S(A2:A20)

Population: =STDEV.P(A2:A20)

Meaning:

Shows average distance of values from the mean

More interpretable than variance

(d) Interquartile Range (IQR)

Measures spread of the middle 50% of data.

Formula:
[
IQR = Q3 - Q1
]

In Excel:

Q1: =QUARTILE.INC(A2:A20,1)

Q3: =QUARTILE.INC(A2:A20,3)

IQR: =Q3 - Q1

Meaning:

Reduces effect of extreme values (outliers)

(e) Coefficient of Variation (CV)

Measures relative spread compared to the mean.

Formula:
[
CV = \frac{Standard\ Deviation}{Mean} \times 100
]

In Excel:

=STDEV.S(A2:A20)/AVERAGE(A2:A20)*100

Meaning:

Used to compare variability between different datasets

3. Practical Excel Steps (Simple Workflow)

Enter data in a column (e.g., A2:A20)

Use formulas:

Mean → =AVERAGE(A2:A20)

Std Dev → =STDEV.S(A2:A20)

Variance → =VAR.S(A2:A20)

Range → =MAX(A2:A20)-MIN(A2:A20)

Interpret results using comparison (high vs low spread)

4. Importance of Measures of Spread

Helps understand data consistency

Identifies outliers

Supports decision-making in business and research

Useful in comparing datasets (e.g., student scores, sales data)

EXCEL DATA ANALYTICS

Variance and Standard Deviation (Descriptive Statistics) – Brief Student Note

In descriptive data analytics, variance and standard deviation are key measures used to show how data values are spread out around the mean (average). They help you understand whether data is consistent or highly varied.

1. Variance

Variance measures the average squared distance between each data value and the mean.

A small variance → data points are close to the mean (consistent data)

A large variance → data points are widely spread (less consistent data)

genui{"probability_statistics_learning_block":{"type_id":"VARIANCE"}}

Excel Formulas for Variance:

=VAR.S(range) → Sample variance (most commonly used)

=VAR.P(range) → Population variance

2. Standard Deviation

Standard deviation is the square root of variance. It shows data spread in the same unit as the original data, making it easier to interpret.

Low standard deviation → values are close to average

High standard deviation → values are widely spread

Excel Formulas for Standard Deviation:

=STDEV.S(range) → Sample standard deviation

=STDEV.P(range) → Population standard deviation

3. Why They Matter in Excel Data Analysis

Helps identify data consistency

Used in financial analysis, business performance tracking, and research

Supports decision-making by showing risk or variability

Essential for charts, dashboards, and statistical reports

4. Quick Example in Excel

If your data is in cells A1:A10:

Variance: =VAR.S(A1:A10)

Standard Deviation: =STDEV.S(A1:A10)

DESCRIPTIVE DATA ANALYTICS, EXPLORATORY DATA ANALYTICS (EDA) & DASHBOARD SETUP IN EXCEL

Brief Student Notes (Microsoft Excel)

1. Descriptive Data Analytics

Descriptive analytics focuses on summarizing historical data to understand what has happened.

Purpose:

To describe data using simple summaries

To identify patterns and trends

To generate basic insights

Common Techniques in Microsoft Excel:

Averages (Mean, Median, Mode)

Percentages and ratios

Tables and frequency distributions

Charts (bar charts, pie charts, line graphs)

Pivot Tables

Example:

Total sales per month

Average student scores

Number of customers per region

2. Exploratory Data Analytics (EDA)

EDA is the process of investigating data deeply to discover patterns, relationships, and anomalies before formal modeling.

Purpose:

Understand data structure

Detect missing values or errors

Identify trends and outliers

Generate hypotheses

Key EDA Activities in Excel:

Sorting and filtering data

Conditional formatting (highlight patterns/outliers)

Pivot Tables and Pivot Charts

Data cleaning (removing duplicates, fixing errors)

Correlation analysis (using Data Analysis ToolPak)

Scatter plots for relationships

Example:

Relationship between advertising and sales

Student attendance vs performance

3. Dashboard Setup in Excel

A dashboard is a visual summary of key data insights used for decision-making.

Purpose:

Present data clearly and interactively

Track performance indicators (KPIs)

Support quick decision-making

Steps to Create an Excel Dashboard:

Step 1: Prepare Data

Clean and organize dataset

Ensure proper formatting (tables)

Step 2: Analyze Data

Use Pivot Tables for summaries

Create calculated fields if needed

Step 3: Visualize Data

Insert charts (bar, line, pie)

Use Pivot Charts for dynamic visuals

Step 4: Build the Dashboard Sheet

Create a new worksheet named “Dashboard”

Arrange charts neatly

Add titles and labels

Step 5: Add Interactivity

Insert slicers (for filtering data)

Add timelines (for date-based filtering)

Step 6: Final Formatting

Use consistent colors

Align visuals properly

Remove gridlines for professional look

1. Skewness: The Measure of Asymmetry

Skewness tells you where the "tail" of your distribution is located. It measures how much your data deviates from a perfect, symmetrical Bell Curve (Normal Distribution).

Positive Skew (Right-Skewed): The tail extends toward the right (positive) side. Most of your data is bunched up on the left, with a few extreme high values pulling the mean to the right.

Rule of thumb: Mean > Median.

Negative Skew (Left-Skewed): The tail extends toward the left (negative) side. Most of your data is on the right, with a few extreme low values pulling the mean to the left.

Rule of thumb: Mean < Median.

Zero Skew: Perfect symmetry. The left and right sides are mirror images.

Rule of thumb: Mean ≈ Median ≈ Mode.

2. Kurtosis: The Measure of "Tails" and "Peakedness"

While skewness looks at the direction of the tail, kurtosis looks at the thickness of the tails and the sharpness of the peak. It essentially tells you how "extreme" your data is compared to a normal distribution.

Leptokurtic (Positive Kurtosis): These distributions have "heavy" or "fat" tails and a sharper, taller peak. This indicates that your data contains more extreme outliers than a normal distribution.

Mesokurtic (Zero Kurtosis): This is the baseline, representing a standard normal distribution.

Platykurtic (Negative Kurtosis): These have thin, light tails and a flatter, broader peak. There are fewer extreme values compared to a normal distribution, as the data is spread out more evenly.

Why does this matter?

Understanding these properties is crucial for statistical modeling and risk assessment:

Outlier Sensitivity: If your data is highly leptokurtic, you are more likely to encounter extreme events (often called "Black Swans" in finance).

Model Assumptions: Many statistical tests (like standard linear regression or t-tests) assume your data is normally distributed (zero skew, zero excess kurtosis). If your data is heavily skewed, you may need to transform your data (e.g., using log or square root transformations) before running analysis.

Central Tendency: Knowing the skew helps you choose the right representative statistic. If your data is heavily skewed (like income), the median is a much better representation of the "typical" value than the mean, which gets dragged by the outliers.

Descriptive data analytics helps summarize and understand the features of a dataset.

In Excel, calculating the measures of central tendency—Mean (Average), Median, and Mode—is straightforward using built-in functions.

1. Mean (Average)

The mean is the arithmetic average of a set of numbers. In Excel, you use the AVERAGE function.

Excel Formula: =AVERAGE(range)

Example: If your data is in cells A1 through A10, enter =AVERAGE(A1:A10).

2. Median

The median is the middle value in a sorted list of numbers. It is more robust than the mean when your data contains outliers or is skewed.

Excel Formula: =MEDIAN(range)

Example: For the same data, enter =MEDIAN(A1:A10).

3. Mode

The mode is the value that appears most frequently in a dataset.

Note: If your data has multiple modes (multimodal), newer versions of Excel allow for the MODE.MULT function, while MODE.SNGL returns the first occurring mode.

Excel Formula (Single Mode): =MODE.SNGL(range)

Excel Formula (Multiple Modes): =MODE.MULT(range) (Select multiple cells before entering this as an array formula).

Pro-Tip: The "Analysis ToolPak"

If you need a comprehensive summary of your data, you don't have to calculate these individually. You can use the Data Analysis ToolPak in Excel:

Go to the Data tab.

Click Data Analysis (if you don't see it, go to File > Options > Add-ins > Manage: Excel Add-ins > Go > Check "Analysis ToolPak").

Select Descriptive Statistics.

Input your data range and check the box for Summary statistics.

DESCRIPTIVE DATA ANALYTICS WITH EXCEL

WORKING WITH DESCRIPTIVE STATISTICS:
CORRELATION

Understanding Correlation

Correlation is a statistical measure that describes the size and direction of a relationship between two or more variables. It tells you how strongly a pair of variables is related and how they change in relation to one another.

Key Concepts

Positive Correlation: Both variables move in the same direction. As one increases, the other also increases (e.g., hours spent studying and test scores).

Negative (Inverse) Correlation: The variables move in opposite directions. As one increases, the other decreases (e.g., speed of a car and the time it takes to reach a destination).

Zero Correlation: There is no predictable relationship between the variables (e.g., shoe size and intelligence).

The Correlation Coefficient ($r$)

The strength and direction of the relationship are expressed by the correlation coefficient ($r$), which ranges from -1 to +1:

Magnitude: The closer the number is to 1 or -1, the stronger the relationship.

Sign: The plus (+) or minus (-) sign indicates the direction of the relationship.

Important Distinction: Correlation vs. Causation

The most critical rule in statistics is: Correlation does not imply causation.

Just because two variables are correlated does not mean that one causes the other. A correlation simply indicates that they move together, but there could be a third "lurking" variable influencing both, or the relationship could be purely coincidental.

Example: Ice cream sales and the number of sunburns are positively correlated. However, eating ice cream does not cause sunburns; both are influenced by a third variable: hot, sunny weather.

DESCRIPTIVE DATA ANALYTICS WITH EXCEL

WORKING WITH DESCRIPTIVE STATISTICS:
HISTOGRAM

A histogram is a graphical representation of the distribution of numerical data. It groups continuous data into ranges called bins (or intervals) and displays the frequency of data points falling into each bin using adjacent bars.

Key Characteristics

Continuous Data: Unlike a bar chart (which compares categories), a histogram is used for interval or ratio-scaled data (e.g., test scores, heights, or time).

Bins: The x-axis is divided into equal intervals. The height of each bar represents the frequency or the number of data points that fall within that specific bin.

No Gaps: Because the data is continuous, the bars in a histogram are typically drawn touching each other, indicating a continuous scale.

Shape: The shape of a histogram reveals the underlying distribution of the data (e.g., normal distribution, skewed left, or skewed right).

When to Use a Histogram

To visualize the shape of your data (e.g., identifying outliers or central tendency).

To determine if your data follows a normal distribution (bell curve).

To quickly summarize large datasets by grouping them into manageable intervals.