CGS 2518 Test 1 Conceptual Questions Ch 1 4 Chapter 1 1 What is the meaning of the following error messages Insufficient width in cell to display numerical data or formulas that result in negative date time prior to 1 1 1990 NAME Unrecognized text in a formula N A No answer REF Invalid cell reference NUM Invalid numeric values in a formula or function DIV 0 Division by zero 2 If you enter 1 149 25 in a cell exactly as shown without an equal ign what value would result 1 149 25 3 If you enter 2 4 10 in a cell exactly as shown what value would 4 List each of the following in order of precedence from 1 to 4 first result 42 to last Multiplication and division 3 Parentheses 1 Addition and subtraction 4 Exponentiation 2 5 When writing formulas why is it preferable to use cell references rather than typing in values It allows the user to easily update the spreadsheet without having to know exactly which formula or formulas contain the changed value 6 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 8 What formula would you write to do each of the following Add a range of numbers in cells A2 X2 i SUM A2 X2 Find the largest value in cells C2 C8 Find the smallest value in cells B2 through Z12 i MAX C2 C8 i MIN B2 Z12 Find the average value in cells C1 through C10 assuming blank cells will be ignored i AVERAGE C1 C10 Find the total number of values listed in cells C1 through C10 excluding any that contain text i COUNT C1 C10 9 Define the following terms Syntax the specific format of a function including the function name and the order of the arguments in the function Argument a function input Algorithm a rule that governs how a function works An algorithm is a systematic set of procedure hat the computer always steps through to calculate the results of a function If the formula B 4 SUM C1 C5 is copied from cell A9 to 10 cell C10 what is the resulting formula B 4 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 12 What new formula results for each fo the following if the formula is copied from cell C10 to cell E13 A2 B 1 A1 A2 i C4 C5 A 1 A2 i A 1 C5 A1 A2 i A4 C5 A 1 A2 i C 1 C5 13 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 Sheet1 B1 Sheet2 B1 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 discount A1 discount A2 Chapter 2 1 What formula could you write to calculate the mean of the following data set 2 5 4 3 1 2 7 Note that a resulting value is not required 2 5 4 3 1 2 7 7 2 What is the median of the data set given in Question 1 3 What is the mode of the data set given in Question 1 3 2 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 a The second set of data has values that occur further from the mean than those in Question 1 5 In the chapter the original labor rate for inspectors was given as 35 per hour However due to 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 25 6 When using the Increase Decimal button on the toolbar the precise value in the cell is modified True or False True 345 7 The formula ROUND 345 43 0 results in what precise value 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 a 10 What is the symbol for the nor equal to relational operator in Excel a 11 Review the following worksheet nd then use the COUNTIF function to write a formula that determines the munver of GM cars in the list COUNTIF A2 A10 GM 12 Using the worksheet shown in Question 11 write the formula to determine the number of cars that cost less than 20 000 COUNTIF B2 B10 20000 13 Using the worksheet sown in Question 11 write a formula to determine the total value of all ford cars SUMIF A2 A10 Ford B2 B10 15 17 18 14 Explain the difference between a what if analysis and Goal Seek by giving an example based on the worksheet in Question 11 For a what if analysis you could change the some of the prices of the cars and evaluate the total and for Goal Seek you could put what you want the total to be and tell excel to change the value f a certain cell to get that total Using the worksheet shown in Question 11 write a formula to determine the value of the third most expensive car LARGE B2 B10 2 16 If each car shown in Question 11is 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 B2 RANDBETWEEN 50 250 The formula RAND gives what result Each time you write it gives a different random value What formula could you write to average the values in cell A10 through A20 excluding blank cells rounded to the nearest 10 ROUND AVERAGE A10 A20 0 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 interger between 10 and 20 a RANDBETWEEN 10 20 Chapter 3 1 List and describe each of Tufte s five data graphics principles a Above all else show the data do not clutter the chart by adding unnecessary illustration or decoration Everything on the chart needs to have a reason fro being there b Maximize the data ink ratio refers to the proportion of the ink that is devoted to displaying the data versus the portion of a graphic that can be removed without losin th data c Erase non data ink related to maximizing …
View Full Document
Unlocking...