Excel Test 1

Front 1

what is the meaning of this error message?
#####

Back 1

Insufficient width in cell to display numerical data or formulas that result in a negative date/time

Front 2

what is the meaning of this error message?
#NAME?

Back 2

Unrecognized text in a formula

Front 3

what is the meaning of this error message?
#N/A

Back 3

No answer

Front 4

what is the meaning of this error message?
#REF!

Back 4

Invalid cell reference

Front 5

what is the meaning of this error message?
#VALUE!

Back 5

wrong argument type or operand

Front 6

what is the meaning of this error message?
#NUM!

Back 6

invalid numeric values in a formula or function

Front 7

what is the meaning of this error message?
#DIV/0!

Back 7

Division by zero

Front 8

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

Back 8

1,149+25

Front 9

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

Back 9

42

Front 10

List each of the following operations in order of precedence, from 1 to 4 (first to last)
-Multiplication/division
-parentheses
-addition/subtraction
-exponentiation

Back 10

1. parentheses
2. exponentiation
3. multiplication/division
4. addition/subtraction

Front 11

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

Back 11

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

Front 12

which formula would you write to add a range of numbers in cells A2:X2

Back 12

=SUM(A2:X2)

Front 13

which formula would you write to find the largest value in cells C2:C8

Back 13

=MAX(C2:C8)

Front 14

which formula would you write to find the smallest value in cells B2 through Z12

Back 14

=MIN(B2:Z12)

Front 15

which formula would you write to find the average value in cells C1 through C10, assuming blank cells will be ignored

Back 15

=AVERAGE(C1:C10)

Front 16

which formula would you write to find the total number of values listed in cells C1 through C10, excluding any that contain text

Back 16

=COUNT(C1:C10)

Front 17

what is the difference between the COUNT function and teh COUNTA function?

Back 17

The COUNT function will count the number of components and ignore blank cells and cells with text. The COUNTA is similar, but does not ignore text cells

Front 18

define syntax

Back 18

Specifies the function name and order of the arguments in each function

Front 19

define arguments

Back 19

the inputs in a function

Front 20

define algorithm

Back 20

the rules of a function and how it is programmed to behave

Front 21

What does $A$4 do?

Back 21

Locks the cell and row

Front 22

What does $A4 do?

Back 22

Locks column A, but the row number will change as the formula is moved to different cells

Front 23

What does A$4 do?

Back 23

Locks row 4, but the column letter will change as the forumla is moved to different colums

Front 24

What formula could you write to calculate the mean of the following data set: 2,5,4,3,1,2,7?

Back 24

=AVERAGE(2,5,4,3,1,2,7)

Front 25

What formula would you write to calculate the median in the set of data 2,5,4?

Back 25

=MEDIAN(2,5,4)

Front 26

what is the definition of standard deviation?

Back 26

a measure of how widely the data values are dispersed from the mean

Front 27

what does it mean if two data sets have the same means, but one stdev is 1.58 and another is 2.5?

Back 27

the data set with the stdev 1.58 must have values much closer to the mean compared with the values of the stdev of 2.5

Front 28

What algebraic expression could you use to determine the % increase for 2 values?

Back 28

(New value-Old Value)/Old Value

Front 29

Whatn using the increase decimal button on the toolbar, the precise value in the cell is modified. true or false?

Back 29

False, the value is kept the same, excel just gives you the decimals

Front 30

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

Back 30

345 (no decimals)

Front 31

write a formula to round up 63.34% to the nearest percent

Back 31

=ROUNDUP(63.34%,2)

Front 32

What is a relational operator?

Back 32

an operator that compares two values

Front 33

what is the symbol for the greater than or equal to relational operator?

Back 33

>=

Front 34

what is the symbol for not equal to relational operator?

Back 34

<>

Front 35

write a formula using the COUNTIF function to determine the number of GM cars on a list (column A)

Back 35

=countif(A1:A50,"GM")

