Essential Excel Data Analysis Techniques for Business
Data Management Fundamentals: Sorting and Filtering
1. Sorting: Putting Data in Order
Purpose: To rearrange your data in a specific sequence (like A to Z or High to Low) without removing anything.
Types of Sorting
- Alphabetical (A to Z or Z to A): Great for names, cities, or product types.
- Numerical (Smallest to Largest): Best for prices, ages, or quantities.
- Date (Oldest to Newest): Perfect for tracking timelines or schedules.
Sorting Example
Imagine you have a list of students and their test scores.
- Unsorted:
Mastering Essential Excel Functions for Data Analysis
Essential Excel Tools for Data Analysis
These tools allow you to connect different datasets and distill thousands of rows into a few meaningful numbers.
Lookup Functions and Data Summarization
1. VLOOKUP (Vertical Lookup)
This is the most common lookup function. Use it when your data is arranged in columns (vertically). It searches for a value in the leftmost column and looks to the right to find what you need.
The Syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- lookup_value:
Statistical Inference Solutions: Estimation and Hypothesis Testing
Q1 True/False (each needs a quick justification)
Q1(a) Binomial(100, 0.2) approximated by Normal N(20,16)
Steps:
Identify: X ~ Bin(n=100, p=0.2).
Compute mean: μ = np = 100(0.2) = 20.
Compute variance: σ² = np(1−p) = 100(0.2)(0.8) = 16.
Rule: For large n, Binomial ≈ Normal with same mean/variance (CLT-ish approximation).
Conclusion: Yes, approximate with N(20,16).
Q1(b) MAP estimator equals argmax log posterior
Steps:
MAP definition: θ̂MAP = argmaxθ f(θ|x).
Taking log does not move the maximizer
Essential Templates for Data Visualization Reporting
Line Graph Analysis Template
- The line graph illustrates trends in [subject] over a [time period] from [start date] to [end date]. The data is measured in [units] and demonstrates [general observation].
- Overall, the most significant trend is the [adjective] [increase/decrease] in [main subject]. While [item A] showed substantial growth, [item B] experienced considerable fluctuation. The period witnessed [item C] consistently [outperforming/underperforming] other categories.
- Beginning with [item A],
Geometry Fundamentals and Logic Principles
Chapter 1: Foundations of Geometry
1.1: Points, Lines, and Planes
- Collinear: Points on the same line.
- Coplanar: Points that lie in the same plane.
- Segment: A part of a line consisting of two endpoints and all points between them.
- Endpoint: A point at the end of a segment or the starting point of a ray.
- Ray: Part of a line that extends from one endpoint infinitely in one direction.
- Opposite rays: Two rays that have a common endpoint and form a line.
- Postulate: A statement that is accepted as true without
Engineering Investment Evaluation: Present, Future & Annual Worth
Present, Future & Annual Equivalent — Equal & Unequal Lives
1. Purpose of Investment Evaluation
Used to compare different engineering projects based on their economic worth.
Projects may have:
- Different lifetimes
- Different cash-flow patterns
- Different initial investments
Goal → Select the economically best alternative.
2. Interest Factors (Essential)
(These appear multiple times, so memorize.)
Let i = interest rate per period, n = number of periods.
(P/F, i, n)
Present worth of single future sum: