BIS UMAINE Moduel 4
Calculate the total for cells B4, B5, and B6 in cell B8. AutoFill that total through cell M8. Then, in cell B9, calculate the sales tax through the rate in cell Q2. AutoFill those values through to cell M9.
Click in cell B8 Type: =B4+B5+B6 Click the fill handle in cell B8 and drag it through cell M8 Click in cell B9 Type: =B8*$Q$2 Click the fill handle in cell B9 and drag it through cell M9
In cell B9, multiply the contents of cell B8 and the named range, TaxRate. Then, AutoFill the formula to cell M9
Click in cell B9 Type: =B8 * TaxRate Click the fill handle in cell B9 and drag it through cell M9
Use the appropriate formulas and Autofill to display the names from cells A2 through B4 in cells D2 through E4, using all capital letters. Display the names from cells A2 through B4 in cells G2 through H4, using all lowercase letters. Use cells J2 through J4 to display the length of the text in cells A2 through A4.
Click in cell D2 Type: =UPPER(A2) Click the fill handle and drag to cell E2 Click the fill handle and drag to cell E4 Click in cell G2 Type: =LOWER(A2) Click the fill handle and drag to cell H2 Click the fill handle and drag to cell H4 Click in cell J2 Type: =LEN(A2) Click the fill handle and drag to cell J4
In cell D3, add a function to get the two leftmost numbers from cell B3. In cell E3, add a function to get the middle three numbers from cell B3. In cell F3, add a function to get the two rightmost numbers from cell B3. AutoFill the three results down through the remaining products.
Click in cell D3 Type: =LEFT(B3,2) Click in cell E3 Type: =MID(B3,4,3) Click in cell F3 Type: =RIGHT(B3,2) Select cells D3 through F3 Click the fill handle and drag through row 8
Display the combined first and last name from cells A2 and B2 in cell L2, adding a space in between the first and last name. Fill those values for the other two names. In cell N2, combine the values of cells H2 and G2 with a delimiter of a comma and ignoring empty cells. Fill those values for the other two names.
Click in cell L2 In the Formulas tab, Function Library group, click the Text drop-down arrow Click CONCAT Click cell A2 to populate the Text1 field Click in the Text2 field Type: " " Click in the Text3 field Click cell B2 Click the OK button Ensure that cell L2 is still selected and click the fill handle and drag to cell L4 Click in cell N2 In the Formulas tab, Function Library group, click the Text drop-down arrow Click TEXTJOIN In the Delimiter field type:"," Click in the Ignore_empty field Type: TRUE Click in the Text1 field Type: H2 Click in the Text2 field Type: G2 Click the OK button Ensure that cell N2 is still selected and click the fill handle and drag to cell N4
Below each of the headings for Sum, Average, Min, and Max, add functions to get the sum, average, maximum, and minimum values for cells B6 through M6. Then, AutoFill the results down through all of the morning classes
Click in cell O6 Type: =SUM(B6:M6) Click in cell P6 Type: =AVERAGE(B6:M6) Click in cell Q6 Type: =MIN(B6:M6) Click in cell R6 Type: =MAX(B6:M6) Select cells O6 through R6 Click the fill handle and drag down through row 20
In cell Q2, insert a function that displays a YES if the total number of morning classes for January is at least 175 and a NO if not.
Click in cell Q2 Click the Formulas tab In the Function Library group, click the Logical drop-down arrow Click IF In the Logical_test field type: SUM(S6:S20)>=175 Click in the Value_if_true field Type: YES Click in the Value_if_false field Type: NO Click the OK button
In cell S6, use a function to display a count of the cells from B6 through M6 that contain a number. In cell T6, use a function to display a count of the cells from B6 through M6 that contain nothing. AutoFill both cells down through all of the morning classes.
Click in cell S6 Type: =COUNT(B6:M6) Click in cell T6 Type: =COUNTBLANK(B6:M6) Select cells S6 and T6 Click the fill handle and drag down through row 20
