CGS2518 Midterm Chapters 1 5 Study Guide Chapter 1 Conceptual Review 1 What is the meaning of each of the following error messages insufficient width of cell to display numerical data NAME unrecognized text in formula N A no answer REF invalid cell reference VALUE wrong argument type NUM invalid numerical values 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 1 149 25 would be seen in the given cell 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 1 Parentheses 2 Exponentiation 3 Multiplication and division 4 Addition and subtraction Remember PEMDAS 5 When writing formulas why is it preferable to use cell references rather than typing in values Cell referencing allows the user to easily update the spreadsheet without having to know exactly which formula or formulas contained the changed value By explicitly listing data inputs which have the potential to vary in their own cells and referencing those cells in formulas you can be secure in the knowledge that all calculations requiring those values will be automatically updated 6 In the worksheet below cell A3 contains the formula A1 A2 Explain the most likely reason the value calculated appears incorrect 1 10 2 4 It is most likely 1 something and 2 something and the format that was chosen does not show the decimals it is not the precise value 7 Referring to the preceding worksheet if you wrote the formula B1 110 what value would result 11 8 What formula would you write to do each of the following Add a range of numbers in cells A2 X2 Find the largest value in cells C2 C8 Find the smallest value in cells B2 Z12 Average value in cells C1 C10 excluding blanks Total number of values in cells C1 C10 excluding text SUM A2 X2 MAX C2 C8 MIN B2 Z12 AVERAGE C1 C10 0 COUNT C1 C10 9 Define the following terms syntax arguments algorithm Syntax the specific format of a function including the function name and order of the arguments in the function Arguments a function input Algorithm a rule that governs how a function works a systematic set of procedures that the computers always steps through to calculate the results of a function 10 If the formula B 4 SUM C1 C5 is copied from cell A9 to 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 SUM A2 B 1 12 What new formula results for each of the following if the formula is copied from cell C10 to E13 A1 A2 C4 C5 A 1 A2 A 1 C5 A1 A2 A4 C5 A 1 A2 C 1 C5 13 What formula could you use to add up cell B1 from Sheet1 Sheet 2 and Sheet3 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 1 that multiplies discount by cell A1 What new formula results if you copy this formula into cell C2 discount A1 discount A2 Chapter 2 Conceptual Review 1 What formula could you write to calculate the mean of the following date set 2 5 4 3 1 2 7 Note that a resulting value is not required MEAN 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 when put in ascending order 3 3 What is the mode of the data set given in question 1 MODE 2 5 4 3 1 2 7 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 standard deviation is a measure of how widely the data values are dispersed from the arithmetic mean There is going to be a wider range of value 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 no 37 50 What algebraic expression could you use to determine the percent increase in labor costs 37 50 35 00 35 00 6 When using the increase decimal button on the toolbar the precise value in the cell is modified True or false False 7 The formula ROUND 345 43 0 results in what precise value 345 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 10 What is the symbol for 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 the worksheet show in question 11 write a formula to determine the number of cars that cost less than 20 000 COUNTIF B2 B10 20 000 13 Using the worksheet show 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 Performing a what if analysis means simply to determine the outcome of changing one or more input values and evaluates the recalculated results However if you want to change an input value in order to reach a specified output you would use Goal Seek Goal Seek enables you to specify the outcome you want and which input value you want to vary automatically calculating the changed input value 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 show 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 SUM B2 RANDBETWEEN 50 250 17 The formula RAND gives what result The RAND function returns 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 A10 A20 0 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 …
View Full Document
Unlocking...