CHAPTER ONE 14 QUESTIONS 1 Errors a Column too narrow or negative date b NAME Unrecognized text in formula c N A No answer can be found d REF Invalid cell reference e VALUE Wrong argument type or operand f NUM Invalid numeric values in the formula g DIV 0 Divide by 0 2 No it will not be able to calculate it Excel will try to correct it by removing the comma and refuses to calculate the formula without removing it 3 2 4 Order of Precedence a Add subtract 4 b Parentheses 1 c Multiply Divide 3 d Exponentiation 2 5 You should use cell references so that if the number in the cell changes later the formula will automatically update to reflect that 6 Set to display no decimals the numbers are rounded so the exact numbers in the cell could be 1 55 and 1 62 7 0 5 8 Formulas a SUM b2 B12 b MAX C2 F2 c MIN A1 X10 d AVERAGE C1 C10 e COUNT A1 F1 10 B 4 SUM D3 D7 11 A2 B 1 12 Formulas a C3 C4 b A 1 C4 c A3 C4 d C 1 C4 13 SUM Sheet1 C1 Sheet2 C1 Sheet3 C1 9 Syntax specifies the function name and order of the arguments Arguments are function inputs Algorithm is the rules that determine how the function behaves 14 discount A1 when you copy to C2 it becomes discount B1 4 The data set with the standard deviation of 4 14 will have a greater spread between numbers compared to the data set with the standard deviation of 2 5 CHAPTER TWO 20 QUESTIONS 1 AVERAGE 4 10 8 6 2 4 14 2 Median value is 6 3 The mode is 4 5 40 35 35 6 FALSE 7 710 9 10 8 ROUNDDOWN 63 34 2 11 COUNTIF B2 B10 Ford 12 COUNTIF B2 B10 20000 13 SUMIF A2 A10 GM B2 B10 14 What if analysis would determine how the average price of ford cars would change if one of the values changes while goal seek would determine how the values would change if the average changes Goal seek is reverse what if analysis 15 SMALL B2 B10 3 16 B2 RANDBETWEEN 100 200 17 A random value between zero and one 18 ROUNDUP AVERAGE B2 B10 0 19 AVERAGEIF A2 A10 GM B2 B10 20 SUMIF B2 B10 20000 CHAPTER THREE 18 QUESTIONS 1 The five principles are Above all else show the data don t add anything unnecessary Maximize the data ink ratio use as little ink as possible to display the data Erase non data ink decorative not important to data Erase redundant data ink ink that repeats information Revise and edit make changes to make the chart better 2 Spark lines differ from charts in that they are smaller word sized charts or graphics embedded within the words numbers images they represent 3 The three steps are first select the data second click the INSERT tab third click the recommended charts button in the charts group 4 An example of a low data ink ratio would be using columns to represent numbers that could be represented the same by a line chart 5 To change the chart type you would First select the chart you want to change then click the CHART TOOLS DESIGN tab third you click the chart types sub types to preview then finally you select the chart you want and click OK 6 The number of data series and data points that Excel can display is only limited by available computer memory and capacity 7 There are 10 standard chart types and 53 sub types to choose from 8 Scatter charts plot numeric values on both the X and Y axis Line charts show values on one axis and category labels on the other Use XY to compare sets of data on both the X and Y axis 9 XY scatter chart plots numeric values on the x and y axis based upon the value of the data whereas a line chart plots numeric data on one axis and category labels on the other 10 Both the Bar and the Column charts display data using rectangular bars where the length of the bar is proportional to the data value Both are used to compare two or more values However their difference lies in their orientation A Bar chart is oriented horizontally whereas the Column chart is oriented vertically Bar charts are good for displaying long data labels and Bar charts are good for displaying large number of data sets on the category axis Column charts are good for displaying data sets with negative values 11 Column charts are a good way to visualize one or more categories of data particularly if each category has sub categories These charts allow you to visually express the difference between the data points of each category Use the area chart for showing trends over time among related attributes Use area chart when you want to display trends over time use column chart when you want to compare individual categories 12 Pie charts can show individual percentages but only for one data series while donut charts show the information contained in a pie chart but for more than one series 13 You should use the stacked charts when you want to show the cumulative effects of data in categories Stacked charts differ from regular charts because they show data cumulatively not individually 14 You should use the 100 charts when you want to show the relative percentages between categories exactly like a pie chart except for the shape 15 Stock chart types closing price decreased High Low Close vertical line represents range from high to low marker represents Open High Low Close vertical line represents range from high to low top and bottom of box represent opening closing prices White box increased in price black Volume Open High Low Close same as a open high low close chart but with an added bar chart to display volume 16 Radar charts are different because it combines the comparison features across categories that area charts provide with the comparison of relative contribution that pie charts provide Radar charts are good for displaying and comparing data across different categories for example comparing total sales between a large number of categories bubble chart 17 A bubble chart is a variation of a scatter chart in which the data points are replaced with bubbles and an additional dimension of the data is represented in the size of the bubbles Just like a scatter chart a bubble chart does not use a category axis both horizontal and vertical axes are value axes Therefore numeric data is suitable for a 18 You should use a dashboard chart when you want to provide a quick visual summary of performance indicators such as customer satisfaction monthly sales etc CHAPTER FOUR 15 QUESTIONS 1 A TRUE B FALSE C FALSE D TRUE 2 Go to conditional formatting then click on new rule then format only cells that contain then make it say format only cells with cell values …
View Full Document
Unlocking...