Excel Formulas/Functions

Cell value references should always be used when possible to avoid having to make multiple updates when one key value changes.

• True
• False

The most common way to identify a cell reference is 

• $D2
• $D$2
• D$2
• D2$

When using VLookUp, use _______________ if you want to look for approximate matches rather than an exact match.

• True
• False

The LOOKUP function in Excel is used to search one column of data and find data in the _______ row

• Corresponding
• Value

Which of the following summary functions can you perform on a categorical variable (e.g. Employee ID)?

• Sum

• Average

• Count or distinct count

• Correlation

Of all active and resigned employees, what percentage are resigned Technical people?

• 11.86%

• 2.82%

• 100%

• 1.13%

Tyrone would like to use a function that returns a value that meets criteria in the second row of a table in his spreadsheet. Which function should he use?

• INDEX

• HLOOKUP

• MATCH

• VLOOKUP

An Excel table of text, numbers, or values that can be used in a formula is referred to as a/an:

• argument

• criterion

• table

• table array

Which is an example of a correctly written nested function?

• =PROPER(RIGHT(B4,LEN(B4)-FIND(" ", B4)))

• =PROPER[RIGHT(B4,LEN,B4) -FIND(" ",B4))]

• =PROPER((RIGHT(B4,LEN(B4-FIND(" ", B4)))

• =(PROPER(RIGHT(B4,LEN(B4)-FIND(" ",B4)))

A veterinarian collected data on the weights of 1,000 cats and dogs treated at a veterinary clinic. The weight of each animal was classified as either healthy, underweight, or overweight. The data are summarized in the table. Based on the data in the table, which of the following is the most appropriate type of graph to visually show whether a relationship exists between the type of animal and the weight classification?

• Back-to-back stemplots

• Scatterplot

• Side-by-side boxplots

• Segmented bar chart

• Dotplot