question

Enter a formula using the VLOOKUP function to find the meaning for the medical abbreviation listed in cell A3. Use the name Abbreviation for the lookup tabble. Item names are located in column 2. Require an exact match.

answer

Formulas tab > in the Function Library group, click Lookup & Reference button, select VLOOKUP. Type A3 in the Lookup_value argument box. Type Abbreviation in the Table_array argument box. Type 2 in the Col_num argument box. Type False in the Rang_lookup box. Click OK

question

Hide all of the dependency tracer arrows at once

answer

Formulas tab > in the Formula Auditing group, click the Remove Arrows button

question

In cell E15, enter a formula to find the lowest line item cost this month (cells E2:E14).

answer

Formulas tab > in the Function Library click the arrow under AutoSum and select MIN, for E2 thru E14

question

On the Year1 sheet in cell B8, enter a formula to display the value of cell B7 from the Salaries sheet.

answer

select B8, enter = then select Salaries sheet and then cell B7, press enter

question

Insert the current date and time in cell A1

answer

Formulas tab > in Function Library, select arrow below Date & Time, then select NOW

question

In cell D15, enter a formula using a counting function to count the number of cells in the Billable? column (cells D2:D14) than are not blank.

answer

Formulas tab > Function Library, click More Functions, point to statistical and select COUNTA from the list. Click and drag to select cells D2:D14. click ok

question

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

answer

enter =IF(B9>=350000,35000,1000) =IF(A>=B,yes,no)

question

Using cell references, enter a formula in cell B6 to calculate monthly payments for the load descried in this worksheet. Omit the optional argument. Use a negative value for the Pv argument.

answer

Formulas tab > Function Library group, click the Financial button, click PMT. Enter B3/12 in rate, enter B4 in Nper, enter -B2 in the Pv. Click ok

question

Enter a formula in the selected cell to calculate the profit projection for 2017: total sales (cell F4) minus the cost of goods sold (cell F5).

answer

enter =F4-F5

question

Show the tracer arrows from the precedent cells to cell C7

answer

Formulas tab > Formula Auditing group click Trace Precedents

question

Use the Create from Selection command to create named ranges for the selected data table in cells B2:E6 using the labels in row 1 as the basis for the names.

answer

Formulas tab > Defined Names group > select Crate from Selection > select top row, click ok

question

Edit the formula in cell D2 so the references to cell C2 will update when the formula is copied, and the reference to cell B9 will remain constant. Use AutoFill to copy the formula to cells D3:D6.

answer

=C2+(C2*$B$9)
Then grab the little square on bottom right and drag to D6

question

In cell E15, enter a formula to find the highest line item cost this month (cells E2:E14).

answer

formulas tab > AutoSum > select MAX, make sure E2:E14 are selected, press enter

question

Show the tracer arrows from cell C2 to the cells that are dependent on it (cells containing formulas that reference the value or formula in cell C2).

answer

formulas tab > formula auditing group > click trace dependents

question

In cell E15, enter a formula using a counting function to count the numbers in the Cost column (cells E2:E14).

answer

formulas tab > functional library > autoSum > Count Numbers > select cells

question

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

answer

=B6

question

In cell D16, enter a formula using a counting function to count the number of blank cells in the Billabe? Column (cells D2:D14).

answer

formulas tab > function library group > click more functions button, point to Statistical and select COUNTBLANK from the list. Click and drag to select the cells (D2:D14). Click OK

question

Insert the current date in cell A1. Do not include the current time.

answer

formulas tab > function library group > click Date & Time button. Click TODAY, click ok

question

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

answer

formulas tab > function library > autosum > average

question

In cell C12 enter a formula using a counting function to count the number of items in the tiem column (cells C2:C11)

answer

formulas tab > function library group > click more functions, point to statistical and select COUNTA