CGS Study Guide Chapter 1 Pages 79-811. What is the meaning of each of the following error messages? ######-The numbers and text can’t fit #NAME?-unrecognized text in the formula #N/A-No Answer #REF!-Invalid reference #VALUE!-wrong argument type #NUM!-invalid numeric values in the formula #DIV/0!-Divided by zero 2. If you enter 1,149+25 in a cell exactly as shown(without an equal sign), what value would result? 1,149+253. If you enter =2+4*10 in a cell exactly as shown, what value would result? 424. List each of the following operations in order of precedence, from 1 to 4(first to last), Multiplication and division, parentheses, addition and subtraction, exponentiation1) Parentheses 2) Exponentiation3) Multiplication and Division4) Addition and Subtraction 5. When writing formulas, why is it preferable to use cell references rather than typing in values? When you copy and paste across or down it speeds up the process, also easier to edit. 6. In the worksheet below, cell A3 contains the formula =A1+A2 . explain the most likely reason the value calculated appears incorrect. The answer is rounded up because of the excel rounding feature7. Referring to the preceding worksheet, if you wrote the formula =b1*110, what value would result( assuming the displayed value is the precise value)? 118. 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(C2:C8)d. Find the average value in cells C1 through C10, assuming blank cells will be ignored-= Average(C1:C10)e. Find the total number of values listed in cells C1 through C10, excluding any that contain text.- =Count(C1:C10)9. Define the following terms Syntax-specific name and order of a function Arguments- The functions inputs Algorithm-the way in which the function behaves 10. If the formula =$B$4-Sum(C1:C5) is copied from cell A9 to cell C10, what is the resulting formula? $b4-Sum(E2:E6)11. 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? =($A2*B$1)12. What new formula results from each of the following if the formula is copied from cell C10 to cell E13? a.=A1+A2- C4+C5b.=$A$1+A2- $A$1+C5c.=$A1+A2- $A4+C5d.=A$1+A2- C$1+C513. What formula could you use to add up cell B1 from Sheet1!, Sheet2!, and Sheet3!(assuming the worksheets are contiguous and in the same workbook)? =Sum(B1, Sheet2!B,Sheet3!B1)14. 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? =Product(A1*Discount) =Product(A2*Discount)Chapter 2 Pages 153- 1541. What formula could you write to calculate the mean of the following data set: 2,5,4,3,1,2,7?( note that resulting value is not required.) =AVERAGE(2,5,4,3,1,2,7)2. What is the median value of the data set given in question 1? =Median(2,5,4,3,1,2,7)3. What is the mode of the data set given in question 1? =MODE(2,5,4,3,1,2,7)4. The data set given in question 1 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? The larger standard deviation means that the data values are dispersed wider from the mean of that group.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.) Current-Historical/Historical6. When using the Increase Decimal button on the toolbar, the precise value in the cell is modified. True or False? False7. The formula= ROUND(345.43,0) results in what precise value? 3458. Write a formula to round up 64.34% to the nearest percent. =ROUND(63.34%,2)9. What is the symbol for greater than or equal to relational operator in Excel? >=10. What is the symbol for the not equal to relational operator in Excel? <>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 he worksheet shown in question 11, write a formula to determine the number of cars that cost less that $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 shown in question 11.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 in dollar 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? A random value between 0 and 1.18. What formula could you write to average the values in cells A10 through A20, excluding blank cells, rounded to the nearest 10? =ROUND(AVERAGE(A2:10:A:20),1)19. Write a formula to determine the average price of only Ford vehicles using the worksheet in Question 11. =AVERAGEIF(A2:A10,”Ford”,B2:B10)20. Write a formula to generate a random integer value between 10 and 20. =RANDBETWEEN(10,20)Chapter 3 Page 2141. List and describe the significance of each of Tufte’s five data graphics principles.1) Above all else show the data- Reminder not to clutter a chart by adding unnecessary illustration or decoration.2) Maximize the data-ink ratio, within reason-refers to the portion of the ink that is devoted to displaying the data versus the portion of graphic that can be removed without losing the data(want less ink to display more data)3) Erase non-data-ink, within reason- part of the chart that decorates more than informs4) Erase redundant data ink, within reason- ink that repeats information5) Revise and edit- Charts can be improved in the same way that writing is
View Full Document
Unlocking...