- Insufficient width in cell to display numerical data, or formulas that result in negative date/time (prior to 1/1/1990)

  If you enter 1,149+25 in a cell exactly as shown (without an equal ign), what value would result?

     1,149+25

   If you enter =2+4*10 in a cell exactly as shown, what value would result?

  42

·        Multiplication and division -- 3

·        Parentheses ( ) --1

·        Addition and subtraction -- 4

·        Exponentiation –2

    When writing formulas, why is it preferable to use cell references rather than typing in values?

      It allows the user to easily update the spreadsheet without having to know exactly which formula or formulas contain the changed value.

  Referring to the preceding worksheet, if you wrote the formula =B1*110, what value would result (assuming the displayed value is the precise value)?

   What formula would you write to do each of the following?

·Add a range of numbers in cells A2:X2.

                   =SUM(A2:X2)

·        Find the largest value in cells C2:C8.

  =MAX(C2:C8)

     Find the smallest value in cells B2 through Z12.

MIN(B2:Z12)

=AVERAGE(C1:C10)

Find the total number of values listed in cells C1 through C10, excluding any that contain text.

  =COUNT(C1:C10)

    the specific format of a function, including the function name and the order of the arguments in the function.

   =$B$4-SUM(E2:E6)

     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?

 What formula could you use to add up cell B1 from Sheet1!, Sheet2!, and Sheet3! (Assuming the worksheets are contiguous and in the same workbook)?

      =SUM(Sheet1!B1,Sheet2!B1,Sheet3!B1)

·        =discount*A1

·        =discount*A2

   What formula could you write to calculate the mean of the following data set: 2,5,4,3,1,2,7? (Note that a resulting value is not required.)

    =(2+5+4+3+1+2+7)/7

   What is the median of the data set given in Question 1?

  What is the mode of the data set given in Question 1?

2,5,4,3,1,2,7?

   The data set given in Question 1 ( 2,5,4,3,1,2,7 )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 second set of data has values that occur further from the mean than those in Question 1.

    In the chapter, the original labor rate for inspectors was given as $35 per hour. However due to contract renegotiation, this value is now $37.50. What algebraic expression could you use to determine the percent increase in labor costs? (Note that a resulting value is not required.)

       =(37.50-35)/35

   When using the Increase Decimal button on the toolbar, the precise value in the cell is modified. True or False?

  The formula =ROUND(345.43,0) results in what precise value?

   Write a formula to round up 63.34% to the nearest percent.

      =ROUNDUP(63.34,2)

 What is the symbol for the greater than or equal to relational operator in Excel?

      >=

  What is the symbol for the nor equal to relational operator in Excel?

   <> 

   Review the following worksheet, nd then use the COUNTIF function to write a formula that determines the munver of GM cars in the list.

  =COUNTIF(A2:A10,”GM”)

     Using the worksheet shown in Question 11, write the formula to determine the number of cars that cost less than $20,000.

  =COUNTIF(B2:B10,”<20000”)

  Using the worksheet sown in Question 11, write a formula to determine the total value of all ford cars.

   Explain the difference between a “what-if” analysis and Goal Seek by giving an example based on the worksheet in Question 11.

     For a what if analysis you could change the sum of the prices of the cars and evaluate the total and for Goal Seek you could put what you want the total to be and tell excel to change the value of a certain cell to get that total.

    Using the worksheet shown in Question 11, write a formula to determine the value of the third most expensive car.

      =LARGE(B2:B10,2)

    If each car shown in Question 11is 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+______

=B2+RANDBETWEEN(50,250)

   The formula =RAND() gives what result?

1.     What formula could you write to average the values in cell A10 through A20, excluding blank cells, rounded to the nearest 10?

=ROUND(AVERAGE(A10:A20),0)

 Write a formula to determine the average price of only Ford vehicles using the worksheet in Question 11.

    =AVERAGEIF(A2:A10,”Ford”, B2:B10)

     Write a formula to generate a random interger between 10 and 20.

     =RANDBETWEEN(10,20)

