Page 1 Midterm Exam Study Guide Answers Spring 2014 CGS 2518 Chapters 1‐5 Answers to all Conceptual Review Chapter 1 1. What is the meaning of each of the following error messages? - ####### Insufficient Cell width - #NAME? Unrecognized text in formula - #N/A No Answer - #REF Invalid cell reference - #Value Wrong Argument type - #Num Invalid numeric values in a formula or function - #Div/0 Division by zero 2. If you enter 1,149+25, what value would result? The cell would display 1,149+25 3. If you enter =2+4*10 exactly as shown, what value would result? 42 4. List the orders of operations in order 1) Parenthesis () 2) Exponentiation 3) Multiplication & Division 4) Adding and subtracting 5. When writing formulas, why is it preferable to use cell reference rather than typing in values? Using cell references is preferable to directly inputting values into a formula because it allows the user to easily update the spreadsheet without having to know exactly which formula or formulas contain the changed value. 6. In the worksheet below, cell A3 contains the formula =A1+A2. Explain the most likely reason the value calculated appears incorrect. The most likely reason is that the format chosen for A1 and A2 will not show decimals. Therefore if cell A1 is “1.4” and cell A2 is “2.4”, the result in cell A3 is actually “3.8” which is displayed as “4” when rounding. 7. Referring to the preceding worksheet, if you wrote the formula =B1*110, what value would result (assuming the displayed value is the precise value)? 11 Page 2 What formula would you write to do each of the following? a. Add a range of numbers in cells A2:X2. =SUM(A2:X2) b. Find the largest value in cells C2:C8. =MAX(C2:C8) c. Find the smallest value in cells B2 through Z12. =MIN(B2:Z12) d. Find the average value in cells C1 through C10, ignoring blank cells =AVERAGE(C1:C10, 0) e. Find the total number of values in cells C1 :10, excluding any that contain text. =COUNT(C1:C10) 8. Define the following terms: syntax, arguments, and algorithm. Syntax The specific format of a function including the function name and the order of the arguments Arguments A function input (when putting a numbe r and how many decimal places) Algorithm A rule that governs how a function works. A systematic set of procedures that the computer always steps through to calculate the results of a function 9. If the formula =$B$4–SUM(C1:C5) is copied from cell A9 to cell C10, what is the resulting formula? =$B$4‐SUM(E2:E6) 10. Refer to the following worksheet. What formula would you write in cell B2 that can be copied down the column and across the row to complete the multiplication table? =SUM($A2*B$1) 11. What new formula results for each of the following if the formula is copied from cell C10 to cell E13? =A1+A2 =C4+C5 =$A$1+A2 =$A$1+C5 =$A1+A2 =$A4+C5 =A$1+A2 =C$1+C5 12. What formula could you use to add up cell B1 from Sheet1!, Sheet 2!, and Sheet 3! (assuming the worksheets are contiguous and in the same workbook)? =SUM(Sheet1!B1,Sheet2!B1,Sheet3!B1) 13. Cell B1 has been given the range name “discount”. How would you write a formula in cell C1 that multiplies discount by cell A1? What new formula results if you copy this formula into cell C2? =discount*A1 =discount*A2 Page 3 Chapter 2 1. What formula could you write to calculate the mean of the following data set: 2,5,4,3,1,2,7? =AVERAGE(2,5,4,3,1,2,7) 2. What is the median value of the set: 2,5,4,3,1,2,7 =MEDIAN(2,5,4,3,1,2,7) 3. What is the mode of the data set: 2,5,4,3,1,2,7 =MODE(2,5,4,3,1,2,7) 4. The data set: 2,5,4,3,1,2,7, has a standard deviation of 1.58 as compared with another data set that has the same mean but a standard deviation of 2.5. What general differences would you expect to find between the two sets of data? Since Standard deviation is the measure of range and distribution, the larger standard deviation would result in a larger range of values. 5. In the chapter, the original labor rate for inspectors was given as $35 per hour. However, due to a contract renegotiation, this value is now $37.50. What algebraic expression could you use to determine the percent increase in labor costs? (Note that a resulting value is not required.) =(37.50‐35)/35 6. When using the increase decimal button on the toolbar, the precise value in the cell is modified. True or false? False 7. The formula =Round(345.43,0) results in what precise value? 345 8. Write a formula to round up 63.34% to the nearest percent =ROUNDUP(63.34%,2) 9. What is the symbol for the greater than or equal to relational operator in Excel? >= 10. What is the symbol for the not equal to relational operator in Excel? <> Page 4 11. Review the following worksheet, and then use the COUNTIF function to write a formula that determines the number of GM cars on this list. =COUNTIF(A2:A10,"GM") 12. Using the worksheet shown in Question 11, write a formula to determine the number of cars that cost less than $20,000. =COUNTIF(B2:B10,"<20000") 13. Using the worksheet shown in Question 11, write a formula to determine the total value of all Ford cars. =SUMIF(A2:A10,"Ford",B2:B10) 14. Explain the difference between a What‐if analysis and Goal Seek by giving an example based on the worksheet for question 11 ? What if: to determine the outcome of changing one or more input values and evaluate the recalculated results. For example, you could determine how changing the price of a Ford would affect the “total” cell. Goal Seek: tool that allows you to work backward to determine the input required that will ensure a specific outcome. For example, you could use goal seek to determine what price a Ford needs to be to make the “total” value over $200,000. 15. Using the worksheet shown in Question 11, write a formula to determine the value of the third most expensive car. =LARGE (B2:B10,3) 16. If each car shown in Question 11 is marked up between 50 and 250 increments, what function could be used to randomly assign the amount to be added to the car price in this formula? =B2+ RANDBETWEEN(50,250) 17. The formula =RAND() gives what result? Gives a random number between 0 and 1 Page 5 18. What formula could you write to average the values in cells A10 through A20, excluding blank cells, rounded to the nearest 10?
View Full Document
Unlocking...