Excel Formulas Only Training

In the Time Card sheet, Cell D30, change the formula to get the SUM of cells D21:D27 and multiply the result by cell D29.

1. Click the Time Card sheet, and select Cell D30. 2. Change the current formula to read=SUM(D21:D27)*D29. Press Enter.

In the Review worksheet, use the CONCATENATE function to display the contents of cell B4and A4 separated by a space in cell J4.

1. Highlight Cell J4, in the Formulastab, go to the Function Library and click Insert Function. 2. In the dialog box inside the Search for a function box enter Concatenate. Click Go, and select from the list below. 3. In the Text1 field enter: B4 in theText2 field enter a Space. In the Text3field enter: A 4. Click Ok.

Use the AutoSum tool to get the SUM of the values for each cell (B through F) in the Total row.

1. Highlight cells B16:F16. 2. In the Home tab, go to theEditing group and click AutoSum

In the Combined Sales worksheet, calculate the sales percentage for each representative in the % Total column and change the cell formatting to Percentage.

1. If the Combined Sales worksheet is not open, click on the Combined Salestab. 2. Click on cell F3 and type the formula =E3/$E$8. 3. Hit the ENTER key. 4. Click on cell F3 again. 5. Move your cursor over the AutoFillhandle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross. 6. Click and drag down to cell F7. 7. When you release the mouse button, the cell range should now display sales percentage for the other representatives. 8. Select the cells F3:F8. 9. On the Home tab, Number group click % symbol.

In the Summary worksheet, determine the total units sold by flavor for each quarter based on the values in the 2009 Sales and 2010 Sales worksheets.

1. If the Summary worksheet is not open, click on the Summary tab. 2. Select cell B3. 3. In the Formulas tab, Function Library group, click the AutoSumbutton. 4. Ensure that your cursor is blinking between the parentheses in the text=SUM(). 5. Open the 2009 Sales worksheet. Click once on cell B3. 6. Hold down the SHIFT key and then open the 2010 Sales worksheet. This will create a 3-D reference across both worksheets. 7. Hit the ENTER key and then click once on cell B3. 8. Move your cursor over the AutoFillhandle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross. 9. Click and drag down to cell B25. 10. When you release the mouse button, the cell range should now display the units sold in the first quarter of 2009 and 2010 for each flavor. 11. Move your cursor over the AutoFill handle at the lower-right corner of cellB25. The pointer should change from a thick white cross to a thin black cross. 12. Click and drag down to cell E25. 13. When you release the mouse button, the cell range should now display the units sold in the remaining quarters of 2009 and 2010 for each flavor

In the Top Performers worksheet, determine the highest sales number for 2011 in cell B3.

1. If the Top Performers worksheet is not open, click on the Top Performers tab. 2. Select cell B3. 3. In the Formulas tab, Function Library group, click the drop down for AutoSum. Select Max. 4. Ensure that your cursor is blinking between the parentheses in the text =MAX(). 5. Open the Sales 2011 worksheet. Select cellsD3:D7. 6. Hit the ENTER key.

In cell B18, enter a cell reference to cell A7.

1. In cell B18, begin a function by typing =. 2. Click in cell A7, and hit Enter.

Without using the average function, create a formula in the cell H4 that gets the SUM of the values in the cell E4 through G4 and divides the sum by three.

1. In cell H4 type: =SUM(E4:G4)/3

In the Budget Plan Chart worksheet, create a reference in cell E42 to EventTotal, cell F42 to ProTotal and G42 to AdTotal

1. In the Budget Plan Chart worksheet, select cell E42. 2. Begin a cell reference by typing =, and then enter the predifined named range EventTotal. 3. Follow steps 1 and 2 for cell F42 with named range ProTotal, and cell G42with named range AdTotal. Press Enter

In the Expenses worksheet, in cell D46, use the SUM formula to get the sum of cell range D41:D45

1. In the Expenses worksheet, select D46 and enter the formula, =SUM(D41:D45). Press Enter.

