Chapter 6 Review Questions and Answers

1. a) What formatting does a newly inserted row contain? It contains the same formatting as the row next to it

b) What formatting does a newly inserted column contain? It contains the same column as the one next to it.

2. The formula =SUM(C3:C22) is entered in cell C24 and used to sum the values in cells C3 through C22.a) If a row is inserted directly above row 20, what must be done in order to include the new cell in

the sum? Nothing has to be done, excel inserted the row automatically b) If a row is inserted directly above row 24, what must be done to include the new cell in the

sum? Nothing, excel will automatically add that on the function.c) If row 20 is deleted, what must be done to the formula so that the deleted cell is no longer in

the range? Nothing, a function will automatically delete it.

3. Using functions, write a formula to calculate:a) the sum of the values stored in cells B4, B5, B6, and B7. =SUM(B4:B7)b) the sum of the values stored in cells B4, C4, D4, and E4. =SUM(B4:E4)c) the average of the values stored in the column of cells D7 through D35. =AVERAGE(D7:D35)d) the average of the values stored in the row of cells F3 through J3. =AVERAGE(F3:J3)e) the maximum value stored in the range of cells D4 through Y5. =MAX(D4:Y5)f) the minimum value stored in the range of cells C1 through C9. =MIN(C1-C9)

4. What is the difference between a relative cell reference and an absolute cell reference? A relative cell changes after u drop the fill handle. Lock in the cell so it does not move

5. List two advantages of using the Insert Function dialog box to insert the name of a function in a formula instead of typing the formula. It shows you what function, so you don’t make a mistake.

6. Why is it important to check the range placed in the SUM function when using FORMULAS > AutoSum? Cause it might get the wrong range.

7. Using functions, write a formula to calculate:a) the sum of the values in cells C5, C6, C7, C8, and C9 rounded to 2 decimal places. -

=ROUND(SUM(C5:C9),2)b) the sum of the values in cells B5, C5, D5, and E5 rounded to the nearest integer.

=ROUND(SUM(B5:E5),0)c) the average of the values in cells A1, A2, A3, B1, B2, and B3 rounded to 1 decimal place.

=ROUND(AVERAGE(A1:B3),1)

8. What will be displayed by the following formulas if cell D4 stores a value of 30 and cell E7 stores a value of –12?a) =IF(D4<=E7, 10, 20)= False 20b) =IF(E7*D4<-5, E7, D4) False - 12

c) =IF(D4–42=E7, D4*2, E7*3) false 60

9. Using functions, write a formula to:a) display 50 if the value stored in D20 equals the value in C70, or 25 if they are not equal.

=IF(D20=C70,50,25)

b) display the value in B40 if the sum of the range of cells C20 to C30 exceeds 1000, otherwise display a 0.c) display the value of R20*10 if R20 is less than 30, otherwise display the value in R20.

=IF(R20<30,R20*10,R20)

10. Write formulas using the IF function for each of the following:a) if B3 is less than or equal to C12 display Low; if greater than, display High.

=IF(B3<=C12,"low","high")

b) if A5 is equal to Z47 display Jonathan; if not equal to, display Judith. IF(A5=Z47,”Jonathan”,”Judith”)

11. Explain why it would be a good idea to change sheet names from Sheet1, Sheet2, and so forth in a workbook with multiple data sheets.

12. List two other “What If?” questions that can be answered using the Charity Fundraiser workbook.