f4

Suppose we want to change the formula SUM(A2:A10) to SUM(A$2:A$10). After

selecting A2:A10 we should click on the _____________ key.
trace precedents
You have been handed a spreadsheet that contains a formula in cell D10 that
computes Year 10 profits. You want to display all cells that are used to compute
Year 10 Profits. You should select which tool from the Ribbon (two-word answer)?
data validation
Again refer to the screenshot below. Suppose we would like to create a drop-down
box in cell E4 so that the user could enter a name by selecting the name from a list.
One way of accomplishing this would be via the _________ (two-word answer)
feature from the Ribbon.
=SUMIFS(Units_Sold,Month,G$2,Name,$F3)
See the screenshot below. What function can be used in cell G3 (and then copied
through the range G3:L9) to summarize the total number of units sold by month and
salesperson? Assume that the three columns of the dataset have been named
Name, Month, and Units_Sold.
=COUNTIFS(Name,$F3,Month,G$2)
Again refer to the screenshot below. What function can be used in cell G3 (and then
copied through the range G3:L9) to summarize the total number of transactions
(assume that each row in the dataset represents one transaction) by month and
salesperson? Again assume that the three columns of the dataset have been named
Name, Month, and Units_Sold.
Name Month Units Sold
Refer to the screenshot below. Suppose you want to use a pivot table to summarize
units sold by month, with each salesperson's name listed in a separate row and each
month listed in a separate column. Identify the fields that should appear in the Rows,
Columns, and Values portion of the Pivot Table Areas section.
sort by month then name
Again refer to the screenshot below. Suppose we want to use the Subtotal feature to
create a summary of our sales data that lists the total units sold by each salesperson
in January, followed by the total units sold by each salesperson in February, etc.
What sort order should be implemented before running the subtotals?
23
Suppose cell B10 contains the price of a stock in 5 months. Consider a 5 month
European call option with an exercise price of $40. The value of the call option in 5
months is $0 if the stock price in 5 months is <=$40. Otherwise the value of the call
option in 5 months is the Stock Price in 5 months - 40. How many of the following
formulas will yield the correct value of the call option?

• I. IF(B10>40,40-B10,0)
• II. IF(B10>40, B10-40,0)
• III. MAX(B10-40,0)
• IV. MIN( 0, 40-B10)
b
Cell F7 tells whether or not we own or not own a stock at the beginning of a day Cell
G7 gives the price of the stock at the end of the day. We decide whether to buy the
stock, sell the stock or take no action based on the following rule:

• If end price >=$50 and we own stock we will sell.

• If end price <=$35 and we do not own the stock we will buy.
• Otherwise we take no action.

Choose the formula that will correctly implement this strategy.

A. IF(AND(F7="not own",G7>=50),"sell",IF(AND(F7=" own",G7<=35),"buy","none"))

