Start Chapter 1 1 What is the meaning of each of the following error messages A Column width of a cell is too narrow to display numeric data or a formula in a cell results in a negative date that is a date prior to 1 1 1900 dates will be discussed later in the chapter B NAME Unrecognized text in a formula C N A No answer can be found D REF Invalid cell reference E VALUE Wrong argument type or operand F NUM Invalid numeric values in a formula or function G DIV 0 Division by 0 2 The formula 1 100 25 is entered into a cell exactly as shown Will Excel be able to calculate a result based on this formula Explain No the comma must be removed before excel can calculate the result 3 If you enter 12 1 10 in a cell exactly as shown what value would result 2 order of operations 4 List each of the following operations in order of precedence from 1 to 4 first to last Addition and subtraction Parenthesis Multiplication and division Exponentiation a Parenthesis b Exponentiation c Multiplication and division d Addition and subtraction 5 When you write a formula why should you use cell references rather than typing in values directly into the formula Because using cell references allows you to easily update the spreadsheet 6 In the worksheet below cell A3 contains the formula A1 A2 Explain the most likely reason the value calculated appears to be incorrect The most likely reason is that the format chosen for A1 and A2 will not show decimals Therefore if cell A1 is 1 5 and cell A2 is 1 5 the result in cell A3 is actually 3 7 Referring to the preceding worksheet if you wrote the formula B1 5 what value would result assuming the displayed value is the precise value 5 8 What formula would you write to do each of the following A Add a range of numbers in cells B2 B12 SUM B2 B12 B Find the largest value in cells C2 F2 MAX C2 F2 C Find the smallest value in cells A1 through X10 MIN A1 X10 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 A1 through F1 excluding any that AVERAGE C1 C10 contain text COUNT A1 F1 9 Define the following terms the arguments A Syntax The specific format of a function including the function name and the order of B Arguments A function input C 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 10 If you copy the formula B 4 SUM C1 C5 from cell A9 to cell B11 what is the resulting formula B 4 SUM D3 D7 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 of the following formulas if you copy it from cell C10 B 1 A2 to cell E12 A A1 A2 B A 1 A2 C A1 A2 D A 1 A2 C3 C4 A 1 C4 A3 C4 C 1 C4 13 What formula could you use to add up cell C1 from Sheet1 Sheet2 and Sheet 3 assuming the worksheets are adjacent and in the same workbook Sheet1 C1 Sheet2 C1 Sheet3 C1 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 the formula into cell C2 A discount A1 B discount A2 Start Chapter 2 1 What formula could you write to calculate the mean of the following data set 4 10 8 6 2 4 14 Note that a resulting value is not required AVERAGE 4 10 8 6 2 4 14 2 What is the median value of this data set MEDIAN 4 10 8 6 2 4 14 3 What is the mode of this data set MODE 4 10 8 6 2 4 14 4 The data set 4 10 8 6 2 4 14 has a standard deviation of 4 14 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 data set in the question would have a larger range of values than the other data set since the standard deviation is larger meaning the data varies more from the average 5 In the chapter the original labor rate for QC inspectors was 35 per hour However due to a contract renegotiation this value is now 40 What mathematical expression could you use to determine the percent increase in labor costs Note that a resulting value is not required 40 35 35 6 When you use the Increase Decimal button in the Number group on the HOME tab the precise value in the cell is modified True or false False 7 The formula ROUND 710 52 1 results in what precise value 710 8 Write a formula to round down 63 34 to the nearest percent ROUNDDOWN 63 34 2 9 What is the symbol for the less 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 Ford cars on this list COUNTIF A2 A10 Ford 12 Using the worksheet shown with the Ford cars write a formula to determine the number of cars that cost more than 20 000 COUNTIF B2 B10 20 000 13 Using the worksheet shown in Question 11 write a formula to automatically determine the total value of all GM cars This formula should work even if the data is later modified SUMIF A2 A10 GM 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 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 least expensive car SMALL B2 B10 3 16 If each car shown in Question 11 is marked up between 100 and 200 in dollar increments what function could be used to randomly assign the amount to be added to the car price in …
View Full Document
Unlocking...