a.      refers to the proportion of the ink that is devoted to displaying the data versus the portion of a graphic that can be removed without losing the data.

 related to maximizing the data-ink ratio. Non-data-ink is part of the chart that decorates more than informs.

 feedback on the chart’s usefulness and clarity should be sought wherever possible, and the chart should be adjusted accordingly. The intended message of the chart must be considered. A positive feature in a chart might become negative if it doesn’t support what the chart creater is tryng to illustrate.

     How do sparklines differ from charts?

    Sparklines are small word-sized charts or graphics and are embedded within the words, numbers and images they represent. They represent the ideal of Tufte’s five data graphics principles.

  What are the three steps involved in using the Insert tab to create a chart?

    First select the data you want to display in the chart, click the insert tab, and then click a button in the Charts group pr the Dialog Box Launcher in the charts group if you want to choose from all the available chart types and sub-types.

  Give an example of a low data-ink ratio in a chart.

   When a chart has too much data, labels and chart effects.

  How do you change the chart type of an existing chart?

  You select the chart or chart series you want to whose type you want to change then in the type group on the Design tab, click the change chart type button and choose the type you want to change it to.

     What chart limits does Excel have in terms of data points and series?

   A chart requires at least one numeric data series and at least one label data series. The number of data series and points that excel can display in a chart is only limited by available computer memory and capacity.

     How many standard chart types and sub-types are available in Excel?

  11 standard chart types and 73 sub-types

 What are the differences and similarities between a line chart and an X Y (Scatter) chart?

 An X Y (Scatter) chart [plots numeric values on both the x- and y-axes based on the value of the data, where as the line chart plots numeric values on one axis and category labels equidistantly on the other axis

    Explain the difference between the data points in a line and an X Y (Scatter) chart.

  The line chart plots the data points along the y-axis based in the value, but the x-axis is based on position because this axis contains categories. The X Y (Scatter) chart plots both the y- and x-axis data as numeric values.

   What are the differences between a bar chart and a column chart? Give an example of when you would use each one.

   A bar chart compares values across categories in a horizontal orientation and values are indicated by the length of the bars. A column chart compares values across categories in a vertical orientation and values are indicated by the height of the columns. You could use either one to compare values across categories.

     What are the differences between a column chart and an area chart? Give an example of when you would use each one.

a.      A column chart compares values across categories in a vertical orientation and values are indicated by the height of the columns. An area chart displays trends over time by category and the values are indicated by thr filled areas below the lines. You could use a column chart to compare the amount of  sales for each category and you cold use the are chart to illustrate the sales trends in different categories over  a longer time period, while emphasizing the contribution that each category made to particular year’s performance.

  How do pie charts differ from doughnut charts?

   Pie charts compare the contribution of each value in a single numeric data series and makes to the whole, or 100% and values are indicated by the size of the pie slices. A doughnut chart compares the contribution value in multiple numeric data series makes to the whole and values are indicated by the size of the doughnut segments.

   When should you use a stacked line, column, or area chart? How do the stacked charts differ from regular charts?

    You shoud use them to show the totals for each category. Staked charts illustrate the cumulative effects of data in categories.

  When should you use a 100% staked line, column, or area chart? How do the 100% staked charts differ from staked charts?

   They can be used as an alternative to a pie chart and can help reduce confusion as over whether the lie line on the charts represents the individual or cumulative contribution to the whole The 100% staked charts illustrate the cumulative contribution for each category expressed as a percentage.

   What chart sub-types are available for the stock chart in Excl? Explain how you interpret the data markers in each of the sub-types.

   How does a radar chart differ from other charts? Give an example of when you would consider doing a radar chart.

      A radar chart compares values across categories in a circular orientation and values are indicated by the distance from a center point. You would do a radar chart to show the numerical value that each category contributed to the whole.

     Define a bubble chart and explain what type of data is suitable for plotting on a bubble chart.

     A buble chart compares sets of three values and values are indicated by the sixe of the bubbles. 3-D data is suitable for plotting on a bubble chart. For example it can be used to display the relationship between the number of styles of shoes offered, sales, and market share.

    When should you use a dashboard chart?

     A dashboard chart can be used to provide a quick, visual summary of  performance indicators.

    False

 Describe how you would format a cell so that is the value Is greater than 50 it would be automatically bolded.

      You select the cells you want to apply the conditional formatting to then click the conditional formatting button in the styles group in the home tab and then point to the highlight sells rule to display its options. Then select greater than and type in 50 and then use the format cell dialog box and select bold then click OK.

     What formula is used in cell E3, which can be copied down the column to determine (TRUE or FALSE) if the item is within budget?

     =OR(D3<C3,FALSE)

     Write a formula to determine if all the items are in budget.

     =AND(E3:E8,TRUE)

    Write a formula to determine if at least one item is within budget.

    =OR(E3:E8,TRUE)

     Write a formula in cell F3 that can be copied down the column to determine if this food item is not within budget.

    =NOT(E3,FALSE)

  Write a formula to determine if none of the values are within budget.

     =AND(E3:E8,FALSE)

     Write a formula to determine if (TRUE or FALSE) only the required items (R) are within budget. Note that this formula does not have to work if the optional/required categories are later modified.

     =IF(B7="r",AND(E7,TRUE),FALSE)

        Are the following two Boolean expressions equivalent? Why or why not?

·        =NOT(OR(E3:E8))

·        =AND(NOT(E3),NOT(E4),NOT(E5),NOT(E6),NOT(E7),NOT(E8))

     These expressions are equivalent because they both give you the same answer

    Is the following formula valid? Why or why not?

·        =NOT(E3:E8)

                                                                    The formula is not valid because for a NOT function you are only supposed to put one argument and it changes it to the opposite of what the argument says.

     What value would the following formula return?

       =IF(D4<=C4,”within budget” ,”over budget”)

                                                              i.      it returns “over budget”

    What value would the following formula return?

     =IF(SUM(D6,D8)<200, “go to both”, IF(SUM(D6,D8)>450,”go to neither”,”choose one”))

                                                              i.      it returns “go to both”

     Write a formula in cell G3 that can be copied down the column to return the following:

·        If this item has an actual cost of less that $100,then return the text “Minor Component Cost”.

·        If this item has an actual cost of $100 or more, then return the text “Major Component Cost”.

                                                                    =IF(D3<100,”Minor Component Cost”,IF(D3>=100,”Major Component Cost”,” “))

     Write an excel formula in cell H3 that can be copied down the column to calculate the cost of this component for a larger sales meeting based on the following:

·        If this item is optional as indicated in column B, then the cost will be equal to the original budgeted amount.

·        If this item is required as indicated in column B, then the cost will  be three times the original budgeted amount.

                                                                   =IF(B3=”O”,C3,IF(B3=”R”,3*C3,C3)

1.     The lookup_value of a VLOOKUP function can be a contiguous cell range.

    In a VLOOKUP formula with a TRUE lookup type, the first column of the lookup table referenced must be in ascending order to retrieve the correct value.

     The result_vector of a LOOKUP function must be sorted in ascending order.

     Reference and Lookup functions may not contain nested functions as arguments.

     The default range_lookup type for the VLOOKUP and HLOOKUP functions is FALSE.

  The row and column arguments in the INDEX function can be numeric values, Boolean values, or text.

     The Formula =MATCH(40, {10,40,50,90},0) returns the value 2.

What happens  when Excel is solving a VLOOKUP formula with a FALSE range_lookup type and does not find an exact match in the lookup table?

    What happens  when Excel is solving a VLOOKUP formula with a FALSE range_lookup type and does not find an exact match in the lookup table?

    The text #N/A is displayed in the cell.

    What is the difference between the LOOKUP function and the VLOOKUP or HLOOKUP function?

        The LOOKUP function looks up values anywhere in a table or range and VLOOKUP can only retrieve data from a table with a vertical orientation and an HOOKUP from a table with a horizontal orientation.

     Which Excel function should you use when you want to look up a value from a two dimensional table, where both the columns and rows can be varied?

        The INDEX function

    Write a formula to choose the name of the fifth day of the week from the list starting with Sunday, Monday, Tuesday…Saturday.

        =CHOOSE(5,"Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday")

     What is the difference between the LOOKUP function and the MATCH function?

You got to fight, fight, fight for FSU! You got to scalp ‘em Seminoles! You got to win, win, win, win this game and Roll on down to make those goals! For FSU is on the warpath now, and at the battle’s end she’s great.  So fight, fight, fight for victory, the Seminoles of Florida State! F-L-O-R-I-D-A…. S-T-A-T-E FLORIDA STATE !! FLORIDA STATE!! FLORIDA STATE!! Woooooooooooo!!