1/64
Looks like no tags are added yet.
Calculate the mean
=AVERAGE(range)
Calculate the median
=MEDIAN(range)
Calculate the sample standard deviation
=STDEV.S(range)
Find the minimum
=MIN(range)
Find the maximum
=MAX(range)
Calculate the range
=MAX(range)-MIN(range)
Count numeric observations
=COUNT(range)
Add values
=SUM(range)
Find the first quartile
=QUARTILE.EXC(range,1)
Find the third quartile
=QUARTILE.EXC(range,3)
Calculate the interquartile range
=QUARTILE.EXC(range,3)-QUARTILE.EXC(range,1)
Find the 90th percentile
=PERCENTILE.EXC(range,0.9)
Average values meeting multiple conditions
=AVERAGEIFS(average_range,criteria_range1,criteria1,criteria_range2,criteria2)
Average spending in I for females in F
=AVERAGEIFS(I2:I401,F2:F401,"Female")
Write the general IF formula
=IF(logical_test,value_if_true,value_if_false)
Label spending of 150 or less Small and spending above 150 Large
=IF(I2<=150,"Small","Large")
Write the general COUNTIF formula
=COUNTIF(range,criteria)
Count people labeled Female in column F
=COUNTIF(F2:F401,"Female")
Count spending values greater than 150 in column I
=COUNTIF(I2:I401,">150")
Count values greater than the value in Q2
=COUNTIF(I2:I401,">"&Q2)
Write the general COUNTIFS formula
=COUNTIFS(criteria_range1,criteria1,criteria_range2,criteria2)
Count Small spenders who are Female
=COUNTIFS($J$2:$J$401,"Small",$F$2:$F$401,"Female")
Count Large spenders who are Male
=COUNTIFS($J$2:$J$401,"Large",$F$2:$F$401,"Male")
Count values greater than 50 and less than 100
=COUNTIFS(I2:I401,">50",I2:I401,"<100")
Count values between Q2 and R2, including both endpoints
=COUNTIFS(I2:I401,">="&Q2,I2:I401,"<="&R2)
Create a frequency distribution
=FREQUENCY(data_array,bins_array)
Calculate a joint probability from a table
=joint_cell_count/grand_total
Calculate a marginal probability from a table
=category_total/grand_total
Calculate P(A given B) from counts
=count_for_both_A_and_B/count_for_B
Calculate P(Large given Male) using 59 large males and 166 total males
=59/166
Calculate P(Male and Large) using 59 large males and 400 total people
=59/400
Find the normal probability to the left of x
=NORM.DIST(x,mean,standard_dev,TRUE)
Find the normal probability to the right of x
=1-NORM.DIST(x,mean,standard_dev,TRUE)
Find the normal probability between a and b
=NORM.DIST(b,mean,standard_dev,TRUE)-NORM.DIST(a,mean,standard_dev,TRUE)
Find a normal cutoff from a left-tail probability
=NORM.INV(probability,mean,standard_dev)
Find the 90th percentile of a normal distribution
=NORM.INV(0.90,mean,standard_dev)
Find the normal cutoff exceeded 5% of the time
=NORM.INV(0.95,mean,standard_dev)
Find P(X>b given X>a) for a normal variable, where b>a
=(1-NORM.DIST(b,mean,standard_dev,TRUE))/(1-NORM.DIST(a,mean,standard_dev,TRUE))
Find P(X<a given X<b) for a normal variable, where a<b
=NORM.DIST(a,mean,standard_dev,TRUE)/NORM.DIST(b,mean,standard_dev,TRUE)
Calculate a stock's decimal return
=(new_price-old_price)/old_price
Find the probability of a positive normally distributed return
=1-NORM.DIST(0,mean_return,SD_return,TRUE)
Find the probability a stock closes at a target price or below when returns are normal
=NORM.DIST((target_price-current_price)/current_price,mean_return,SD_return,TRUE)
Find the z-based margin of error for a mean
=CONFIDENCE.NORM(alpha,population_SD,n)
Find the t-based margin of error for a mean
=CONFIDENCE.T(alpha,sample_SD,n)
Find the lower confidence limit for a mean with unknown population SD
=AVERAGE(range)-CONFIDENCE.T(alpha,STDEV.S(range),COUNT(range))
Find the upper confidence limit for a mean with unknown population SD
=AVERAGE(range)+CONFIDENCE.T(alpha,STDEV.S(range),COUNT(range))
Find the lower confidence limit for a mean with known population SD
=sample_mean-CONFIDENCE.NORM(alpha,population_SD,n)
Find the upper confidence limit for a mean with known population SD
=sample_mean+CONFIDENCE.NORM(alpha,population_SD,n)
Calculate an expected count for a chi-square test
=row_total*column_total/grand_total
Calculate the chi-square test p-value
=CHISQ.TEST(observed_range,expected_range)
Calculate pooled variance for two independent samples assuming equal variances
=((n1-1)s1^2+(n2-1)s2^2)/(n1+n2-2)
Calculate the standard error of a difference using pooled variance
=SQRT(pooled_variance*(1/n1+1/n2))
Calculate the pooled two-sample t statistic
=((mean1-mean2)-null_difference)/SQRT(pooled_variance*(1/n1+1/n2))
Calculate degrees of freedom for a pooled two-sample t test
=n1+n2-2
Find the positive t critical value for a two-sided confidence interval
=T.INV(1-alpha/2,df)
Find the positive t critical value for a 95% confidence interval
=T.INV(0.975,df)
Calculate the margin of error for a pooled two-sample mean difference
=T.INV(1-alpha/2,n1+n2-2)SQRT(pooled_variance(1/n1+1/n2))
Find the lower confidence limit for a difference in means
=(mean1-mean2)-margin_of_error
Find the upper confidence limit for a difference in means
=(mean1-mean2)+margin_of_error
Calculate a square root
=SQRT(number)
Calculate an absolute value
=ABS(number)
Calculate correlation
=CORREL(x_range,y_range)
Predict Y using a regression equation
=intercept+slope*x
Calculate the approximate 95% prediction interval lower limit used in class
=predicted_y-2*model_standard_error
Calculate the approximate 95% prediction interval upper limit used in class
=predicted_y+2*model_standard_error