Excel, Data Analytics, and Statistics: Key Concepts and Formulas

What is the difference between relative addressing and absolute addressing in Excel?

A relative address uses just the row and column label; an absolute address uses a dollar sign ($) before the row or column label.

What Excel function is used to find the largest value in a range of cells?

MAX(range)

What type of analytics summarizes and describes what happened in the data?

Descriptive analytics

What does predictive analytics do?

Detects patterns in historical data and extrapolates them forward in time.

Which question will prescriptive analytics help a company address?

"What is the best way of shipping goods to minimize costs?"

What form of model represents the demand for coffee beans over three months?

Visual model

What are decision options?

Quantities that are controllable and can be selected at the discretion of the decision maker.

What do prescriptive decision models help decision makers do?

Identify the best solution to decision problems.

What type of analytics does OESC project to optimize resource allocation and maximize outcomes?

Prescriptive analytics

During which phase in problem-solving is a formal model often developed?

Analyzing the problem

What is the logical function that returns one value if true and another if false in Excel?

IF(condition, value if true, value if false)

What happens when you drag a field into the Filter area in PivotTables?

It allows addition of a third dimension to the analysis.

Which tab must be accessed to select a chart type in Excel from the Charts group?

Insert tab

Which chart does Roger want to use to compare values across categories using vertical rectangles?

Clustered column chart

Which data type is continuous and has a natural zero point?

Ratio data

Identify ratio data from a shoe manufacturing database.

Item cost — it is continuous with a true zero point.

How is a relative frequency distribution for categorical data constructed?

Divide the number of observations in each category by the total number of observations.

Which statement is NOT true about cross-tabulation?

"Each observation can be classified into multiple subcategories" — FALSE.

What is the purpose of sampling?

Obtain sufficient information to draw a valid inference about a population.

Which is NOT a measure of location?

Variance — it is a measure of dispersion, not location.

What is the median of the data set {2,3,3,5,6,6,6,7,10,10,10,10,12,15,15}?

7 — the 8th value in 15 sorted observations.

What is the mode of the data set {2,3,3,5,6,6,6,7,10,10,10,10,12,15,15}?

10 — it appears 4 times, more than any other value.

What is a key difference between a mean and a median?

A median is not affected by outliers; a mean is affected by outliers.

What is the formula for midrange?

Midrange = (Max + Min) / 2

What describes dispersion in statistics?

The degree of variation in the data.

What describes variance?

The average of the squared deviations of the observations from the mean.

What describes standard deviation?

It is the square root of the variance.

Which is NOT a measure of dispersion?

Midrange — it is a measure of location, not dispersion.

What describes a positively skewed histogram?

More mass of data on the LEFT, with the tail extending toward the RIGHT.

What measures the degree of asymmetry of observations around the mean?

Coefficient of skewness.

What does kurtosis refer to in statistics?

The peakedness or flatness of a histogram.

What is a measure of the linear relationship between two variables?

Correlation.

What value can the Correlation Coefficient NOT take?

1.5 — correlation ranges only from -1 to +1.

What does a correlation coefficient of 0.8 indicate?

A positive linear relationship exists between the two variables.

What is the probability that a respondent is female and prefers Brand 3?

0.2 — read directly from the joint probability table.

What is the probability that a respondent prefers Brand 3?

0.45 — the column total for Brand 3.

What is the formula for conditional probability P(Female | Brand 3)?

P(Female | Brand 3) = P(Female ∩ Brand3) / P(Brand3) = 0.2 / 0.45.

What is true of normal distributions?

The mean, median, and mode are all equal.

True or False: Probabilities can be defined from only one perspective.

False — probabilities can be classical, empirical, or subjective.

True or False: Two events are mutually exclusive if they have at least one outcome in common.

False — mutually exclusive means they have NO outcomes in common.

True or False: In a normal distribution, half the area lies above the mean and half below.

True.

Which probability distribution assumes all values within a specified range are equally likely?

Uniform distribution.

How can you differentiate between descriptive, predictive, and prescriptive analytics?

Descriptive = "What happened?" | Predictive = "What will happen?" | Prescriptive = "What should we do?"

What are the four data measurement scales in order?

Categorical (Nominal) → Ordinal → Interval → Ratio.

What is the difference between continuous and discrete random variables?

Continuous = any value; Discrete = countable whole numbers.

Which is a continuous random variable: hits on a website, patients per day, new hires per year, weight of patients?

Weight of patients — it can take any value.

What is the range formula?

Range = Maximum value − Minimum value.

What is the IQR (Interquartile Range)?

IQR = Q3 − Q1.

What is the conditional probability formula?

P(A|B) = P(A ∩ B) / P(B).

What is the difference between AND and OR functions in Excel?

AND returns TRUE only if ALL conditions are true; OR returns TRUE if ANY condition is true.

What is the shortcut to toggle between relative and absolute cell references in Excel?

Press F4.