Ch1 1 Q what is the meaning of each of the following error messages a Insufficient width in cell to display numerical data or formulas that result in negative date time prior to 1 1 1900 b NAME unrecognized text in a formula c N A no answer d REF invalid cell reference e VALUE wrong argument type or operand f g DIV 0 division by zero NUM invalid numeric values in a formula or function 2 If you enter 1 149 25 exactly as show without the 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 What is in the quotes a 42 a Multiplication and division 3 b Parentheses 1 c Addition and subtraction 4 d Exponentiation 2 5 When writing formulas why is it preferable to use cell references rather than typing in values a Because it allows the user to easily update the spreadsheet without having to know exactly which formula or formulas contain the changed value 6 In the worksheet below cell A3 contains the formula A1 A2 Explain the most likely reason the value calculated appears incorrect a Since no decimal place show in the cell there could be a rounding problem 7 Refering to the preceding worksheet if you wrote the formula B1 110 what value would result assuming the displayed value is the previse value a 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 values in cells B2 through Z12 i i i SUM A2 X2 MAX C2 C8 MIN B2 Z12 i AVG C1 C10 text i COUNT C1 C10 9 Define the following terms d Find the average values in cells C1 through C10 excluding any that contain text e Find the total number of values listed in cells C1 through C10 excluding any that contain formula a B 4 SUM E2 E6 a B 1 A2 E13 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 a A1 B1 b A2 B2 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 like 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 4 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 12 What new formula results for each of the following if the formula is copied from cell C10 to cell 13 What formula would you use to add up cell B1 from Sheet1 Sheet2 and Sheet3 a SUM B1 Sheet2 B1 Sheet3 B1 14 If cell 1 is given the 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 C2 Ch2 1 What formula could you write to calculate the mean of the following data set a average set of data 2 What is the median value of the data set in question 1 a MEDIAN 2 5 4 3 1 2 7 3 3 What is the mode of the data set in question 1 a MODE SNGL 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 a They vary from each other on how close to each other they are b A large standard deviation indicates that the data points are far from the mean and a small standard deviation indicates that they are clustered closely around the mean 5 In the chapter the original labor hour rate for inspectors was given at 35 per hours However due to a contract renegotiation this value is now 37 5 What algebraic expression could you use to determine the percent increase in labor costs a new old old 6 When using the increase decimal button on the toolbar the precise value is modified T F 7 The formula ROUND 345 43 0 results in what precise value 8 Write a formula to round up 63 34 to nearest percent a 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 a false a 345 a a 11 Write a countif to determine the number of GM cars in the data a COUNTIF A2 A10 GM 12 Using previous worksheet determine number of cars costing less than 20000 a COUNTIF B2 B10 20 000 13 Using previous worksheet determine total value of all ford cars a SUMIF A2 A10 ford B2 B10 14 Explain the difference between a what if and a goal seek using by giving an example using previous sheets a what if analysis means simply to determine the outcome of changing one or more input values and evaluate the recalculated results b Goal Seek allows you to specify the outcome you want and which input value you want to vary and Excel automatically calculates the solution 15 the worksheet write a formula to determine the value of the third most expensive car a LARGE B2 B10 3 16 Write a formula to determine how much a mark up between 50 and 250 would be a B2 RANDBETWEEN 50 250 17 The formula rand gives what result a A random number between 0 and 1 cells and rounded to nears 10 a ROUND AVERAGE A2 A10 1 18 What formula could you write to avg the values in cells A10 through A20 excluding blank 19 Write a formula to determine the average price of only ford vehicles using this 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 Ch3 1 List and describe the significance of each of Tufte s five data graphics principles a Above all else show the data i Reminder not to clutter a chart by adding unnecessary illustration or decoration Everything on the chart needs to have a reason of being there or else it becomes difficult to read use …
View Full Document
Unlocking...