Chapter 1 Conceptual Review Answers 1 What is the meaning of each of the following error messages Insufficient width to display value NAME Unrecognized text in a formula N A No answer REF Invalid cell reference VALUE Wrong argument type of operand 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 sign what value would result The values appears as text 1 149 25 3 If you enter 2 4 10 in a cell exactly as shown what value would result 42 4 List each of the following operations in order of precedence from 1 to 4 first 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 If input values are later updated they need only be modified in one place so that any subsequent formulas that use this value via a cell reference are automatically updated 6 In the worksheet below cell A3 contains the formula A1 A2 Explain the most likely reason why the value calculated appears incorrect Values displayed are not the precise values stored 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 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 assuming blank cells will be ignored e Find the total number of values listed in cells C1 through C10 including any that contain text AVERAGE C1 C10 COUNT C1 C10 arguments algorithm 9 Define the following terms syntax arguments and algorithm Each function has its own syntax that specifies the function name and order of the The function inputs are referred to as arguments A function always behaves in the same way according to the rules programmed into the function These rules methods of calculating the solution are referred to as a function s 10 If the formula B 4 SUM C1 C5 is copied from cell A9 to cell C10 what will the resulting formula be B 4 SUM E2 E6 11 Refer to the following worksheet Write a formula in cell B2 that can be copied down the column and across the row to complete the multiplication table 12 What new formula would result for each of the following if the formula was copied from cell C10 A2 B 1 to cell E13 a A1 A2 C4 C5 b A 1 A2 A 1 C5 c A1 A2 A4 C5 d A 1 A2 C 1 C5 13 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 Sheet3 B1 or 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 the discount by A1 What new formula would result if you copied this formula into cell C2 A1 discount copied into C2 adjusts to A2 discount 1 What formula could you write to calculate the mean of the following data set 2 5 4 3 1 2 7 Chapter 2 Conceptual Review Answers Note that a 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 3 What is the mode value 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 The second data set has a larger standard deviation indicating values are more widely distributed thus more high low values away from the mean 5 In the chapter the original labor rate for inspectors was given as 35 per hour However because of 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 6 When using the Increase Decimal button on the toolbar the precise value in the cell is modified required 37 50 35 35 True or False False 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 54 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 345 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 Ford cars SUMIF A2 A10 FORD B2 B10 13 Using the worksheet shown in Question 11 write a formula to determine the total value of all 14 Explain the difference between a what if analysis and Goal Seek by giving an example based on the worksheet shown in Question 11 A what if analysis would be changing one or more of the input values on the sheet such as the price of the first Ford car once this is changed the resulting total should automatically update A Goal Seek analysis would allow you to determine what the needed input value to get a specified output For example what would the price of the first Ford have to be to get a total cost of all makes of 200 000 15 Using the worksheet shown in Question 11 write a formula to determine the value of the third 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 this car price in this most expensive car LARGE B2 B10 3 formula B2 B2 RANDBETWEEN 50 250 17 The formula RAND gives what result A random value between 0 and 1 cells rounded to the nearest 10 ROUND AVERAGE A10 A20 1 Question 11 AVERAGEIF A2 A10 FORD B2 B10 18 What formula could you write to average the values in cells A10 through A20 excluding blank 19 Write a formula to determine the average price of only Ford vehicles using the worksheet in 20 Write a formula to generate a random integer value between 10 and 20 RANDBETWEEN 10 20 Chapter …
View Full Document
Unlocking...