CGS2518 Study Guide Chapter 1 Conceptual Review 1 Meaning of these errors Insufficient width in cell to display numerical data NAME Unrecognized text in a formula N A No answer REF Invalid cell reference VALUE Wrong argument type or operand NUM Invalid numeric values in a formula or function DIV 0 Divided by zero 2 1 149 25 will show 1 149 25 3 2 4 10 will equal 42 4 List in order of use Parentheses Exponents Multiplication and Division Addition and Subtraction It makes changing the data easier It s also faster to reference cells rather 5 Why is it important to reference cells instead of typing in values than typing each number in individually when it will be used several times 6 Not enough decimal places show the real value of the number 7 10 110 11 8 What formula would you use to write each of the following Add a range of numbers A2 X2 SUM A2 X2 Find the largest value in cells C2 C8 MAX C2 C8 Smallest value in cells B2 Z12 MIN B2 Z12 Average value in cells C1 C10 AVERAGE C1 C10 Find number of values in C1 C10 excluding text COUNT C1 C10 9 Define the following terms Syntax The specific format of a function including the function name and the order of the arguments in the function Arguments A function input For example the ROUND function contains two arguments a value and the number of decimal places as follows ROUND 2 22 0 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 Copy B 4 SUM C1 C5 from A9 to C10 it changes to B 4 SUM E2 E6 11 Keep Column the same A to keep row the same A 1 12 If the following formulas are copied from C10 to E13 A1 A2 C4 C5 A 1 A2 A 1 C5 A1 A2 A3 C5 A 1 A2 C 1 C5 SUM Sheet1 B1 Sheet2 B1 Sheet3 B1 13 What formula do you use to add up cell B1 from Sheet1 Sheet2 and Sheet3 14 B1 is named discount write a formula in cell C1 that multiplies discount by A1 Move formula to C2 discount A2 Chapter 2 Conceptual Review 1 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 2 What is the median value of the data set given in Question 1 MEDIAN 2 5 4 3 1 2 7 3 What is the mode MODE 2 5 4 3 1 2 7 4 5 New cost Old Cost Old Cost 100 6 When using the increase decimal button on the toolbar the precise value in the cell is modified True or false True 7 The formula ROUND 345 43 0 8 ROUNDUP 63 34 0 345 9 What is the symbol for the greater than or equal to relational operator in Excel 10 What is the symbol for not equal to 11 COUNTIF formula 12 COUNTIF formula 13 SUMIF range criteria sum range 14 The what if analysis allows you to change a value in one cell and see how the change affects the rest of the data The Goal Seek analysis performs the task automatically 15 To find the 3rd largest number use the LARGE function LARGE array k 16 RANDBETWEEN bottom top 17 The RAND function returns a random value between 0 and 1 18 Round to the nearest tenth ROUND number 1 nearest hundredth ROUND number 2 19 AVERAGEIF 20 RANDBETWEEN bottom top Chapter 3 Conceptual Review 1 List and describe the significance of each of Tufte s five data graphics principles Above all else show the data don t clutter a chart by adding unnecessary illustration or decoration Maximize the data ink ratio within reason portion of the ink that is devoted to displaying the data versus the portion of a graphic that can be removed without losing data Erase non data ink within reason Erase redundant data ink within reason ink that repeats information Revise and edit 2 How are sparklines different from charts the words numbers and images they represent Sparklines are small word sized charts or graphics that are embedded within First select the data then click the Insert tab then click a button in the Charts 3 What are the three steps involved in using the Insert tab to create a chart group or the Dialog Box Launcher in the Charts group 5 How do you change the chart type of an existing graph Select the chart or chart series In the Type group on the Design tab click the Change Chart Type button Click the chart type you want to change to and then click the sub type if necessary Click the OK button There are 11 chart types and 25 sub types An XY scatter chart plots numeric values on both the x and y axes based on An Excel chart requires at least one numeric data series and at least one label 6 What charting limits does Excel have in terms of data points and series data series The number of data series and data points that Excel can display in a chart is only limited by available computer memory and capacity 7 How many standard chart types and sub types are available in Excel 8 What are the differences and similarities between a line chart and an XY scatter chart When should you use each one the value of the data whereas a line chart plots numeric values on one axis and category labels equidistantly on the other axis 9 What are the differences between the data points on a line chart and an XY scatter chart The line chart plots the data points along the y axis based on the value but the x axis as categories The XY chart plots both the y and x axis data as numeric values 10 What are the differences between a bar chart and a column chart A column chart compares values across categories in a vertical orientation Values are indicated by the height of the column Bar charts compare values across categories in a horizontal orientation Values are indicated by the length of the bars 11 What are the differences between a column chart and an area chart by the filled areas below the lines 12 How do pie charts differ from doughnut charts Doughnut charts show the information contained in a pie chart for more than one series An area chart displays trends over time or by category Values are indicated Pie charts can show individual percentages but only for one data series 13 When should you use a stacked line column or area chart How do the stacked charts differ from regular charts Stacked column chart is the one that most clearly shows the totals for each category The stacked area and stacked line charts don t clearly show that the lines represent the cumulative total of each …
View Full Document
Unlocking...