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 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 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 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 you want to change …
View Full Document
Unlocking...