Excel Functions and Formulas Quiz

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.