CGS 2518 Exam 1 Study Guide Chapter 1 1 What is the meaning of each of the following error messages 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 Excel cannot calculate 1 100 25 because it has a comma Excel will display a message box that asks you if you want to change the formula to 1100 25 The correct formula 1100 25 will result in a value of 44 Using commas in formulas will become more problematic if your formula uses a Function because a comma indicates the beginning of the next argument 3 If you enter 12 1 10 in a cell exactly as shown what value would result 4 List each of the following operations in order of precedence from 1 to 4 First to Last 2 1 Parentheses 2 Exponentiation 3 Multiplication Division 4 Addition Subtraction 5 When you write a formula why should you use cell references rather than typing in values directly into the formula You should use cell references instead of directly typing in values because you can easily update the spreadsheet without having to know exactly which formula or formulas contain the value that changed Formulas that use cell references will automatically reflect any changes made to the cells in the formula 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 why the value in A3 appears to be incorrect is because the worksheet selected a format that does not show decimals However this does not change the Precise Value the number of significant digits that are stored inside of the cells For example if the value in A1 is 1 7 and the value in A2 is 1 4 the resulting value in A3 is actually 3 1 but due to the formatting in the worksheet the value in A3 is rounded down to 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 though X10 MIN A1 X10 d Find the average value in cells C1 through C10 assuming blank cells will be ignored AVERAGE C1 C10 0 e Find the total number of values listed in cells A1 through F1 excluding any that contain text COUNT A1 F1 9 Define the following terms Syntax The specific format of a function including the function name and the order of the arguments Arguments A function input when putting a number 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 10 If you copy the formula B 4 SUM C1 C5 from A9 to 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 C3 C4 b A 1 A2 A 1 C4 c A1 A2 A3 C4 d A 1 A2 C 1 C4 13 What formula could you use to add up cell C1 from Sheet1 Sheet 2 and Sheet 3 Assuming the worksheets are contiguous and in the same workbook SUM 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 this formula into cell C2 discount A1 discount A2 1 What formula could you write to calculate the Mean of the following data set Chapter 2 4 10 8 6 2 4 14 AVERAGE 4 10 8 6 2 4 14 2 What is the Median value of the set 4 10 8 6 2 4 14 MEDIAN 4 10 8 6 2 4 14 3 What is the Mode of the data set 4 10 8 6 2 4 14 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 while another data set has the same mean but has a standard deviation of 2 5 What general differences would you expect to find between the two sets of data Standard Deviation measures the variation and dispersion of a set of values Smaller Standard Deviation Smaller range of values Larger Standard Deviation Larger range of values STDV of 4 14 The values are further away from the mean bigger distribution curve STDV of 2 5 The values are closer to the mean smaller distribution curve 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 40 What algebraic expression could you use to determine the percent increase in labor costs Note that a resulting value is not required 40 35 35 The algebraic expression is 6 When using the Increase Decimal button on the toolbar the Precise Value in the cell is modified True or false False Increase Decimal has no effect on the precise value in the cell 7 The formula ROUND 710 52 1 results in what precise value 710 8 Write a formula to round up 63 34 to the nearest percent ROUNDUP 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 in Question 11 write a formula to determine the number of cars that cost more than 20 000 COUNTIF B2 B10 20000 SUMIF A2 A10 GM B2 B10 13 Using the worksheet shown in Question 11 write a formula to determine the total value of all GM cars This formula should work even if the data is modified later 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 Analysis The process of changing the values of cells to evaluate what effect those changes have on the outcome of the formulas in the worksheet Example You could change the price of a GM to see how what effect it would have on cell B11 the total value …
View Full Document
Unlocking...