|
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. |
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 |
|
Hide all of the dependency tracer arrows at once |
Formulas tab > in the Formula Auditing group, click the Remove Arrows button |
|
In cell E15, enter a formula to find the lowest line item cost this month (cells E2:E14). |
Formulas tab > in the Function Library click the arrow under AutoSum and select MIN, for E2 thru E14 |
|
On the Year1 sheet in cell B8, enter a formula to display the value of cell B7 from the Salaries sheet. |
select B8, enter = then select Salaries sheet and then cell B7, press enter |
|
Insert the current date and time in cell A1 |
Formulas tab > in Function Library, select arrow below Date & Time, then select NOW |
|
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. |
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 |
|
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. |
enter =IF(B9>=350000,35000,1000) =IF(A>=B,yes,no) |
|
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. |
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 |
|
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). |
enter =F4-F5 |
|
Show the tracer arrows from the precedent cells to cell C7 |
Formulas tab > Formula Auditing group click Trace Precedents |
|
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. |
Formulas tab > Defined Names group > select Crate from Selection > select top row, click ok |
|
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. |
=C2+(C2*$B$9) Then grab the little square on bottom right and drag to D6 |
|
In cell E15, enter a formula to find the highest line item cost this month (cells E2:E14). |
formulas tab > AutoSum > select MAX, make sure E2:E14 are selected, press enter |
|
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). |
formulas tab > formula auditing group > click trace dependents |
|
In cell E15, enter a formula using a counting function to count the numbers in the Cost column (cells E2:E14). |
formulas tab > functional library > autoSum > Count Numbers > select cells |
|
Enter a formula in the selected cell to display the owner’s draw percentage (cell B6) |
=B6 |
|
In cell D16, enter a formula using a counting function to count the number of blank cells in the Billabe? Column (cells D2:D14). |
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 |
|
Insert the current date in cell A1. Do not include the current time. |
formulas tab > function library group > click Date & Time button. Click TODAY, click ok |
|
Enter a formula in cell E4 to calculate the average value of cells B4:D4 |
formulas tab > function library > autosum > average |
|
In cell C12 enter a formula using a counting function to count the number of items in the tiem column (cells C2:C11) |
formulas tab > function library group > click more functions, point to statistical and select COUNTA |
Share This
Flashcard
