CGS2518 CHAPTER 1 ######-insufficient width #Name?-unrecognized text in formula # DIV/0-division by zero #REF!-invalid cell reference #Value!-wrong argument type or operand # N/A-No Answer #Num!-invalid numeric values in a formula or function If you enter 1,149+25 in a cell with no equal sign what results-NO ANSWER =2+4*10- 42 results..(10*4)+2 Order of Precedence in excel: 1.Parentheses 2.Exponentiation 4.Multiplication and division 5. Addition and Subtraction When writing formulas why is it preferable to use cell references rather than typing in the values-So that if you have to change the value (cell reference) later the formula will automatically change Add a range of numbers in cells A2 through X2- =SUM(A2:X2) Largest value in cells C2 through C8- =MAX(C2:C8) Smallest Value in Cells B2 through Z12- =MIN(B2:Z12) Average of C1 through C10 and numbers not in all of them- =AVERAGE(C1, C2, Etc..) leave out the cells that are blank. Total number of values listed in cells C1 through C10 excluding any that have text- =SUM(C1,C2…) don’t list cells with zero or text Algorithm-Rule that governs how a function works or a systematic set of procedures that the computer always steps through to calculate the results of a function Arguments-a function input..example- ROUND function contains two arguments a value and the number of decimals places as follows =ROUND(2.22,0) Syntax-The specific format of a function including the function name and the order of the arguments in the function =$B$4-SUM(C1:C5) and copy from A9 to Cell C10 what happens?-=$b$4-SUM(E2:E6) $B11-Number will not vary when copied across the COLUMN C$5-Number will not vary when copied down the ROW =B1 *110= (B1 in Excel is 10%)- 11 In Excel what if the numbers 2 and 4 are presents and then their Sum is 5.8 what could be the problem?-One of the numbers either 2 or 4 could be rounded to the eye in excel so excel still calculates the sum for the actual number not the rounded one.CHAPTER 2 What formula could you write to calculate the mean of the following data set: 2,5,4,3,1,2,7?- =AVERAGE(2,5,4,3,1,2,7) What is the median value of the data set given in question 1? (Middle number when in numerical order)- 1,2,2,3,4,5,7 What is the mode of the data set given in question 1?- 2 it is the most common 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 data set with the standard deviation of 2.5 has more higher numbers than does The data set in Question 1. In the chapter, the original labor rate for inspectors was give 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?- (New-Old)/Old (37.50-35)/35 When using the increase decimal button on the toolbar the precise value in the cell is modified-FALSE Write a formula to round up 63.34% to the nearest percent- =ROUND (63.34%,2) What is the symbol for the greater than or equal to relational operator in Excel?->= What is the symbol for the not equal to relation operator in excel-< > To count the number of specific things in a list- = COUNTIF (Range, “What you are looking for”) Write a formula to determine the number of cars that cost less than $20,000 (pg153)-=COUNTIF (range, “<20,000”) Write a formula to determine the total value of all ford cars- =SUMIF( NAME RANGE, “Ford”, SUM RANGE( Cars prices)) Explain the difference between a “What-if” analysis and Goal Seek by giving an example based on the worksheet shown in question 11- The “What-if” analysis means simply to determine the outcome of changing one or more input values and evaluate the recalculated results. For example: If labor rate is set at $35.00 and multiple cells follow that use that rate in their formulas, change 35 to $45 and all of the cells will automatically change. View the changed results. “Goal Seek” is used when you want to change an input value in order to reach a specified output. For example: What labor rate would produce the total cost to inspect at $150. Goal Seek is located under Data, What if Analysis, Goal Seek. Write a formula to determine the value of the third most expensive car- = RANK.EQ (1st price on list, range of car prices) because it is descending order If each car showing 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+ RAND()*200+50 The Formula =RAND() gives what result- a random value between 0 and 1, when given numbers minus them (x-y) and then do =RAND()*(x-y)+y 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,A11, leaving out blank cells)),1) Write a formula to determine the average price of only ford vehicles-=AVERAGEIF(NAMES OF CARS,”FORD”,PRICES) Write a formula to generate a random integer value between 10 and 20-=RANDBETWEEN(20,10), Rand between because they asked for an integer which means no decimal places. RAND() gives decimals . CHAPTER 3 List and describe the significance of each of Tufte’s five data graphics principles- 1. “Above all else show the data”-don’t clutter a chart by adding unnecessary illustrations. 2.”Maximize the data-ink ratio”refers to the portion of the ink that is devoted to displaying the data versus the portion of the graphic that can be removed without losing the data. 3.“Erase non-data-ink”-part of the chart that decorates more than informs 4.”Erase redundant data ink,”-ink that repeats information. 5.”Revise and Edit”-charts can be improved by revising and editing. How do sparklines differ from charts-Sparklines are small word-sized charts or graphics embedded within the words, numbers, or images they represent. See page 168 Three steps to insert a chart into excel- First select the data you want to display, click the inset tab, and then clik a button in the charts group or the dialog box launcher in the charts group if you want to choose from all available chart types and sub-types. Give an example of a low data-ink ratio chart-Column or Bar charts. Use a lot of ink to display minimal information. How do you change the chart type of an existing chart-select the chart
View Full Document
Unlocking...