=countif(cell criteria range, cell criteria)

Front 36

write a formula to determine the number of cars that cost less than 20,000

Back 36

=countif(X1:X10,"<20000")

Front 37

write a formula to determine the total value of all ford cars

Back 37

=sumif(A2:A10,"Ford",B2:B10)

(Col A= car type)
(Col B = Value)

Front 38

What is a "what-if" analysis?

Back 38

when you perform a what if analysis, you determine the outcome of changing one or more input values and evaluate the recalculated results.

Front 39

what is the "goal seek" tool?

Back 39

Allows you to specifiy the outcome you want and which input value you want to vary. Helps you reach a specified output (work backward)

Front 40

write a formula to determine the value of the 3rd most expensive car

Back 40

=Large(B2:B10,3)

(Col B= $$ car value)
(3 = 3rd largest value)

Front 41

If each car is marked up between $50 and $250 what function could be used to randomly assign the amount to be added to the car price in this formula?

Back 41

=B2+randbetween(50,250)

Front 42

the formula =RAND() gives what result?

Back 42

a random value between 0 and 1

Front 43

what forumla could you write to average the values in cells A10 through A20, excluding blank cells, rounded to the nearest 10?

Back 43

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

Front 44

Write a formula to determine the average price of only ford vehicles.

Back 44

=AVERAGEIF(A2:A10,"ford",B2:B10)

(ColA=car names)
(ColB=values)

Front 45

write a formula to generate a random interger value between 10 and 20

Back 45

=randbetween(10,20)

Front 46

What are Tufte's 5 data graphics principles?

Back 46

1. Above all else show the data
2. Maximize the data-ink ratio, within reason
3. Erase non-data-ink, within reason
4. Erase redundant data ink, within reason
5. Revise and edit

Front 47

What does Tufte's first data graphic principle "Above all else show the data" mean?

Back 47

It is a reminder not to clutter a chart by adding unnecessary illustration or decoration. everything needs to have a reason to be there.

Front 48

What does Tufte's second data graphic principle "Maximize the data-ink ratio, within reason" mean?

Back 48

refers to the portion of the ink that is devoted to displaying the data verses the portion of a graphic that can be removed without losing the data.
a good example would be using lines instead of columns to present data

Front 49

What does Tufte's third data graphic principle "Erase non-data-ink, within reason" mean?

Back 49

non-data-ink is a part of the chart that decorates more than informs.

Front 50

What does tufte's fourth data graphic principle "erase redundant data ink" mean?

Back 50

redundant data ink is ink that repeats information. for example, the use of a data table and lables on each point on a chart is redundant

Front 51

What does tufte's last principle "revise and edit" mean?

Back 51

charts can be improved by revision like writing can. often the best way to choose a chart is through trial and error.

Front 52

How do sparklines differ from charts?

Back 52

sparklines are word-sized charts or graphics that are embedded within the words, numbers, and images they represent. Placing them in the context of what they represent makes multiple comparisons of trends and patterns much easier.

Front 53

What are the 3 steps involved in using the insert tab to create a chart?

Back 53

1. select the data you want to display in the chart
2. click the insert tab
3. click a button in the charts group

Front 54

what does it mean if a chart has a low data-ink ratio

Back 54

a chart uses lots of ink to display a small amount of data

Front 55

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

Back 55

1. Select the chart you want to change
2. In the type group on the design tab, click change chart type
3. click the chart type you want to change to
4. click okay

Front 56

What charting limits does excel have in terms of data points and series?

Back 56

the number of data series and data points that excel can display in a chart is only limited by available computer memory and capacity

Front 57

how many standard chart types and subtypes are available in excel?

Back 57

11 standard chart types, 73 sub types

Front 58

What are differences between a line chart and an X Y (scatter) chart?

Back 58

Line charts plot numeric values on one axis and category lables on the other axis. Scatter charts compare pairs of numeric values on the x and y axes based on the value of the data.

