Chapter 3 'Quick Check' Questions and Answers

Cell B2 contains the formula =SUME(A1:A100) with the name of SUM misspelled as SUME. What error value will appear in the cell?

#NAME?

If 4/30/2017 and 5/31/2017 are the initial values, what are the next two valuse autofill will insert

6/30/2017, 7/31/2017

Convert the following equation into an excel formula where the radius value is stored in cell E31 and the value of Pi is stored in cell D12: area = (pi) * radius^2

=(D12*(E31*E31))

The range of a set of values in defined as the maximum value minus the minimum value. Write a formula to calculate the range of values in the range Y1:Y100

=MAX(Y1:Y100)

Write the formula to display the current date and time.

=NOW()

In EXCEL, the PI() function returns the decimal value of pi. Rewrite your answer for the previous formula using this function.

=PI(E31*E31)

You want to take out a loan for $130,000. The interest on the loan is 5 percent compounded monthly. You intend to pay back the loan in 20 years. Write the formula to calculate the monthly payment required to pay off the loan under those conditions.

=PMT(0.05/12, 12*20, 130,000)

If cell R10 contains the formula =$R$1+$R$2, which is then copied to cell S20, what formula is entered in cell S20?

=R1+R2

If cell R10 contains the formula =R1+R2, which is then copied to cell S20, what formula is entered in cell S20?

=S11+S12

You need to reference cell Q57 in a formula. What is its relative reference? What is its absolute reference? What are the two mixed references?

Absolute cell reference is $Q$57, and the mixed references are $Q57 and Q$57

Explain the difference between the COUNT function and the COUNTA function.

Count: counts the number of cells with numeric values in a range. Counta: counts the number of cells containing data (letters or numbers) in a range. Counta counts the number of non-blank cells in a range. Counta will therefore exclude any empty cells, which would be included if the count function were used.

Stephen is entering hundreds of temperature values into an excel worksheet for a climate research project, and he wants to speed up data entry by freezing point values as blanks rather than typing zeros. Explain why this will cause complications if he later tries to calculate the average temperature from those data values.

Excel ignores blank cells, so when he tries to get an average temperature, it won't be correct because part of the data won't be calculated.

What is the difference between a what-if analysis by trial and error and by Goal Seek?

Goal Seek automates the trial-and-error process by allowing you to specify a value for a calculated item, which Excel uses to determine the input value needed to reach that goal. Trial and error requires some guesswork as you estimate which values to change and by how much.

If cell Q3 is greater than cell Q4, you want to display "OK"; otherwise, you want to display "RETRY". Write the formula that accomplishes this.

IF(Q3>Q4, "OK", "Retry"

What financial function do you use to determine how many payment periods are required to pay off a loan?

NYPER

Write a formula to return the middle value from the values in the range Y1:Y100

fx=MEDIAN(Y1:Y100)

Write a formula to round the value in cell A5 to the fourth decimal place

fx=ROUND(A5,4)