In the Expenses worksheet, in cell F54, create a formula that gets the AVERAGE of each Event item cost using the data in the Actualcolumn heading in column D. Exclude the total rows.

1. In the Expenses worksheet, select cell F54, in the Formulas tab, go to the Function Library and click Insert Function 2. In the Search field enter Average, click Go. Under the Select a function field, select AVERAGE and click OK. 3. Starting with the Number1 field select or enter D6:D9, in the Number2 field D13:D17, in the Number3 field D21:D23, in the Number4field D27:D30, in the Number5 field D34:D37, in the Number6 field D41:D45, in the Number7field D49:D50. 4. Click OK.

In the June worksheet, in cells E4:E33 use the IF formula to display a 1 if the values of the cells D4:D33 are greater than 2500, and a 0 if it is less than or equal to 2500.

1. In the June worksheet, highlight the cell E4. 2. In the Formulas tab, go to the Function Library and click Insert Function. 3. In the dialog box select the IFfunction and click Ok. 4. In the new window for: Logical_testenter: D4>2500 and for Value_if_true: 1, and Value_if_false: 0 and click Ok. 5. Highlight the cells E4: E33 and in the Home tab, go to the Editing group and click Fill and select Down.

In the Marketing Budget Plan worksheet, cell E51, insert a COUNTIF formula to count the data in cell range D43:D51 that is greater than 0

1. In the Marketing Budget Plan worksheet, select cell E51. 2. In the Formula tab, locate the Function Library group, and click Insert Function. 3. In the dialog box, search for the COUNTIFfunction. Select it from the list, and click OK. 4. In the Function Arguments dialog box, enterD43:D51 as the range, and ">0" as the Criteria, and click OK.

In the Marketing Budget Plan worksheet, in cells D10, D18 and D25 create a formula that gathers the SUM of the data above in each of the cells.

1. In the Marketing Budget Planworksheet, select cell D10. In the Formulas tab, locate the Function Library group, click the AutoSum dropdown, and select Sum. 2. Enter cell range D5:D9 as the range for the formula, and hit Enter. 3. Repeat steps 1 to 2 for cell D18using cells D13:D17 4. Repeat steps 1 to 2 for cell D25using cells D21:D24

In the Profit - Loss Summary worksheet, apply a cell reference in cell D5 to the Income worksheet cell H31.

1. In the Profit - Loss Summary worksheet, select cell D5, and begin a cell reference by typing =. 2. Click on the Income worksheet, and click cellH31, and hit Enter.

In the Profit - Loss Summary worksheet, name cell C5 EstIncome and C6 EstExpenses. In cell C9 use a formula that subtracts EstExpenses from EstIncome.

1. In the Profit - Loss Summaryworksheet, select cell C5. Right click the cell, and select Define Name. 2. In the dialog box, enter EstIncome. Click Ok. 3. Repeat step 1 and 2 for cell C6 and name EstExpenses before continuing to step 4. 4. In cell C9 enter the formula =EstIncome-EstExpenses to subtract the values of the 2 named ranges. Press Enter.

In the Profit - Loss Summary worksheet, in cell D8, use the IF function to show Close if the SUM of C9:D9 is greater than 22500 and Not Close if it is less than or equal to 22500.

1. In the Profit - Loss Summaryworksheet, select cell D8, in the Formulas tab, in the Function Library group click Insert Function. 2. In the Insert Function dialog box, type IF and click Go. Highlight IFfrom the Select a Function group and click Ok. 3. In the Function Argumentsdialog box, for Logical_test enter SUM(C9:D9)>22500, for Value_if_true enter Close, and for Value_if_false enter Not Close and click Ok.

In the Quarterly Sales worksheet, cell B9, enter a formula that consists of a cell reference to the Annual Sales worksheet, cell F16, multiplied by 1 plus the growth value in the Quarterly Sales worksheet, cell B3.

1. In the Quarterly Sales worksheet,cell B9, begin a formula by entering the= character. 2. Switch to the Annual Salesworksheet, and click on cell F16.3. Type *(1+ and then click on theQuarterly Sales worksheet, cell B3. Finally, close the formula by typing ). Press Enter

