Excel Functions and Formulas Quiz

Podcast

Play an AI-generated podcast conversation about this lesson

Questions and Answers

Enter a formula using the VLOOKUP function to find the meaning for the medical abbreviation listed in cell A3.

=VLOOKUP(A3, Abbreviation, 2, FALSE)

What action can you take to hide all of the dependency tracer arrows at once?

Click the Remove Arrows button in the Formula Auditing group.

In cell E15, enter a formula to find the lowest line item cost this month.

=MIN(E2:E14)

On the Year1 sheet in cell B8, how do you display the value of cell B7 from the Salaries sheet?

=Salaries!B7 Signup and view all the answers

How do you insert the current date and time in cell A1?

=NOW() Signup and view all the answers

In cell D15, enter a formula to count the number of non-blank cells in the Billable? column.

=COUNTA(D2:D14) Signup and view all the answers

Enter a formula in cell B10 to return a value of 35000 if the Net Profit After Tax is greater than or equal to 350000 or 1000 if it is not.

=IF(B9>=350000, 35000, 1000) Signup and view all the answers

Using cell references, how do you enter a formula in cell B6 to calculate monthly payments?

=PMT(B3/12, B4, -B2) Signup and view all the answers

Enter a formula in the selected cell to calculate the profit projection for 2017.

=F4-F5 Signup and view all the answers

How do you show the tracer arrows from the precedent cells to cell C7?

Click Trace Precedents in the Formula Auditing group. Signup and view all the answers

Use the Create from Selection command to create named ranges for the selected data table in cells B2:E6.

Select Create from Selection in the Defined Names group. Signup and view all the answers

Edit the formula in cell D2 to update references to C2 when copied and keep B9 constant.

=C2+(C2*$B$9) Signup and view all the answers

In cell E15, enter a formula to find the highest line item cost this month.

=MAX(E2:E14) Signup and view all the answers

Show the tracer arrows from cell C2 to the dependent cells.

Click Trace Dependents in the Formula Auditing group. Signup and view all the answers

In cell E15, enter a formula using a counting function to count the numbers in the Cost column.

=COUNT(E2:E14) Signup and view all the answers

Enter a formula in the selected cell to display the owner's draw percentage (cell B6).

=B6 Signup and view all the answers

In cell D16, enter a formula using a counting function to count the number of blank cells in the Billable? column.

=COUNTBLANK(D2:D14) Signup and view all the answers

Insert the current date in cell A1 without the current time.

=TODAY() Signup and view all the answers

Enter a formula in cell E4 to calculate the average value of cells B4:D4.

=AVERAGE(B4:D4) Signup and view all the answers

In cell C12, enter a formula using a counting function to count the number of items in the item column.

=COUNTA(C2:C11) Signup and view all the answers

Study Notes

VLOOKUP Function

Use VLOOKUP to find meanings of medical abbreviations, with the lookup value in cell A3 and the table named "Abbreviation."
Formula format: =VLOOKUP(A3, Abbreviation, 2, FALSE)

Dependency Tracer Arrows

To hide all dependency tracer arrows, navigate to Formulas tab > Formula Auditing group and click the "Remove Arrows" button.

Minimum Cost Calculation

Find the minimum line item cost from the range E2:E14 using the MIN function.
Access through Formulas tab, Function Library, then AutoSum > MIN.

Referencing Another Sheet

Reference another worksheet by entering = in cell B8, then select B7 from the Salaries sheet.

Current Date and Time

To insert current date and time, use the NOW function located in the Date & Time dropdown under Function Library.

Counting Non-Blank Cells

Count non-blank cells in a specific column (D2:D14) with COUNTA.
Access via Formulas tab, then select More Functions, point to Statistical > COUNTA.

Conditional Profit Calculation

Use the IF function in cell B10 to return 35000 if the Net Profit (B9) is ≥ 350000; otherwise, return 1000.
Formula: =IF(B9>=350000,35000,1000)

Monthly Payments Calculation

Calculate monthly payments using the PMT function, referencing B3 (rate), B4 (nper), and using -B2 (Pv).
Access through Formulas tab > Function Library > Financial > PMT.

Profit Projection Formula

Calculate profit projection for 2017 by subtracting cost of goods sold (F5) from total sales (F4).
Formula: =F4-F5

Tracing Precedents

To show tracer arrows from precedent cells to cell C7, navigate to Formulas tab > Formula Auditing > click "Trace Precedents."

Create Named Ranges

Use Create from Selection to define named ranges based on labels in row 1 for the data in cells B2:E6.
Located under Formulas tab > Defined Names group.

Editing Formulas with AutoFill

Update the formula in D2 to automatically adjust C2 while keeping reference to B9 constant using absolute reference: =C2+(C2*$B$9).
Use AutoFill to copy the formula down to D6.

Maximum Cost Calculation

Determine the highest line item cost in E2:E14 using the MAX function, accessed through AutoSum in the Formulas tab.

Tracing Dependents

To show tracer arrows from cell C2 to dependent cells, navigate to Formulas tab > Formula Auditing > "Trace Dependents."

Counting Numeric Values

Count the total numbers in the Cost column (E2:E14) using the COUNT function found under AutoSum.

Owner's Draw Percentage

Display owner's draw percentage by entering =B6 in the selected cell.

Counting Blank Cells

Count the blank cells in the Billable? column (D2:D14) using the COUNTBLANK function.
Selected via More Functions > Statistical > COUNTBLANK in the Formulas tab.

Current Date Only

To insert the current date in A1 without time, use the TODAY function located in the Date & Time button of Function Library.

Average Value Calculation

Calculate the average value of cells B4:D4 using the AVERAGE function under AutoSum in Function Library.

Item Count

Count the number of items in the item column (C2:C11) using the COUNTA function through More Functions > Statistical.

Studying That Suits You

Use AI to generate personalized quizzes and flashcards to suit your learning preferences.

Description

Test your knowledge of essential Excel functions such as VLOOKUP, MIN, and NOW, while learning how to manipulate and reference data across sheets. This quiz covers practical applications of formulas in Excel, making it perfect for anyone looking to enhance their spreadsheet skills.