IS44 Exam 1
Cell B2 contains the formula = sume(A1:A100) with the name of the sum function misspelled as sume. What error value will appear in the cell?
#NAME?
If the research firm wants to display the changing ethnic profile of the country over time as a percentage of the country population, which chart type should it use? Explain why.
100% stacked column chart
If 4/30/2017 and 5/31/2017 are the initial values, what are the next two values AutoFill will insert?
6/31/2017 7/31/2017 The last date of the next two months
If cell R10 contains the formula =$R1 + R$2, which is then copied to cell S20, what formula is entered in cell S20?
=$R10 + S$2
Convert the following equation into an Excel formula where the radius value is stored in cell E31 and the value pi is stored in cell D12: area = pi x radius^2
=(D12*(E31E31))
What formula would you enter to add the values in cells C1, C2, and C3? What function would you enter to achieve the same result?
=C1+C2+C3 =sum(C1:C3)
What function do you use to determine how many payment periods are required to repay a loan?
=NPER()
You want to apply for a $225,000 mortgage. The annual interest on the loan is 4.8 percent with monthly payments. You plan to repay the loan in 20 years. Write the formula to calculate the monthly payment required to completely repay the loan under those conditions.
=PMT(0.048/12, 20*12, 225000)
In Excel, the PI() function returns the decimal value of pi. Rewrite your answer for the previous formula using this function.
=Pi(E31E31)
Write the formula to display the current date in the worksheet.
=TODAY()
Write the formula to display a date that is four workdays after the date in cell A5. Do not assume any holidays in your calculation.
=WORKDAY(A5,4)
What formula would you enter to count how many numeric values are in the range D21:D72?
=count(D21:D72)
If cell Q3 is greater than cell Q4, you want to display the text "OK"; otherwise, display the text "RETRY". Write the formula that accomplishes this.
=if(Q3 > Q4, "OK", "RETRY")
The range of a set of values is defined as the maximum value minus the minimum value. Write a formula to calculate the range of values in the range Y1:Y100 and then to round that value to the nearest integer.
=max(Y1:Y100)-min(Y1:Y100)
Write a formula to return the middle value from the values in the range Y1:Y100.
=median(Y1:Y100)
Write a formula to round the value in cell A5 to the fourth decimal place.
=round(A5,4)
Write the formula to perform an exact match lookup with the lookup value from cell G5 using a vertical lookup table located in the range A1:F50. Return the value from the third column of the table.
=vlookup(G5, A1:F50, 3, false)
What is a histogram?
A column chart displaying the distribution of values from a single data series.
How are page breaks indicated in Page Break Preview?
A dotted blue border separates one page from another
What are sparklines? Describe the three types of sparklines.
A graphic that is displayed entirely within a worksheet cell. Convey the maximum amount of information within a very small space. A line sparkline for highlighting trends A column sparkline for column charts A win/loss sparkline for highlighting positive and negative values Can be inserted anywhere in a workbook. Can represent data from several rows or columns.
What is the difference between a line chart and a scatter chart?
A line chart is similar to a column chart but uses lines. The data series are plotted against the category values. The categories have a sequential order. Scatter chart has no category values. One series of data values is plotted against another.
A research firm wants to create a chart that displays the total population growth of a country over a 10-year period broken down by five ethnicities. Which chart type best displays this information? Explain why.
A stacked column chart in order to display how the population is broken into the 5 ethnicities and still show total population
How do you format text so that it is set vertically within the cell?
Alignment group Orientation button, rotate text up.
What is text data?
Any combination of letters, numbers, and symbols. Text is left aligned in cells whereas numbers, dates, and times are right aligned
What is a conditional format?
Applies formatting to a cell when its value meets a specific condition, used to help analyze data. Four types: data bars, highlighting, color scales, and icon sets Cell highlighting changes the cell's font color or fill color based on the cell's value
The range B3:B13 is merged into a single cell. What is its cell reference?
B3 Only the content from the upper left cell is retained.
What is the cell reference for the cell located in the second column and fifth row of a worksheet?
B5
What is the reference for the nonadjacent block of cells B5 through C10 and cells B15 through D20?
B5:C10,B15:D20 How to select? Use the command key on the mac while selecting the second nonadjacent range, or the control key on windows
How do you display the formulas used in a worksheet instead of the formula results?
Be on Page Layout view and then use Ctrl + ' or Command + ' on mac
Why does the PMT function return a negative value when calculating the monthly payment due on a loan or a mortgage?
Because the monthly loan payments are treated as an expense to the borrower.
What is the range reference for the block of cells C2 through D10?
C2:D10
What is the difference between the chart area and the plot area?
Chart area covers the entire background of the chart Plot area includes only the portion of the chart where the data markers, such as the columns in a column chart, have been placed or plotted.
What three chart elements are included in a pie chart?
Chart title chart legend: identifying each pie slice data labels: provide data value associated with each slice
If the research firm is interested in comparing the numeric sizes of different ethnic groups over time, which chart should it use? Explain why.
Clustered column chart, does not need total
A data series contains values grouped into 10 categories. Would this data be better displayed as a pie chart or a column chart? Explain why.
Column chart. Pie charts should not have more than 6 categories.
Explain the difference between the count function and the counta function.
Count will tell how man cells in a range contain numbers CountA will tell how many cells are not empty in a range, includes numbers and text entries
What are data bars? How do data bars differ from sparklines?
Data bars are a conditional format that adds a horizontal bar to the background of a cell containing a number. When applied to a range of cells, the data bars have the same appearance as a bar chart. The lengths are based off of the selected range. Larger values have a longer bar. Different from sparklines because the bars are placed in the cell they are referencing
How do you change the scale of a chart axis?
Double click the axis pane and click the format tab. Enter the new axis scale in the minimum and maximum boxes. Specify the space between tick marks in major and minor boxes.
Where can you access all the formatting options for worksheet cells?
Format cells dialog box Home tab--font group--dialog box launcher
If you insert cells into the range C1:D10, shifting the cells to the right, what is the new location of the data that was previously in cell F4?
H4 2 columns were inserted. The row would stay the same and it would shift 2 to the right which is column H.
What is the General format?
How cells start out formatted Displays numbers exactly as typed or if from a formula will give numbers after the decimal and round out the last one that won't fit into the cell.
How do you enter a number so that Excel sees it as text?
Include an apostrophe before the numbers Example: '02392
What orientation would you use to make the printed pages wider than it is tall?
Landscape Portrait is taller than it is wide
What are major tick marks, minor tick marks, and chart gridlines?
Major tick marks-- main units on the chart axis Minor tick marks-- identify the smaller intervals between the major tick marks Chart gridlines-- extend tick marks into chart area
How do you insert a manual page break in a worksheet?
Manual page breaks will appear as a solid blue line. Automatic page breaks appear as a dotted blue line on page break preview Click the first cell below the row where you want to insert a page break. Click a column heading or a row heading. On page layout tab in page setup group, click breaks button and then click insert page break.
Describe four ways of viewing the content of a workbook in Excel.
Normal view: shows contents of the worksheet Page layout view: shows how the worksheet will appear when printed Page Break Preview: displays the location of the different page breaks within the worksheet Formula View: printout displays formulas instead of values.
What keyboard shortcut makes the active cell to cell A1?
On a windows computer: Control + home
If the research firm wants to display the ethnic profile of the county only for the current year, which chart should it use? Explain why.
Pie chart
Describe how to add the workbook filename to the center section of the footer on every page of the printout.
Print screen -- page setup -- header/footer tab -- click the center section and click the filename tab to add it.
What is a print area?
Region of the worksheet that is sent out to the printer Page Break Preview will shade parts of the workbook that will not be printed Page Setup group, print area, set print area
You need to reference Cell Q57 in a formula. What is its relative reference? What is its absolute reference? What are the two mixed references?
Relative reference: Q57 Absolute: $Q$57 Two mixed: $Q57 and Q$57
What are print titles?
Rows of the worksheet that you want to be printed at the top of each page, not just the first location Page layout -- page setup -- print titles -- rows to repeat at top -- then select
A researcher wants to plot weight versus blood pressure. Should the researcher use a line chart or a scatter chart? Explain why.
Scatter chart. They aren't declaring anything as low or high. Also there are two data series involved.
How would you highlight the top 10 percent values of the range A1:C20?
Select range A1:C20. The quick analysis tool will show in the lower left corner. The formatting category will show buttons for greater than and top 10% conditional formatting rules.
Explain how to use the Quick Analysis tool to calculate a running total of the values in the range D1:D10.
Select range D1:D10 click the quick analysis button in the lower right corner of the select range. Click totals from the list of tools. Click the right scroll arrow to view additional tools and hit Running.
Describe two methods of applying the same format to different ranges.
Select the cell or range in which you want to apply. On the home tab select cell styles and click the one you want. Format painter. Will copy the format of the selected area. You click the new cell or cell range you want the format to be copied to. If you click the format painter only once it can only be used once, click twice to make it unlimited use. Paste option. Each time you paste the paste options button appears in the lower-right corner of the pasted cell or range. You click it to open a list of pasting options such as pasting only the format
What is the difference between a serif font and a sans serif font?
Serif fonts have extra strokes at the end of each character that aid in reading passages of text Example: Times New Roman Sans serif fonts do not include strokes Example: Arial
Cell B2 contains the entry May 3, 2017. Why doesn't excel consider this a text entry?
Short answer: it is stored as a number entry/ as a date referred to as 'date data' Excel stores the date as a number entry equal to the number of days between the specified date and January 0, 1900. Times are also entered as text and stored as fractions of a 24 hour day.
Describe the differences between Currency format and Accounting format.
Similarities: Add a thousands separator and display two digits to the right of the decimal point. Currency: places a currency symbol directly to the left of the first digit and displays negative numbers with a negative sign Accounting: fixes a currency symbol to the left edge of a cell and displays negative numbers with parentheses and zero values with a dash. Also slightly indents values away from the right edge of the cell to allow room for parentheses.
Red is a standard color. What happens to red text when you change the workbook's theme?
The appearance of nontheme fonts, colors, and effects remains unchanged no matter which theme is applied to the workbook.
Stephen is entering hundreds of temperature values into an Excel worksheet for a climate research project, and he wants to speed up data entry by leaving freezing point values as blanks rather than typing zeroes. Explain why this will cause complications if he later tries to calculate the average temperature from those data values.
The average function won't consider those as entries so they will not be calculated, his average would be off. Excel ignores blank entries.
Cell E11 contains the formula =sum(D1:D20). How does this formula change if a new row is inserted above row 5?
The formula will include the new row. =sum(D1:D21) ** because inserting a new row or column moves the location of the other cells in the worksheet, any cell references in a formula or function are updated to reflect the new layout
What is the difference between a theme color and a standard color?
Theme colors are the 12 colors that belong to a workbook's theme. 4 colors are designated for text and background. 6 colors are used for accents and highlights. 2 colors are used for hyperlinks. Each theme color has 5 variations, where tint or shading differs. Standard colors include 10 colors that are visible no matter the theme dark red, red, orange, yellow, light green, green, light blue, blue, dark blue, and purple
A cell containing a number displays ######. Why does this occur, and what can you do to fix it?
This occurs because the column width is not wide enough to display all of the numbers in a cell. This can be fixed by autofitting the column to the maximum cell length in that column.
What is the difference between a what-if analysis by trial and error and by Goal Seek?
Trial and error-- you change one of the input values and see how it affects results, requires guess work Goal seek automates the trial and error process by allowing you to specify a value for a calculated item.
What are the two types of sheets used in a workbook?
Worksheets: contains a grid of rows and columns into which you can enter text, numbers, dates, and formulas and display charts. Chart Sheets: contains a chart that provides a visual representation of worksheet data
How do you auto-fit a column to match the longest cell entry?
You double click the line between that column and the next. Double click the right border of the column heading or for a row the bottom border of a row heading
When would you use a waterfall chart?
used to track the effect of adding and subtracting values within a sum. Used to show the impact of revenue and expenses in profit and loss statements