Excel
On the Formulas tab, in the Function Library group, click the More Functions button. Point to Statistical and select COUNTA from the list. Click and drag to select cells C2:C11. Click OK.
In cell C12, enter a formula using a counting function to count the number of items in the Item column (cells C2:C11).
Formula Tab Text -> Text Join Delimiter A2:C2
In cell D2, enter a formula using TEXTJOIN to combine the text from cells A2:C2 to display the customer name, city, state. Use a space as the delimiter. Ignore blank cells. Use a cell range as the Text 1 argument.
On the Formulas tab, in the Function Library group, click the Text button. Select CONCAT from the list. Type B2 in the Text1 box. Type " " in the Text2 box. Type C2 in the Text3 box. Click OK.
In cell D2, use the CONCAT function to combine the values of cells B2 and C2 with a space in between. The result of the formula should look like this: Davidsonville MD
Formula tab More Functions Statistical and select Count Click Ok
In cell F12, enter a formula using a counting function to count #'s in the ordered column (cells F2:F11)
Formula tab More Functions Statistical and select CountBlank Click and drag to select cells in G2:G11 Click Ok
In cell G12, enter a formula using a counting function to count # of blank cells in the Received column (cells G2:G11)
On the Formulas tab, in the Function Library group, click the AutoSum arrow, and click Max. Press Enter.
In cell H12, enter a formula to find the highest percentage of items received in the order (cells H2:H11).
On the Formulas tab in the Function Library group click the AutoSum arrow and click Min. Press Enter.
In cell H12, enter a formula to find the lowest percentage of items received in the order (cells H2:H11).
Date and Time Click Now
Insert the current date and time in the selected cell
Type = and then click the ByMonth sheet tab. Click cell B3. Press Enter.
On the Summary sheet, in cell B3, enter a formula to display the value of cell B3 from the ByMonth sheet.
On the Formulas tab in the Formula Auditing group, click the Show Formulas button. Click the File tab. Click Print.
Preview how this worksheet would print with formulas showing instead of calculated values.
n the Page Layout tab, in the Scale to Fit group, click the Width arrow. Click 1 page.
Change the scaling option so all columns will print on one page.
On the Formulas tab, in the Defined Names group, click the Name Manager button. Click in the Refers to box and edit the cell range reference to =Bonus!$B$2:$B$5. Click the checkmark to the left of the Refers to box to accept the change. Click the Close button.
Edit the Bonuses named range so it refers to cells B2:B5 on the Bonus worksheet. Close the Name Manager when you are finished.
Double-click cell B2 to enter Edit mode. Type: =E8* and then type bo to display the Formula AutoComplete list. Double click BonusRate to enter it into the formula and then press Enter.
Enter a formula in cell B2 to calculate Ken Dishner's bonus for the first quarter. Multiply his sales total (cell E8) times the bonus rate (the cell named BonusRate).
On the Formulas tab, in the Function Library group, click the Lookup & Reference button, select VLOOKUP. Type B1 in the Lookup_value argument box. Type DailySales in the Table_array argument box. Type 5 in the Col_num argument box. Type False in the Lookup_value box. Click OK.
Enter a formula in cell B2 using the VLOOKUP function to find the total sales for the date in cell B1. Use the name DailySales for the lookup table. The total sales are located in column 5 of the lookup table. Be sure to require an exact match
On the Formulas tab, in the Function Library group, click the Text button. Select LOWER from the list. Type A8 in the Text box, and click OK.
Enter a formula in cell B8 to display the text from cell A8 in all lower case letters.
Text Upper A8 in the Text Box Ok
Enter a formula in cell B8 to display the text from cell A8 in all upper case letters
On the Formulas tab, in the Function Library group, click the Text button. Select PROPER from the list. Type A8 in the Text box, and click OK.
Enter a formula in cell B8 to display the text from cell A8 in proper case with only the first letter of each word in upper case.
Formula tab, click logical, select if. Enter E8>=B2 in logical_text box. Enter yes in the Value__if__true box No in the Value__if__false box
Enter a formula in cell C2 to return a value of yes if the value in cell E8 is greater than or equal to the value in B2 or no if it is not
On the Home tab, in the Editing group, click the AutoSum button arrow and select Average. Press Enter.
Enter a formula in cell E4 to calculate the average value of cells B4:D4.
On the Page Layout tab, in the Page Setup group, click the Print Titles button. In the Page Setup dialog, on the Sheet tab, click in the Rows to repeat at top box. Click the row selector for row 1. Click OK in the dialog.
Set row 1 to print on every page.
On the Formulas tab, in the Formula Auditing group, click the Trace Dependents button.
Show the tracer arrows from cell B5 to the cell(s) that are dependent on it (cells containing formulas that reference the value or formula in cell B5).
Formula tab, in the formula auditing group, click the Trace Precedents
Show the tracer arrows from the precedent cells to cell B5
Click on cell D6 Click Smart Tag Click Copy Formula Above
There is an error in cell D6. Accept Excels suggestion for fixing the error
Formulas tab, defined names, click Name Manager button. Click the Total_Q1 name and then click the Delete button. Click Ok. Click close
This workbook includes a named range that results in a #REF! error. Delete that named range
Type = in cell E4 Type SU Double-Click Sum to enter it in your formula Click and drag to select cells B4:D4 Enter
Use Formula AutoComplete to enter a Sum function in cell E4 to calculate the total of cells B4:D4
Math and Trig Sum Number 1 B4:D4 Ok
Use the Function Arguments dialog to enter a SUM function in cell E4 to calculate the total of cells B4:D4
Formula Tab Financial PMt Enter B5/12 in the rate argument box Enter B6 in the Nper argument box Enter -B4 in the Pv argument box Click Ok
Using cell references, enter a formula in cell B7 to calculate monthly payments for the loan described in this worksheet. Use a neg. value for the Pv argument
