Deck 8: Statistical Functions

Question 1

This is the top three lines in a list of 8200 records.You want an average of the number of speeding tickets for Red Toyota Priuses.Which is the correct formula?

A)=AVERAGEIFS(A2:A8201,D2:D8201,"Red",B2:B8201,"Toyota",C2:C8201,"Prius")
B)=AVERAGEIFS(E2:E8201,D2:D8201,"Red",B2:B8201,"Toyota",C2:C8201,"Prius")
C)=AVERAGEIF(E2:E8201,D2:D8201,"Red",B2:B8201,"Toyota",C2:C8201,"Prius")
D)=AVERAGEIF(E2:E8201,"Red","Toyota","Prius")

Answer 1

B

Question 2

Which of the following is not an argument in the PERCENTRANK.INC and PERCENTRANK.EXC functions?

A)array
B)criteria
C)x
D)significance

Answer 2

B

Question 3

Which function returns the rank of a value within a dataset, assigning an average rank for tied values?

A)RANK.EQ
B)RANK.AVG
C)RANK.TIE
D)AVERAGERANK

Answer 3

B

Question 4

In SUMIF, AVERAGEIF, and COUNTIF functions using text criteria, enclose the criteria in:

A)**
B)<>
C)##
D)""

Question 5

If you autofill the formula =RANK.AVG(A1,$A$1:$A$5,0)from B1 to B5, what would the value be in B5?

A)4.5
B)4
C)3.5
D)3

Question 6

What is the first argument of an SUMIFS function?

A)Criteria_range1
B)Sum_range
C)Criteria1
D)Criteria2

Question 7

What is the sum_range argument of: =SUMIF(A2:A64,"Green",C2:C64)?

A)C2:C64
B)"Green"
C)SUMIF
D)A2:A64

Question 8

Relative Standing measures ________.

A)the median of a dataset
B)the rank value of the quartiles of a dataset
C)the standard deviation of a dataset
D)the rank of one value with regard to other values in a dataset

Question 9

Which of the following is an allowable quart argument in a QUARTILE.EXC function?

A)4
B)0
C)2
D)5

Question 10

Which of the following functions determines the standard deviation of a sample?

A)STDEVA
B)VARA
C)STDEV.P
D)STDEV.S

Question 11

When using the Function Arguments Box, Excel will ________.

A)suggest criteria
B)not enter quotation marks in text criteria
C)suggest ranges
D)enter the quotation marks in text criteria, automatically

Question 12

Which function would you use to determine the number of occurrences of a particular value in a dataset?

A)BINS
B)DEVP
C)FREQUENCY
D)CORREL

Question 13

Which of the following values would demonstrate the strongest positive relationship while using the CORREL function?

A)0
B)0.5
C)-1
D)1

Question 14

What function returns the variance of a population?

A)VARA
B)VAR.P
C)VAR.S
D)VARPA

Question 15

What are the two arguments for the FREQUENCY function?

A)data_array, correlation_array
B)data_array, bins_array
C)data_array, set_value
D)data_array, outlier

Question 16

What would be the result of this formula: =AVERAGEIF(A2:A64,"Green",C2:C64)?

A)An average of the values in the C and A columns when the values in the B column are Green
B)There isn't enough information here to tell.
C)An average of the values in the A column when the values in the C column are Green
D)An average of the values in the C column when the values in the A column are Green

Question 17

The RANK.EQ function returns ________.

A)the rank of a value within a dataset, giving an average ranking to tied values
B)the rank of a value within a dataset, giving the highest ranking possible to ties
C)the rank of a value within a dataset, throwing out ties
D)the rank of a value within a dataset, giving the same ranking to tied values

Question 18

A college admissions office might use which of the following functions to offer scholarships to incoming student who are in the top 10% of their high school classes?

A)PERCENTILE.INC
B)PERCENTILE.EXC
C)PERCENTRANK.INC
D)PERCENTRANK.EXC

Question 19

What is the Criteria2 argument of: =COUNTIFS(A2:A64,"Blue",B2:B64,"Black")?

A)"Blue"
B)B2:B64
C)"Black"
D)A2:A64

Question 20

This is the first 4 lines of an excel list of 3082 records. Which formula counts the number of Gold Level Members in the entire list?

A)=COUNTIF(A2:A3083,"Gold")
B)=COUNTIFS(A2:A3083,"Gold")
C)=COUNTIF(E2:E3083,"Gold")
D)=COUNTIFS(A2:E3083,"Gold")

Question 21

The ________ function returns the variance of a sample of numerical data

A)VARPA
B)VAR.P
C)VARA
D)VAR.S

Question 22

Which function would you use to test for a relationship between students' attendance and their test scores?

A)CORREL
B)RELATE.S
C)RELATE.P
D)VARPA

Question 23

What is MS in an ANOVA summary report?

A)Mean of the sample squared
B)Maximum of squared value
C)Minimum of squared value
D)Multiplier of sample

Question 24

Which function returns the standard deviation of a population including logical values and text?

A)STDEVA
B)STDEV.P
C)STDEVPA
D)STDEV.S

Question 25

The simplest method of creating a ________ is with the Analysis Toolpak

A)frequency chart
B)histogram
C)standard deviation
D)distribution chart

Question 26

What is SS in an ANOVA summary report?

A)Summary statistics
B)Statistical score
C)Sum of squares
D)Square summary

Question 27

A(n)________ is a business intelligence tool which extrapolates future values based on current trends.

A)analysis of variance
B)forecast worksheet
C)standard deviation
D)frequency

