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?
Signup and view all the answers
How do you insert the current date and time in cell A1?
Signup and view all the answers
In cell D15, enter a formula to count the number of non-blank cells in the Billable? column.
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.
Signup and view all the answers
Using cell references, how do you enter a formula in cell B6 to calculate monthly payments?
Signup and view all the answers
Enter a formula in the selected cell to calculate the profit projection for 2017.
Signup and view all the answers
How do you show the tracer arrows from the precedent cells to cell C7?
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.
Signup and view all the answers
Edit the formula in cell D2 to update references to C2 when copied and keep B9 constant.
Signup and view all the answers
In cell E15, enter a formula to find the highest line item cost this month.
Signup and view all the answers
Show the tracer arrows from cell C2 to the dependent cells.
Signup and view all the answers
In cell E15, enter a formula using a counting function to count the numbers in the Cost column.
Signup and view all the answers
Enter a formula in the selected cell to display the owner's draw percentage (cell 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.
Signup and view all the answers
Insert the current date in cell A1 without the current time.
Signup and view all the answers
Enter a formula in cell E4 to calculate the average value of cells 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.
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.