Consumer Analytics 301 Exam 2

Q: How many?

=COUNT()

Q: What is the average?

=AVERAGE()

Q: What is the median?

=MEDIAN()

Q: What is the mode?

=MODE()

Q: What is Variance?

=VAR.S()

Q: What is Standard Deviation?

=STDEV.S()

Q: What is inter-quartile range?

=QUARTILE(data, 3) - QUARTILE(data, 1)

Q: What is the Skew?

=SKEW()

Q: What is Kurtosis of the data? (What do the tails look like?)

=KURT()

Q: How many stores are in each climate category?

=COUNTIF(data, “EX”)

Q: What is the average store sales for each store size?

=AVERAGEIF(size data, size code num, sales data)

Q: What is the percentage of stores in each size code?

=COUNTIF(size data, size code num)/total number of stores

Q: What is the percentage of each store size out of total sales?

=SUMIF(size data, size code num, sales data)/SUM (sales data)

Q: Index

=(total sales %/ % of stores)*100

T-test: is there a significant difference?

Mean: AVERAGEIF

Count: COUNTIF

DF: count-1

P-value: T.test(IF(climate_dummy=1, data), IF(climate_dummy=0,data),2,2

CHI-SQUARE: Observed

Count: COUNTIFS(climate_dummy, 0, Size, 1) or COUNTIFS(climate_dummy, 1, size, 1)

TOTAL: SUM(rows)

CHI-SQUARE: Expected

Horizontal total * vertical total/ overall total

CHI-SQUARE: Individual Deviation

= ((observed-expected)²)/expected

CHI-SQUARE: Analysis

X²: =SUM(individual deviation boxes)

D.F.: (2-1)(3-1)=2

p-value: =CHISQ.TEXT(observed boxes without totals, expected boxed without totals)

Correlation: Is there a relationship?

Correlation ( r ): =CORREL(count, sales data)

T-stat: r * SqRT(total store count -2)/SQRT((1-(r²)))

P-value: =T.DIST.2T(t-stat, DF)

R

correlation between predicted and observed values of your dependent variable

R²

proportion of variance that is “explained” by your equation

• on a scale of 0-1, closer to 0 shows low variance, closer to 1 shows greater variance of equation

F

statistic used to test whether the model fits the data well

• good if less than 0.05

Multiple R

the correlation between our predicted outcome of Y and our observed outcome of Y

• scale of 0-1, closer to 1 is a positive strong correlation

Adjusted R²

use if there are multiple IVs used

ANOVA (analysis of variance)

provides information about “levels of variability” within a regression model that allow us to test the overall significance of the regression model