B. IF(AND(F7="own",G7>=50),"sell",IF(AND(F7="not own",G7<=35),"buy","none"))
C. IF(AND(F7="own",G7>=50),"sell",IF(AND(F7="not own",G7<=35),"sell","none"))
D. IF(F7="own",G7>=50),"sell",IF( (F7="not own",G7<=35),"buy","none"))
E. None of the above
f3
You want to average the numbers in the range B1:B30. You have named this range
of cells Data and wish to use this range name in your formula. After typing
Average( you should select the ______ key.
.20
Four relatives are going to give you money for your birthday. The amount each
relative gives you is equally likely to be any number between $0 and $200. The
chance that the total amount of money you receive is at most $300 is ______.
=COUNTA(A1:D100)
Suppose you want to count the number of nonblank cells in the cell range A1:D100.
What formula (using only a single Excel function) would you use?

Include the equal sign at the beginning—for example, =SUM(A1:A24)—but do not

include any unnecessary spaces or parentheses in your response.
flash fill
for the names listed below you would like to put each person's first name in Column
F and each person's last name in column G. The easiest way to do this is use the
_____________ feature.
name manager
We have created 50 range names and want to delete 10 of the range names. We
should go to the ______ button.
6
A company produces products at 2 plants each of which have a capacity of
producing 75 units. 50 units of each product must be shipped to each of three
customers. We know the cost of shipping a unit of the product from each plant to
each customer. Our goal is to minimize the total cost of shipping the needed units to
the customers. If we use the Excel Solver to try and minimize the total cost of
meeting customer demand we will need how many variable cells?
1
In the spreadsheet shown below, we computed profit earned by selling houses by
entering the formula =F8-E8 in cell G8 and copying it down to G9:G11. We used the
word open to denote a house that is not yet sold. Unfortunately, when a house has
not yet been sold our spreadsheet shows the #VALUE error, because you cannot
subtract the buy price from the word open. We would like to "error trap" Column G so
that when a house has been sold the correct profit has been computed and if the
house has not yet been sold a _ will be entered in Column G. How many of the
following formulas could be entered in cell G8 and copied down to G9:G11 to
achieve this goal?

• I. IFERROR(F8-E8,"_ ")
• II. ISERROR(F8-E8," _")
• III. IF(COUNT(G8)>0,F8-E8,"_")
• IV. IFERROR(F8-E8,_)
• V. IFERROR(E8-F8,"_ ")
=SUM('March:July'!F7)
Suppose you have listed Le Napoleon's monthly sales of pear tortes in a twelve-
sheet workbook. The first worksheet contains January sales, the second worksheet
February sales, etc. The pear torte sales are always listed in cell F7.

What multi-sheet (three-dimensional) formula would give the total pear torte sales for
the months March-July, inclusive? Assume that each worksheet is named with the
month name only, spelled out in full.

Include the equal sign at the beginning—for example, =SUM(A1:A24)—but do not

include any unnecessary spaces or parentheses in your response. Also do not
anchor any cells—i.e., do not use any $ characters.
6024
you are given the three dates shown below. If you add up the year in which each
date occurred and the month in which each date occurred what is your total? For
example, if a date were in August 2005 the date would contribute 2005 +8 to our
total sum.
the file tab
You are working with a file and want to save it as a .csv file, rather than .xlsx. Where
can you click to access this option?
the page layout button
You want to set up a worksheet so that only a certain range of cells will print when
the sheet is printed. Where can you click to access this option?
ctrl end
Which key combination selects the cell at the intersection of the last-used column
and last-used row on a worksheet?
the range a1:a5
Refer to the screenshot below, in which cell A1 is selected. You hold down the Ctrl
and Shift keys and press the down arrow once. Which cell(s) are now selected?
C
n the screenshot below, the New Comment button is about to be added to the Quick
Access Toolbar. What does this mean?

A. The button will move out of the Review tab and will instead be available through a
hotkey combination.
B. The button will stay on the Review tab and will also be available through a hotkey
combination.
C. The button will stay on the Review tab and will appear near the top left of the
Excel window, above the Ribbon. It is available no matter what tab is selected.
Select the range A1:A2 and fill down
See the screenshot below, in which the value 1 has been entered in cell A1 and 2 in
cell A2. The user wants to use Auto Fill to create the series 1, 2, 3, 4, 5... What
should the next step be?
Row 5 will be deleted, and the current row 6 will become row 5
Refer to the screenshot below, in which row 5 is selected. When Delete is clicked,
what will happen?
false
If you forget to create a pivot chart at the same time as a pivot table, you need to
start over and re-create the pivot table to be able to add a pivot chart.
true
If you have a pivot table and a pivot chart, each will respond to changes made in the
other; e.g., if you filter the pivot table to show only sales made in the East, the pivot
chart will automatically display only East sales as well.
c
When creating a chart from a selection of cells, how does Excel decide what to put
along the x-axis and what to put in the legend?

A. The data in the leftmost column goes along the x-axis and the remainder goes in
the legend.
B. The labels along the top determine what is appropriate to place in the legend.
C. Whatever there are more examples of goes along the x-axis, and whatever there
are fewer examples of goes in the legend.
false
Once you create and customize a chart, if you want to change the data that the chart
is based on (e.g., chart Quarter 4 sales rather than Quarter 3 sales), it is simplest to
start over.
circular
The value in cell A1 of a spreadsheet is needed to compute the value in cell E3. The
value in cell E3 is needed to compute the value in cell E10. The value in cell E10 is
needed to compute the value in cell A1. This spreadsheet contains a __________
reference.

Terms in this set (23)

=SUMIF (Name,"Jen",Dollars)
Assume we have named the data in column B as Name; the data in column C as
Date, and so on. What function would compute total revenue generated by Jen?
=COUNTIF (Product,"lip gloss")
For the data in the worksheet Makeup, what formula would determine total number
of transactions that involve lip gloss?
Name Product Dollars
You want to set up a pivot table to determine the total revenue generated by each
girl for sales of each product. You want each row of the table to give a breakdown of
the dollar amount that a given girl sells of each product.

Which field would you place in the Rows, Columns, and Values zones in the Areas
Section of the Pivot Table?
=RANK (E2,Units,1)
Still referring to the worksheet Makeup, what function, entered in cell H2 and copied
down, would determine where each transaction ranks in unit sales (with the lowest
number of units sold receiving a rank of 1)?
=SUM (Dollars)
Still referring to the worksheet Makeup, what formula using a range name would you
use to compute total revenue? Enter the function anywhere on the Makeup
worksheet, check that it works, and then copy and paste your function below.
create from selection
Still referring to the worksheet Makeup, what button on the Formulas tab (on the
Ribbon) would you use to most efficiently create the range name for each column?
1 and 2
Again refer to the screenshot below. What function can be used in cell D17 so that,
assuming that cell D16 will contain a date, the price of the product will be retrieved—
or, if no price is defined for the date entered, the text "not relevant"?

• I. =IF(D16>=D12,E12,IF(D16>=D11,E11,IF(D16>=D10,E10,IF(D16>=D9,E9,"not
relevant"))))
• II. =IF(D16<D9,"not
relevant",IF(D16<D10,E9,IF(D16<D11,E10,IF(D16<D12,E11,E12))))
• III. =IF(D16<=D12,E12,IF(D16>=D11,E11,IF(D16>=D10,E10,IF(D16>=D9,E9,"not
relevant"))))
• IV. =IF(D16>=D12,E12,IF(D16>=D11,E11,IF(D16<=D10,E10,IF(D16>=D9,E9,"not
relevant"))))
=CONCATENATE (B4,$C$1)
Refer to the screenshot below. Assuming that all of the people listed have an e-mail
address that is their first name followed by @winval.com, what function could be
entered in cell C3 and copied down to return each person's e-mail address?
134
The worksheet Lineups contains the number of minutes played by four basketball
lineups. For example lineup 1 played 4.4 minutes. Suppose you want to enter in cell
F11 a formula that can be copied down and creates the number of minutes in
numerical (e.g., 4.4) format. If your formula is to use three Excel functions what
would they be?

• I. FIND
• II. LEN
• III. LEFT
• IV. RIGHT
• V. VALUE
124
The worksheet Movies contains the price of movie DVDs. You want to enter a
formula in cell E8 that can be copied down to return the title of each DVD. If the
formula must use three Excel functions, what would they be?

• I. FIND
• II. LEN
• III. LEFT
• IV. RIGHT
• V. VALUE
=Today()+2
Which Excel formula will return the date two days from whatever today happens to
be (and no additional information)?
=Year (B2)
Suppose each cell in the range B2:B50 contains a date (such as 1/4/2004). You
would like cells C2:C50 to find the year of each corresponding date in column B.
What function (that could be copied down) would accomplish this?
=hour (B2)
Suppose each cell in the range B2:B50 contains a time (such as (14:32:52). You
would like cells C2:C50 to find just the hour of each corresponding time in column B.
What function (that could be copied down) would accomplish this?
134
suppose cell C2 contains the number of people showing up for an airline flight which
can seat 100 people. Which of the following formulas would return the number of
people who are "overbooked" and cannot get on the flight?

• I. IF(C2>100,C2-100,0)
• II. IF(C2>100,C2,0)
• III. IF(C2<100,0,C2-100)
• IV. IF(C2<=100,0,C2-100)
123
We have named the cell containing the height of a triangle h and the cell containing
the base of the triangle as b. Which of these formulas would yield the area of the
triangle?

• I. .5bh
• II. .5hb
• III. bh.5
• IV. (.5*h)b
=LARGE (B10:B500,34)+SMALL (B10:B500,10)
Suppose the cell range B10:B500 contains the salaries of all NBA players. Which of
the following is a way of computing the sum of the 34th largest and 10th smallest
salaries?
=INT ($C$2/C5)
Refer to the screenshot below, where we give the price of various types of candy.
Cell C2 contains the amount of money Vivian has to buy candy. What formula could
be entered in cell D5 (and then copied from D5 to D6:D8) to compute the number of
packages of each kind of candy Vivian can buy (assuming that she would choose to
buy only one kind of candy)
remove duplicates
Let's reconsider the data in worksheet Makeup. Your boss wants you to efficiently
compute for every combination of salesperson, product and location the total units
sold. To begin you must find every unique combination of sales person, product and
location which appears in the data. What tool listed on the ribbon would you use to
accomplish this goal?
sumifs
Once you have a listing of every unique combination of salesperson, product and
location, what Excel function would you use to compute the total sales for each
combination of salesperson, product and location?
=COUNT (A1:C10)
What formula would count how many numeric values are in the cell range A1:C10 of
a spreadsheet?
countblank
What function would count how many blank cells are in a given range?
Trace Dependents
You receive a spreadsheet model from Charlene in which cell A10 contains an
assumption that sales will grow 10% per year. What button on the ribbon (two-word
answer) can you use to determine which cells in the model depend on her
assumption?
Mean = 41; Standard Deviation = 31
Find (rounded to the nearest unit) the mean and standard deviation of the units sold
in the worksheet Makeup.