In the Totals worksheet enter a formula in cell B2 that displays the SUM of the Fiction12named range.

1. In the Totals worksheet, select CellB2. Click the Formulas tab, locate theFunction Library, click the Auto Sumdropdown, and click Sum. 2. In the formula, type the nameFiction12, and press enter.

In the Totals worksheet in column B, enter a cell reference to the matching Totalsvalues from the June worksheet.

1. In the Totals worksheet, select cell B2 and type = to begin a formula. 2. In the June worksheet, select cell B34, and hit enter. The contents of Cell B2 in the Totals worksheet should read, =June!B34. 3. In the Totals worksheet, select cell B3 and type = to begin a formula. 4. In the June worksheet, select cell C34, and hit enter. The contents of Cell B3 in the Totals worksheet should read, =June!C34. 5. In the Totals worksheet, select cell B4 and type = to begin a formula. 6. In the June worksheet, select cell D34, and hit enter. The contents of Cell B4 in the Totals worksheet should read, =June!D34.

In the Combined Sales worksheet, correct the formula in the cell range D3:D7 so that it calculates the percentage of change from Sales 2010 to Sales 2011.

1. Open the Combined Sales worksheet. 2. Select cell D3. 3. In the Formula Bar, add an opening parenthesis after the text = and a closing parenthesis before the text /. 4. Change 'Combined Sales'!E3 to 'Sales 2010'!D3 5. Hit the ENTER key. 6. Click on cell D3 again. 7. Move your cursor over the AutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross. 8. Click and drag down to cell D7. 9. When you release the mouse button, the cell range should now display change percentage for the other representatives.

In the Top Performers worksheet, determine the highest sales number for 2010 in cell B2.

1. Open the Top Performersworksheet. 2. Select cell B2. 3. In the Formulas tab, Function Library group, click the drop down for AutoSum. Select Max. 4. Ensure that your cursor is blinking between the parentheses in the text=MAX(). 5. Open the Sales 2010. Select cellsD3:D7 6. Hit the ENTER key.

In the Annual Sales worksheet, in cell B17 use the COUNTIF function to count the number of times that the values in cell range B6:B15 exceed 5000.

1. Select cell B17. In the Formulas tab, locate theFunction Library group, and click Insert Function. 2. Type COUNTIF in the Search for a Functionfield. Select COUNTIF from the results and clickOK. 3. In th Range field, type B6:B15, and in theCriteria field, type >50004. Click OK.

In the June worksheet, enter a formula in cell B34 that will get the SUM of the cell range B4:B33. Copy the formula to columns C and D.

1. Select cell B34. In the Formulastab, locate the Function Library group, and click Insert Function. 2. In the dialog box click SUM and click Ok. 3. Enter B4:B33 in the Number1 area and click Ok. 4. Select cells B34:D34. In the Hometab, locate the Editing group, select Fill, and click Right.

In cell D3 create a formula that gets the SUM of cells I6:I33.

1. Select cell D3, enter the formula,=SUM(I6:I33). Press Enter.

In the Mileage Log worksheet, in cell J6, use a formula that subtracts G6 from H6 and multiplies the result by .36.

1. Select cell J6, enter the the formula,=(H6-G6)*.36. Press Enter

Correct the function in the Overall column. The values Profitable or Marginalshould display, depending on whether the quarterly sales exceed research costs.

1. Select cell K3. 2. In the Formula Bar, replace the comma after SUM(D3,F3,H3,J3) with a greater than sign >. Add a quote " after the text Profitable and before the text Marginal. 3. Hit the ENTER key.

In the Mileage Log worksheet, define the name for cell range I6:I33 as Miles. In cell D3, insert a SUMformula to show the sum of the Miles range.

1. Select cell range I6:I33, in the Formulas tab, go to the Defined Names group and click theDefine Name. 2. In the New Name dialog box enter the nameMiles and click Ok. 3. In cell D3, enter the formula, =SUM(Miles). Press Enter.

