Ch11. Q:what is the meaning of each of the following error messagesa. #####: 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 formulac. #N/A: no answerd. #REF!: invalid cell referencee. #VALUE!: wrong argument type or operandf. #NUM!: invalid numeric values in a formula or functiong. #DIV/0!: division by zero2. If you enter “1,149+25” exactly as show(without the equal sign), what value would resulta. What is in the quotes3. If you enter “=2+4*10” in a cell exactly as shown, what value would result?a. 424. List each of the following operations in order of precedence, from 1 to 4(first to last)a. Multiplication and division: 3b. Parentheses(): 1c. Addition and subtraction: 4d. Exponentiation: 25. 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 value6. 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. 118. What formula would you write to do each of the following?a. Add a range of numbers in cells A2:X2i. =SUM(A2:X2)b. Find the largest value in cells C2:C8i. =MAX(C2:C8)c. Find the smallest values in cells B2 through Z12i. =MIN(B2:Z12)d. Find the average values in cells C1 through C10, excluding any that contain texti. =AVG(C1:C10)e. Find the total number of values listed in cells C1 through C10, excluding any that contain texti. =COUNT(C1:C10)9. Define the following termsa. Syntax: the specific format of a function, including the function name and the order of the arguments in the functionb. 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 function10. If the 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 would you write in cell B2 that can be copied down the column and across the row to complete the multiplication table?a. =B$1*$A212. What new formula results for each of the following if the formula is copied from cell C10 to cell E13?a. =A1+A2i. =C4+C5b. =$A$1+A2i. =$A$1+C5c. =$A1+A2i. =$A4+C5d. =A$1+A2i. =C$1+C513. 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?a. =A1*B1b. =A2*B2Ch21. 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) = 33. What is the mode of the data set in question 1?a. =MODE.SNGL(2,5,4,3,1,2,7) = 24. 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 areb. 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)a. false7. The formula =ROUND(345.43,0) results in what precise value?a. 3458. Write a formula to round up 63.34% to nearest percenta. =ROUNDUP(63.34%,2)9. What is the symbol for the greater than or equal to relational operator in excel?a. >=10. What is the symbol for the not equal to relational operator?a. <>11. Write a countif to determine the number of GM cars in the dataa. =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 carsa. =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 sheetsa. 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 cara. =LARGE(B2:B10,3)16. Write a formula to determine how much a mark up between 50 and 250 would bea. =B2+RANDBETWEEN(50,250)17. The formula =rand() gives what resulta. A random number between 0 and 118. **What formula could you write to avg the values in cells A10 through A20, excluding blank cells, and rounded to nears 10a. =ROUND(AVERAGE(A2:A10),-1)19. Write a formula to determine the average price of only ford vehicles using this worksheeta. =AVERAGEIF(A2:A10,"=ford",B2:B10)20. Write a formula to generate a random integer value between 10 and 20a. =RANDBETWEEN(10,20)Ch3 1. List and describe the significance of each of Tufte’s five data graphics principlesa. “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, and interpretb. “Maximize the data-ink ratio”i. This refers to portion of the chart that shows the data verse the portion of a graphic that can be removed without losing the data. For example in column chart it uses a lot of ink to display datac.
View Full Document
Unlocking...