1350 final quick check answers

The first value in a series is 100. Extrapolate the next four values if each value grows by 18% over the previous value?

118, 139.24, 164.30, 193.88

The first value in a linear trend is 50 and the fifth value is 475. What are the values of the second, third, and fourth items?

156.25, 262.50, 368.75

The first value in a series is 100. Extrapolate the next four values assuming a linear trend with a step size of 125?

225, 350, 475, 600

How would the number 00514 appear in a cell?

514

The first value in a growth trend is 50 and the fifth value is 475. What are the values of the second, third, and fourth items?

88.78, 154.11, 270.56

What is a formula add values in cells A1, B1, and C1?

=A1+B1+C1

What is the formula to calculate the average of the Income field from the Employees table?

=AVERAGE(Employees[Income])

What is the function to calculate the average of the cells in the range C1:C50 for which the adjacent cell in the range B1:B50 equals "B"?

=AVERAGEIF(C1:C50, "B", B1:B50)

What is a standard PivotTable?

A standard PivotTable is a PivotTable that is based on an Excel table or data range and not the Power Pivot Data model.

How is the value of a calculated field summarized within a PivotTable?

All calculated fields are summarized using the SUM function.

What is the cell reference for the cell located in the third column and fourth row of a worksheet?

Answer: Cell C4

What is the range reference for the block of cells D3 through E10?

D3:E10

Cell B2 contains the entry May 3, 2025. Why doesn't Excel consider this a text entry?

Dates and times are stored as numbers and not as text.

What does Excel do to date fields that are added to a PivotTable?

Excel automatically groups the date values into quarters, months, and years if the dates span more than one year.

Can you create a PivotChart without a PivotTable?

No, every PivotChart must be based on a PivotTable somewhere within the workbook.

Can any Excel chart type be turned into a PivotChart?

No, only the following chart types can become PivotCharts: Column, Line, Pie, Bar, Area, Surface, Radar, and Combo charts created from the preceding chart types.

Before you can add subtotals to a data range, what must you first do with the data?

Sort the data range by order the field for which you want the subtotals calculated.

How is text aligned within a worksheet cell by default?

Text is left-aligned in a cell by default.

What happens to banded rows in an Excel table when you insert or delete a row?

The formatting adjusts to retain the banded row effect.

What is the purpose of the Watch Window?

The purpose of the Watch Window is to examine the changing value of cells located throughout the workbook when performing a what-if analysis.

How does XLOOKUP differ from VLOOKUP in how it handles failed matches?

VLOOKUP will always return the error value #N/A in the case of failed matches. XLOOKUP allows you to specify a return value in the case of a failed match.

By default, how does a PivotTable arrange the categories in a row or column?

alphabetically for text fields; by date for date fields

What are two ways of filtering a PivotTable?

by adding a field to the Filters area of the table or by using the filter buttons for the fields in the row and column areas.

What is default statistic is used for non-numeric data in the Values area of the PivotTable?

count

What are two ways of manually sorting a PivotTable field?

rag and drop the items in the field or type the item labels in the order you prefer.

What are the four areas of a PivotChart?

the Axis (Category) area, the Legend (Series) area, the Values area, and the Filters area

What are the four primary areas of a PivotTable?

the Rows area, the Columns area, the Values area, and the Filters area

When you do you need to refresh a PivotTable?

whenever the data source is changed by modifying a data value or adding new data records

Provide the value of the asset in Year 1 through Year 5 using the declining balance depreciation schedule in Question 8?

year 1 = $16,540; Year 2 = $13,678.58; Year 3 = $11,312.19; Year 4 = $9,335.18; Year 5 = $7,736.73

What is the function to count the number of numeric values in the range A1:A30?

=COUNT(A1:A30)

What is the function to count the number of cells in the range B1:B50 that equal "B"?

=COUNTIF(B1:B50, "B")

Write a formula to calculate the average of the filtered values from the Sales Price field in the Sales_Result table.