Front 59

What are the similarities between a line chart and an XY scatter chart?

Back 59

They both display trends over time

Front 60

When should you use a line chart?

Back 60

Line charts are useful if the changes in the overall trend of the data needs to be emphasized.

Front 61

when should you use a scatter XY chart?

Back 61

when comparing sets of numerical data on both the x axis and y axis

Front 62

what are the difference between data points in a line chart and an XY scatter chart?

Back 62

Line chart plots data points along the y axis based on the value, but x axis based on position in the data (years)

Scatter XY charts plot both the y and x azis data as numeric values. sorts x values lowest to highest and plots them

Front 63

what are the differences between a bar chart and a column chart?

Back 63

bar charts compare values across categories in a horizontal orientation
Column charts compare values across categories in a vertical orientation

Front 64

When should you use a bar chart? a column chart?

Back 64

Bar charts would be helpful in noticing declining trendlines, where column charts would be useful in noticing a positive trendline

Front 65

What is an area chart?

Back 65

It combines the features of a line chart with a bar or column chart by filling in the area below the line and displaying the trend of values over time or categories.

Front 66

when is it better to use an area chart instead of a column chart?

Back 66

Area charts display trends over time. They can make reading data easier because a large number of columns can clutter up a chart. They are better to use to show data for longer periods of time

Front 67

How do pie charts differ from doughnut charts?

Back 67

Pie charts can show individual percentages, but only for one data series. Doughnut charts show the information contained in a pie chart for more than one series.

Front 68

What is a potential problem using doughnut charts?

Back 68

they can easily become confusing if too many data series are charted at once

Front 69

When should you use a stacked line chart?

Back 69

when trying to display a cumulative trend

Front 70

when should you use a stacked column chart?

Back 70

when you are looking to clearly show totals for a given category

Front 71

when should you use a stacked area chart?

Back 71

similar to the stacked line chart, these are good at showing a cummulative trend across data

Front 72

How do stacked charts differ from regular charts?

Back 72

Sstacked charts give you a slightly different perspective on the contribution that each category makes to the sum or whole

Front 73

When should you use a 100% stacked line chart?

Back 73

useful to show the trend of the percentage each value contributes over time or ordered categories.

Front 74

when should you use a 100% stacked column chart?

Back 74

when you have two or more data series and you want to emphasize the contributions to the whole, especially if the total is the same for each category. equivalent of multiple pie charts

Front 75

When should you use a 100% stacked area chart?

Back 75

display the trend of the percentage that each value contributes over time as a part of the total 100% (pie chart comparison)

Front 76

what chart sub types are available for the stock chart in excel?

Back 76

1. High-Low-Close
2. Open-High-Low-Close
3. Volume High-Low-Close
4. Volume Open-High-Low-Close

Front 77

For the high-low-close chart, what does the vertical line and tip of the arrowhead represent?

Back 77

Vertical line represents the range of lowest to highest stock prices for the time period

Tip of the arrow head represents the closing price for the time period

Front 78

For the open-high-low-close chart, what do the vertical line, black box, white box and the top and bottom of the box represent?

Back 78

Vertical line = range of lowest to highest stock prices

Black Box = Decrease in stock value

White Box = increase in stock value

Top and bottom of the box= the opening and closing prices

Front 79

How does a radar chart differ from other charts?

Back 79

They combine the comparison features across categories that area charts provide, with the comparison of the relative contribution to the whole that pie charts provide

Front 80

define a bubble chart

Back 80

Allow 3D data to be plotted on a 2D 2 axis graph. the x and y axes are numeric rather than one containing categories. the 3rd dimension is also numeric and is represented by varying the size of the data point. the larger the bubble, the larger the value.

Front 81

what type of data is suitable for putting on a bubble chart?

Back 81

when you have 3 sets of data that need to be displayed in one chart

Front 82

when should you use a dashboard chart?

Back 82

when you want a quick visual summary of data