CGS Midterm Exam Study Guide Chapter 1 1 What is the meaning of the following error messages a i b NAME c N A i d REF i e VALUE f i NUM i g DIV 0 a 1 149 25 a 42 Insufficient width in cell to display numerical data or formulas that result in negative date time prior to 1 1 1990 i Unrecognized text in a formula No answer Invalid cell reference Wrong argument type or operand Invalid numeric values in a formula or function i Division by zero 2 If you enter 1 149 25 in a cell exactly as shown w o an equal sign what value would result 3 If you enter 2 4 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 a Parentheses Ex 2 2 4 12 not 8 b Exponentiation Ex 3 2 3 3 8 24 c Multiplication and division left to right Ex 2 3 10 2 30 32 d Addition and subtraction left to right Ex 2 3 10 10 2 3 1 1 1 0 5 When writing formulas why is it preferable to use cell references rather than typing in values a Because cell referencing allows for copying a general formula rather than re typing it again and again If you must change the value of the referenced cell all formulas will automatically update b Absolute Cell Referencing E 3 only one is mixed cell reference 6 In the worksheet below cell A3 contains the formula A1 A2 Explain the most likely reason the value calculated appears incorrect B 10 C A 1 2 4 1 2 3 a It could be a rounding issue 1 4 2 4 3 8 rounded to 4 or 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 a B1 1 10 as a percentage so 1 110 11 8 What formula would you write to do each of the following a Add a range of numbers in cells A2 X2 b Find the largest value in cells C2 C8 c Find the smallest value in cells B2 through Z12 i sum a2 x2 i max C2 C8 i min B2 Z12 i average C1 C10 text i COUNT C1 C10 9 Define the following terms 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 excluding any that contain a Syntax The specific format of a function including the function name and the order of the arguments in the function b Arguments A function input For example the ROUND function contains two arguments a value and the number of decimal places as follows ROUND 2 22 0 c Algorithm A rule that governs how a function works An algorithm is a systematic set of procedures that the computer always steps through to calculate the results of a function 10 If the formula B 3 SUM C1 C5 is copied from cell A9 to cell C10 what is the resulting 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 formula a B 3 SUM E2 E6 G A 1 2 3 4 5 B C 2 1 2 1 2 4 6 3 8 4 5 10 D E 4 3 4 3 6 8 12 9 16 12 15 20 F 5 5 10 15 20 25 1 2 3 4 5 6 7 8 a B 1 A1 E13 12 What new formula results for each of the following if the formula is copied from cell C10 to cell a A1 A2 i C4 C5 b A 1 A2 i A 1 C5 c A1 A2 d A 1 A2 i A4 C5 i C 1 C5 13 What formula could you use to add up cell B1 from Sheet1 Sheet2 and Sheet3 assuming the worksheets are contiguous and in the workbook a 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 C1 that multiplies discount by cell A1 What new formula results if you copy this formula into cell C2 1 What formula could you write to calculate the mean of the following data set 2 5 4 3 1 2 7 a PRODUCT A1 Discount b PRODUCT A2 Discount Chapter 2 a AVERAGE 2 5 4 3 1 2 7 2 What is the median value of the data set given in Q1 a 3 MEDIAN 2 5 4 3 1 2 7 3 What is the mode of the data set given in Q1 a 2 MODE SINGL 2 5 4 3 1 2 7 4 The data set given in Q1 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 a The second data set likely contains numbers that spread over a larger range because it deviates further from the mean 5 In the chapter the original labor rate of inspectors was given as 35 per hour However due to a contract renegotiation this value is now 37 50 What algebraic expression could you use to determine the percent increase in labor costs a 37 50 35 35 new value old value old value 6 When using the Increase Decimal button on the toolbar the precise value in the cell is modified True or False a False 7 The formula ROUND 345 43 0 results in what precise value a 345 43 but it shows 345 in the cell 8 Write a formula to round up 63 34 to the nearest percent a ROUNDUP 63 34 0 b 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 the not equal to relational operator in Excel a a 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 C A B Price 1 Make Ford 2 15 837 12 883 GM 3 21 210 4 GM 27 837 Ford 5 20 432 Honda 6 Ford 7 24 552 Toyota 21 553 8 32 412 9 Lexus 23 134 10 Nissan 11 Total 199 850 12 a COUNTIF A1 A10 GM 2 less than 20 000 a COUNTIF A1 A10 20000 2 12 Using the worksheet shown in Q11 write a formula to determine the number of cars that cost 13 Using the worksheet shown in Q11 write a formula to determine the total value of all Ford cars a SUMIF A1 A10 Ford B1 B10 68 226 14 Explain the …
View Full Document
Unlocking...