=SUBTOTAL(1, Sales_Result[Sales Price]

Write a formula to calculate the sum of the filtered values from the Sales Price field in the Sales_Result table.

=SUBTOTAL(9, Sales_Result[Sales Price])

What is a function that will add the cell values of A1, B1, and C1?

=SUM(A1:C1)

What is a formula to sum all numeric values in column C?

=SUM(C:C)

Cell E11 contains the formula =SUM(E1:E10). How is the formula adjusted when a new row is inserted above row 5?

=SUM(E1:E11)

What is the function to calculate the sum of the values in the range D1:D50 for which in the adjacent value in the range A1:A50 equals "Senior" and the adjacent value in the range B1:B50 equals "B"?

=SUMIFS(D1:D50, A1:A50, "Senior", B1:B50, "B")

A school gives out grades in the following ranges: F: 0 - < 60; D: 60 - < 70; C: 70 - < 80; B: 80 - < 90; A: 90 - 100. Create a vertical lookup table for this grade scale with the range values in the first column and the letter grades in the second.0 F60 D70 C80 B90 AWhat is the VLOOKUP function to retrieve the letter grade stored in the second column of the GradeScale table using a lookup value of 83 in an approximate match lookup?

=VLOOKUP(83, GradeScale, 2)

Write a formula to retrieve the value from the third field in the Sales_Result table that exactly matches the lookup value in cell B10.

=VLOOKUP(B10, Sales_Result, 3, FALSE)

What is the XLOOKUP function to perform an approximate match lookup for a test score of 83? Assume the column containing the lower end of the range of test scores is named TestScores and the range containing the grades is named Grades.

=XLOOKUP(83, TestScores, Grades,,-1)

What is a PivotTable?

A PivotTable is an interactive table that groups and summarizes data in a concise tabular format.

How do you allow a PivotTable field to include both a Date filter and a Value filter?

By modifying the properties of the PivotTable to allow for multiple filters within the same field

When sorting a PivotTable by values in a field, how are the field values sorted by default?

By the grand total of the field across all items, even if the grand total doesn't appear in the PivotTable

How does the Compact layout differ from the Tabular layout?

Compact layout places all fields from the Rows area in a single column and with subtotal rows placed at the top of each row group. Tabular layout places those fields in separate columns with subtotal rows placed at the bottom of each row group.

If you want to sort employees by the value of the Hire Date field within each value of the Dept field, which field is the primary sort field? Which is the secondary sort field?

Dept is the primary sort field; Hire Date is the secondary sort field.

How do you AutoFit a column to match its longest entry?

Double-click the right border of the column heading or click the Format button in the Cells group on the Home tab and click Autofit Column Width.

By what percentage do the values in Question 3 grow?

Each value grows by 77.56% over the previous value.

What is the reference to the Income field from the Employees table?

Employees[Income]

You receive $50 at the end of Year 1 from an investment, $75 at the end of Year 2, and $100 at the end of Year 3. If the rate of return is 6%, what is the present value of this investment? Show the formula and formula results you used to answer this question?

Enter the Excel formula =NPV(6%, 50, 75, 100), which returns the value $197.88.

For the loan conditions specified in Question 6, what are the formulas to calculate how much interest the business will pay in the first year and how much the business will repay toward the principal? What are the resulting values?

Enter the formula =CUMIPMT(5.3%/12, 10*12, 250000, 1, 12, 0) to calculate the total interest payments during the first 12 months of the loan. The formula returns the value ($12,781.31), indicating that almost $12,800 would be paid toward interest in the first year. To calculate the total paid towards the principal in the first year, enter the formula =CUMPRINC(5.3%/12, 10*12, 250000, 1, 12, 0). The formula returns the value ($19,480.07), indicating that about $19,500 of the principal will be paid off in the first year.

For the loan conditions in Question 6, calculate the total cost of the loan in terms of the total interest paid through the 10 years of the loan?

Enter the formula =CUMIPMT(5.3%/12, 10*12, 250000, 1, 120, 0) to calculate the total interest payments through all 120 months of the loan. The formula returns the value ($72,613.84), indicating that the loan will cost more than $72,600.

Assume a declining balance depreciation for the computer equipment described in Question 6 and provide the formula and result to determine the depreciation in the first year?

Enter the formula =DB(20000, 3000, 10, 1), which returns the value $3,460.

What is the formula to calculate how much a savings account would be worth if the initial balance is $1,000 with monthly deposits of $75 for 10 years at 4.3% annual interest compounded monthly? What is the formula result?

Enter the formula =FV(4.3%/12, 10*12, -75, 1000), which returns the value $9,684.13

Calculate the internal rate of return for the investment in Question 4. If another investment is available that pays a 7.3% rate of return, should you take it? Show the formula and formula results you used to answer this question?

Enter the formula =IRR({ 350, 75, 75, 75, 75, 75, 75}). Excel returns the value 7.69%. You should take this investment over one offering a 7.3% internal rate of return

Rerun your calculations from Question 4 assuming that the business can afford only a $1,000 monthly payment. What is the revised formula and resulting value? How do you explain the result?

Enter the formula =NPER(4.8%/12, -1000, 250000), which returns the value #NUM!, indicating that the monthly payments are insufficient to pay back the loan.

How do you display the rank (smallest to largest) of a PivotTable field?

Open the Value Field Settings dialog box for the field, go to the Show Values As tab, and then select Rank Smallest to Largest from the calculation options

If you split the worksheet into panes at cell E3, how many panes are created?

Four panes are create

What are the three freeze pane options?

Freeze panes for panes placed at the top and left of the workbook window, Freeze Top Row to freeze the worksheet's top, and Freeze First Column to freeze the worksheet's first column.

When an ascending sort order is used, how is a date-time field sorted?

From the earliest date and time to the latest

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

Why is it not a good idea for a company to treat employee records with duplicate first and last names as duplicate records?

In a large enough company, there is a risk that two different employees will have the same name.

When highlighting duplicate values with a conditional format, do the duplicate values have to be adjacent to each other?

No, duplicate values located anywhere within the selected range will be highlighted by the conditional format rule.

Can slicers be moved to any worksheet or external workbook?

No, slicers can be moved to another worksheet but not to another workbook.

Can slicers be used with both data ranges and Excel tables?

No, slicers cannot be used with data ranges.

Can a timeline slicer be used with Excel tabs as well as PivotTables?

No, they can be used only with PivotTables; they cannot be used with Excel tables.

Can a worksheet have only one window?

No; a single workbook can have multiple workbook windows.

Describe 4 ways of viewing worksheet contents in Excel?

Normal View; Page Layout View; Page Break View; Formula ViewHow are page breaks indicated in Page Break View?Page breaks are identified with blue dotted lines.

Conditional formats can be applied to which parts of a PivotTable?

To selected cells, to all cells showing the value of a particular field, and to all cells at the intersection of specified fields

What are two reasons for using filter buttons rather than slicers to filter data?

Use filter buttons when you want to apply text filter, date filters, and numeric filters, and when you want to view all of the record in place as you filter the data.

What is the difference between a calculated item and a calculated field?

A calculated item is user-defined formula that is applied to items with a field and appears in the PivotTable as just another field item. A calculated field is a user-defined formula that is applied across fields and appears as a new field in the PivotTable.

What is a field? What is a record?

A field is a single value that describes a person, place, or thing. A record is a group of related fields.

When a PivotTable field is manually grouped, how does Excel treat the grouped items?

As a new field in the PivotTable field list

(Question 4) A business takes out a loan for $250,000 at 4.8% interest compounded monthly. If the business can afford to make monthly payments of only $1,500 on the loan, what is the formula to calculate the number of months required to repay the loan completely? What is the formula result?

Enter the formula =NPER(4.8%/12, -1500, 250000), which returns the value 275.2020128. Based on the formula result, it will take 276 months (23 years) to pay off the loan.

You spend $350 on an investment that pays $75 per year for the next six years. If you make the investment immediately, what is the net present value of the investment? Assume a 6% rate of return. Show the formula and formula results you used to answer this question.

Enter the formula =NPV(6%, 75, 75, 75, 75, 75, 75)-350, which returns the value $18.80.

Suppose that instead of spending $350 immediately on an investment, you spend $350 one year from now and then receive $75 per year for the next six years after that. What is the net present value assuming a 6% rate of return? Show the formula and formula results you used to answer this question?

Enter the formula =NPV(6%, −350, 75, 75, 75, 75, 75, 75), which returns the value $17.74.

You want a savings account to grow from $1,000 to $5,000 within two years. Assume the bank provides a 3.2% annual interest rate compounded monthly. What is the formula to calculate how much you must deposit each month to meet your savings goal? What is the formula result?

Enter the formula =PMT(3.2%/12, 2*12, 1000, 5000), which returns the negative cash flow value ($245.09). Based on the formula result, you will need to deposit about $245 per month in the savings account to reach the savings goal.

If you take out a loan for $200,000 that must be repaid in 10 years with quarterly payments of $7,200, what is the formula to calculate the annual interest rate of the loan? What is the result?

Enter the formula =RATE(4*10, -7200, 200000)*4, which returns the value 7.65%

A new business buys $20,000 worth of computer equipment. If the useful life of the equipment is 10 years with a salvage value of $3,000, provide the formula to determine the depreciation during the first year assuming straight-line depreciation. What is the formula result?

Enter the formula =SLN(20000, 3000, 10), which returns the value $1700.00.

What happens when you place a date field in a row or column area?

Excel automatically groups the dates, creating new fields for month, quarter, and year.

What is a potential mistake you could make with calculated fields?

Performing a nonsensical calculation in the PivotTable's grand total row or subtotal row by adding up fields that should not be summed.

Explain the difference between positive and negative cash flow. If you borrow $20,000 from a bank, is that a positive or negative cash flow? Explain your answer.

Positive cash flow is money flowing to you or your company. Negative cash flow is money flowing away from you or your company. Borrowing $20,000 from a bank is a positive cash flow because the money is flowing to you.

How do you create a worksheet group consisting of sheets that are not adjacent in a workbook?

Press and hold CTRL as you click each sheet tab that you want to add to the worksheet group.

When tracing an error with the auditing tools, what do red tracer arrows indicate?

Red tracer arrows indicate that the cells connected to the active cell contain errors.

What default statistic is used for numeric data in the Values area of the PivotTable?

SUM

How do you apply the same slicer to multiple PivotTables?

Select the slicer, click the Report Connections button in Slicer group on the Slicer Tools Options tab, and then select the PivotTables that the slicer is applied to.

For the loan conditions specified in Question 6, provide formulas to calculate the amount of the first payment used for interest and the amount of the first payment used to repay the principal. What are the resulting values

The amount of interest paid in the first month is calculated with the formula =IPMT(5.3%/12, 1, 10*12, 250000), which returns the value ($1,104.17). The principal payment in the first month is calculated with the formula =PPMT(5.3%/12, 1, 10*12, 250000), which returns the value ($1,584.28).

What are the three arguments of an Excel Database function?

The database argument that references the range containing the data, the field argument that specifies which field to retrieve data on, and the criteria argument that specifies a range containing filter criteria to be applied to the data.

What happens to worksheet rows that do not match the filter criteria? What happens to the data they contain?

They are hidden in the worksheet, but the data is not removed.

If motels are grouped by size in one PivotTable, how are they displayed in other PivotTables sharing the same cache?

They will also be grouped by size.

(Question 6) A business takes out a 10-year loan for $250,000 at 5.3% interest compounded monthly. What is the formula to calculate the monthly payment and what is the resulting value?

To calculate the monthly payment, enter the formula =PMT(5.3%/12, 10*12, 250000), which returns the negative cash flow value ($2,688.45).

When would you use a Label filter in a PivotTable?

To filter the values of a field by the item labels within the field

When would you use a Value filter in a PivotTable?

To filter the values of a field by the item labels within the field

When would you use an advanced filter in place of the filter buttons?

To join several fields with an OR logical operator

What are two reasons for using slicers rather than filter buttons to filter data?

Use slicers when you want to filter the data into a few distinct categories that can be easily listed within the slicer and when you want to perform the filter on a separate worksheet from the data.

If the annual rate of return is 5%, is $95 today worth more than, less than, or the same as $100 a year from now? Show the formula and formula results you used to answer this question.

Use the formula =PV(5%, 1, 0, 100) to calculate the present value of $100 a year from now. The formula returns the negative cash flow value ($95.24), indicating that receiving $100 a year from now is a fair exchange for spending $95.24 today. In other words, $100 a year from now is worth more than $95 today by 24 cents.

How does XLOOKUP differ from VLOOKUP in the placement of the column of lookup values?

With VLOOKUP, the lookup column must be the first column in the table. With XLOOKUP, the column can be placed anywhere within the table or even outside of the table entirely.

When multiple filter buttons are used with a data range, how are criteria in different fields combined?

With an AND logical operator

How are chart sheets different than worksheets?

Worksheets contain a grid of cells in which text and formulas can be entered and can also contain graphical objects. Chart sheets contains charts and graphic objects but do not contain grids in which text and formulas can be entered.

Provide the value of the asset in Year 1 through Year 5 using the depreciation schedule in Question 6.

Year 1 = $18,300; Year 2 = $16,600; Year 3 = $14,900; Year 4 = $13,200; Year 5 = $11,500

What is the structural reference to the table header row?

[#Headers]