In the June worksheet, in Cell E34, enter a formula that returns the AVERAGEvalue of all the numbers in the range B4:D33.

1. Select the cell E34. Click the Formulas tab, locate the Function Library group, click theAuto Sum dropdown, and cick Average. 2. In the formula, type B4:D33. Press Enter.

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.

1.Click in cell B8 2.Type: =B4+B5+B6 3.Click the fill handle in cell B8 and drag it through cell M8 4.Click in cell B9 5.Type: =B8*$Q$2 6.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.

1.Click in cell B9 2.Type: =B8 * TaxRate 3.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 lengthof the text in cells A2 through A4.

1.Click in cell D2 2.Type: =UPPER(A2) 3.Click the fill handle and drag to cell E2 4.Click the fill handle and drag to cell E4 5.Click in cell G2 6.Type: =LOWER(A2) 7.Click the fill handle and drag to cell H2 8.Click the fill handle and drag to cell H4 9.Click in cell J2 10.Type: =LEN(A2) 11.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 numbersfrom 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.

1.Click in cell D3 2.Type: =LEFT(B3,2) 3.Click in cell E3 4.Type: =MID(B3,4,3) 5.Click in cell F3 6.Type: =RIGHT(B3,2) 7.Select cells D3 through F3 8.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.

1.Click in cell L2 2.In the Formulas tab, Function Library group, click the Text drop-down arrow 3.Click CONCAT 4.Click cell A2 to populate the Text1field 5.Click in the Text2 field 6.Type: " " 7.Click in the Text3 field 8.Click cell B2 9.Click the OK button 10.Ensure that cell L2 is still selected and click the fill handle and drag to cell L4 11.Click in cell N2 12.In the Formulas tab, Function Library group, click the Text drop-down arrow 13.Click TEXTJOIN 14.In the Delimiter field type:"," 15.Click in the Ignore_empty field 16.Type: TRUE 17.Click in the Text1 field 18.Type: H2 19.Click in the Text2 field 20.Type: G2 21.Click the OK button 22.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.

1.Click in cell O6 2.Type: =SUM(B6:M6) 3.Click in cell P6 4.Type: =AVERAGE(B6:M6) 5.Click in cell Q6 6.Type: =MIN(B6:M6) 7.Click in cell R6 8.Type: =MAX(B6:M6) 9.Select cells O6 through R6 10.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.

1.Click in cell Q2 2.Click the Formulas tab 3.In the Function Library group, click the Logical drop-down arrow 4.Click IF 5.In the Logical_test field type: SUM(S6:S20)>=175 6.Click in the Value_if_true field 7.Type: YES 8.Click in the Value_if_false field 9.Type: NO 10.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.

1.Click in cell S6 2.Type: =COUNT(B6:M6) 3.Click in cell T6 4.Type: =COUNTBLANK(B6:M6) 5.Select cells S6 and T6 6.Click the fill handle and drag down through row 20

In the Commission Rates worksheet, for the cell range A2:C6, define the named range Bonus. In the Combined Sales worksheet, add a right-most column named Bonus Pay and calculate the bonus amount for each sales representative using the Bonus named range.

1.Open the Commission Rates worksheet. 2.Select cell range A2:C6 3.In the Formulas tab, Defined Names group, click Defined Name. 4.In the New Name window enter Bonus for the name. Click OK. 5.Open the Combined Sales worksheet. 6.Select cell F2. 7.In the Home tab, Cells group, click the Insert button and choose the Insert Sheet Columns. 8.Type Bonus Pay and hit ENTER. 9.Select cell F3. 10.In the Formulas tab, Function Library group, select Lookup & Reference. Select VLOOKUP. 11.In the Lookup_value text box, type E3. In the Table_array text box, type Bonus, and in the Col_index_num text box, type 3. 12.Click OK. 13.Select cell F3 14.Move your cursor over the AutoFill handle at the lower-right corner of the cell. The pointer should change from a thick white cross to a thin black cross. 15.Click and drag down to cell F7. 16.When you release the mouse button, the cell range should now display bonus pay amount for the other representatives.