CGS2518 Exam 1 Study Guide Chapter 1 1 What is the meaning of the following error messages Number text doesn t fit in cell NAME Unrecognized text in formula N A No Answer REF Invalid Reference VALUE Wrong Argument Type NUM Invalid numeric values in formula 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 a The exact same numbers 3 If you enter 2 4 10 in a cell exactly as shown what value would result a 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 a It s easier to go back and change one cell that s referenced in multiple functions than to change each function and also cell references lock in certain rows columns 6 In the worksheet below Cell A3 contains the formula A1 A2 Explain the most likely reason the value calculated appears incorrect a The worksheet is only showing a rounded answer 7 Referring to the preceding worksheet if you wrote the formula B1 B10 what value would result assuming the displayed value is the precise value a 11 8 What formula would you write to do each of the following Add a range of numbers in cells A2 X2 SUM A2 X2 Find the largest value in cells C2 C8 MAX C2 C8 Find the smallest value in cells B2 Z12 MIN B2 Z12 Find the average value in cells C1 C10 assuming blank cells will be ignored AVERAGE C1 C10 Find the total number of values listed in C1 through C10 excluding any that contain text COUNT C1 C10 9 Define the following terms Syntax Specific name and order of a function Arguments The function s input Algorithm Defines the way in which the function behaves 10 If formula B 4 SUM C1 C5 is copied from cell A9 to cell C10 what is the resulting formula a B 4 SUM E2 E6 11 Refer to the following worksheet What formula should you write in cell B2 that can be copied down the column and across the row to complete the multiplication table a A2 B 1 12 What new formula results for each of the following if the formula is copied from cell C10 to cell E13 A1 A2 C4 C5 AS1 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 Sheet2 and Sheet 3 Assuming the worksheets are contiguous and in the same workbook a SUM Sheet1 B1 Sheet2 B1 Sheet3 B1 14 Cell B1 has been given the range name discount How would you write a new formula in cell C1 that multiplies discount by cell A1 What new formula results if you copy this formula into cell C2 a A1 Discount A2 Discount Chapter 2 1 What formula could you write to calculate the mean of the following data set 2 5 4 3 1 2 7 a AVERAGE 2 5 4 3 1 2 7 2 What is the median value of the data set given in question 1 a Median 2 5 4 3 1 2 7 3 What is the mode of the data set given in question 1 a MODE 2 5 4 3 1 2 7 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 different standard deviation of 2 5 What general differences would you expect to find between the two sets of data a The larger standard deviation means that the data values are dispersed wider from the mean of that group 5 In the chapter the original labor rate for inspectors was given as 35 per hour However due to a contract rengotiation 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 a 37 5 35 35 100 6 When using the Increase decimal button on the toolbar the precise value in the cell is modified True or False a False a 345 a a 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 a ROUND 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 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 GM cars on this list A B 1 Make Price 2 Ford 15 837 3 GM 12 883 4 GM 21 210 5 Ford 27 837 6 Honda 20 432 7 Ford 24 552 8 Toyota 21 553 9 Lexus 32 412 10 Nissan 23 134 11 Total 199 850 12 Using the worksheet show in question 11 write a formula to determine the number of cars that cost less than a COUNTIF A2 A10 GM 20 000 a COUNTIF B2 B10 20 00 13 Using the worksheet show in question 11 write a formula to determine the total value of all Ford Cars a SUMIF A2 A10 Ford B2 B10 14 Explain the difference between a what if analysis and Goal Seeking by giving an example based on the worksheet shown a What if to try out various values for the formulas in the sheet Goal Seek Will find the right input when you know the result you want EX What price would you have to charge for a ford car to make the total 200 000 15 Using the worksheet shown in question 11 write a formula to determine the value of the third most expensive car a LARGE B2 B10 3 16 If each car in the worksheet 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 function a B2 RAND 200 50 17 The formula RAND gives what result a A random value between 0 and 1 the nearest 10 a ROUND AVERAGE A2 10 A 20 1 18 What formula could you write to average the values in cells A10 through A20 Excluding blank cells rounded to 19 Write formula to determine the average price of only Ford vehicles using the worksheet a AVERAGEIF A2 A10 FORD B2 B10 20 Write a formula to generate a random integer value between 10 and 20 a RANDBETWEEN 10 20 Chapter 3 1 List and describe the significance of each of Tufte s five data graphics principles a 1 Above all else show data 2 Maximize the data ink ration within reason 3 Erase non date ink within reason …
View Full Document
Unlocking...