Question 28

Which of the following is an Excel Add-in?

A)Analysis ToolPak
B)Frequency
C)Standard deviation
D)ANOVA

Question 29

________ is a measure of how two set of data vary simultaneously and is calculated by taking the average of each product of the deviation of a data point.

A)Covariance
B)Correlation
C)Causation
D)Period

Question 30

A(n)________ is a dataset that contains a manageable subset of a population.

A)population
B)sample
C)variance
D)standard deviation

Question 31

________ returns the forecasted values of a target date using exponential smoothing.

A)FORECAST.ETS.CONFINT
B)FORECAST.ETS
C)FORECAST.ETS.SEASONALITY
D)FORECAST.LINEAR

Question 32

What is the common name for the square root of the variance?

A)Correlation coefficient
B)P-value
C)Standard deviation
D)T-test

Question 33

Why is it important to analyze the relationships between a sample and full population?

A)To find out how many data points are necessary to be statistically valid
B)To help make more informed statements about a population
C)To bring diverging data into analysis
D)To limit variance

Question 34

Which of the following represents the number of data points in a sample minus one?

A)F ratio
B)Probability
C)Degrees of Freedom
D)Critical value

Question 35

What is F in an ANOVA summary report?

A)Frequency
B)Falling rate
C)F ratio
D)Fallibility reading

Question 36

________ is a measure of how two sets of data vary simultaneously.

A)Deviation
B)Covariance
C)Probability
D)Frequency

Question 37

A(n)________ is a visual depiction of a population distribution created by the Analysis ToolPak.

A)histogram
B)ANOVA
C)correlation coefficient
D)mu

Question 38

Which of the following is not an ANOVA calculation type?

A)Single-factor
B)Two-factor with replication
C)Two-factor without replication
D)Two wing

Question 39

Which of the following outputs from an ANOVA summary report is used to determine the significance of the F-Test?

A)F
B)dF
C)SS
D)F-crit

Question 40

________ determines how two sets of data from a sample vary simultaneously.

A)STDEV.S
B)COVARIANCE.S
C)CORREL
D)STDEV.P

Question 41

The ________ is the square root of the variance.

Question 42

The ________ is the probability of a population being similarity to a sample.

Question 43

The ________ function is best suited to calculate quartiles including quartiles 0 and 4.

Question 44

The ________ function returns the variance of a population.

Question 45

To count the number of Finance majors in a list of students and their college majors, use the ________ function.

Question 46

________ is a measure of how two sets of data vary simultaneously.

Question 47

A(n)________ is a visual display of tabulated frequencies.

Question 48

The ________ is an add-in program that provides statistical analysis tools.

Question 49

The ________ function returns a value's rank as a percent between 0% and 100%.

Question 50

The ________ function will use average values to determine the rank of a number within a list if tied values are present.

Question 51

The ________ function counts the number of cells in a range that meet multiple criteria.

Question 52

________ statistics help analyze differences between groups and relationships within groups of data.

Question 53

The ________ function identifies a value's rank within a list of values, omitting the next
rank in the case of tied values.

Question 54

________ is a statistical hypothesis test that helps determine if samples of data were taken from the same population.

Question 55

________ is the measure of how far the data sample spreads from the mean.

Question 56

In SUMIFS, AVERAGEIFS and COUNTIFS functions, you must have a(n)________ argument for each criteria_range argument.

Question 57

A histogram requires ________ to tabulate data and return a frequency distribution table.

Question 58

Sum_range is the argument in the ________ function that is added together if a single condition is met.

Question 59

________ is a measure of a dataset's dispersion or the difference between the highest and lowest values.

Question 60

The ________ function calculates the average, or arithmetic mean, of all cells in a range that meet a specific condition.

Question 61

Sort data in ________ order before beginning a forecast analysis.

Question 62

A quartile is a value used to divide a range into five equal groups.

Question 63

The CORREL function would be useful to determine the strength of a relationship between two variables.

Question 64

To determine how often a value occurs in a dataset, use the SUMIFS function.

Question 65

The VARPA function calculates the variance of a population for numerical values.

Question 66

The ________ function returns the length of the repetitive pattern Excel detects for the time series.

Question 67

Both RANK.EQ and RANK.AVG functions have the same arguments.

Question 68

The ________ function returns the forecasted values for a specific future target date using exponential smoothing.

Question 69

This formula will successfully count all green items in the range C2:C256.
=COUNTIF(C2:C256,green)

Question 70

Two or more conditions can be used with SUMIFS function.

Question 71

The Greek letter mu is the symbol for standard deviation.

Question 72

The arguments for COUNTIFS, SUMIFS, AVERAGEIFS are Range and Criteria.

Question 73

=AVERAGEIFS(C2:C64,A2:A64,"Blue",B2:B64,"Black")
Returns an average of Column C of the items which are either Blue or Black.

Question 74

________ returns the forecasted values for a specific future target date using exponential smoothing.

Question 75

Inferential statistics and Descriptive statistics are the same thing.

Question 76

The k argument in a PERCENTILE function is a value from 0 to 1.

Question 77

The FREQUENCY function determines the strength of the relationship between two data sets.

Question 78

The Significance argument in a PERCENTRANK function is the input for confidence intervals.

Question 79

The PERCENTRANK.INC function displays a value's rank as a percentile of a range, including 0% and 100%.

Question 80

If you have 10 years of SAT data and 10 years of Teacher pay data and you'd like to see a trend that might predict their behavior in the future, you would create a ________ Sheet.