Excel Lesson 6

Finding the Middle Value with MEDIAN

1. Cell H3 has been selected. Type the formula =MEDIAN(C2:C22) in the formula bar. Press Enter to submit your answer.

Managing Errors with the IFERROR Function.

1. Edit the formula in formula bar to: =IFERROR(VLOOKUP(B1,Stock,3,FA:SE),"item not found")

Creating a Depreciation Schedule 1. In cell b8, enter a formula using the function for straight line depreciation method. Use the cell names Cost, Salvage, and Life as the function arguments. 2. In cell b8, enter a formula using the function for the straight line depreciation method. Use the cell names Cost, Salvage, and Life as the function arguments. 3. In cell d8, enter a formula using the function for the declining balance depreciation method. Use the cell names cost, salvage, and life as the function arguments. Use a relative reference to cell a8 for the period argument. The asset will be in use for only six months in the 1st period. 4. in cell e8, enter a formula using the function for the double declining balance deprecition method. use the cell names cost, salvage, and life as the function arguments 1.5 as mulitplier.

1. Cell B8 has been selected. Click the Formulas tab. 2. On the Formulas tab, in the function library group, click the financial button. 3. Select SLN to insert the Straight-line depreciation function. 4. In the cost argument box, type Cost 5. In the Salvage argument box, type Salvage 6. In the Life argument box, type Life 7. click ok 8. click the financial button again to enter the syd depreciation function. 9.select syd to insert the accelerated depreciation function. 10. the cost, salvage, and life arguments will be entered in the insert function dialog. 11. In the Per argument box, type A8. 12. Click Ok 13. cell d8 has been selected. Click the financial button again to enter the d8 depreciation function. 14. Select DB to enter the Declining balance depreciation function. 15. the animation shows the cost, salvage, life, and period arguments being entered in the insert function dialog. 16. In the month argument box, type 6 17. click ok 18. cell c8 has been selected. Click the financial button again to enter the ddb depreciation function. 19. select ddb to insert the double declining depreciation function. 20. the animation shows the cost, salvage, life and period arguments being entered. 21. in the factor argument box, type 1.5 22. click ok

Using AVERAGEIF and AVERAGEIFS 1. Enter a formula in cell H1 using averageif to calculate the average delivery time for items currently on order (where the value in the On Order? column is yes). Use the range name ReorderStatus for the Range argument and DeliveryTime for the Average range argument. 2.

1. Click the Formulas tab. 2. On the Formulas tab, in the Function Library group, click the More Functions button. 3. Notice that the mouse is pointing to the Statistical category. Select the AVERAGEIF function. 4. In the Range argument box, type ReorderStatus. Press Enter to submit your answer. 5. In the Criteria argument box, type "yes". Press Enter. 6. In the Average_range argument box, type DeliveryTime. Press Enter to submit your answer. 7. Click OK. 8. Cell H2 has been selected. Click the More Functions button again. 9. Click math and trig then click sumifs 10. In the average range argument box, type Delivery Time. Press Enter to submit your answer. 11. In the Criteria range 1 argument box, type Reorder Status. Press Enter to submit your answer. 12. In the Criteria 1 argument box, type "yes:. Press Enter to submit your answer. 13. In the Criteria range 2 argument box, type InStock. Press Enter submit your answer. 14. In the Criteria2 argument box, type 0. 15. Click OK.

Using COUNTIF and COUNTIFS 1. Enter a formula in cell H3 using COUNTIF to count the number of cells where the number of items in inventory is less than five. Use the range name InStock for the Range argument. 2. Now enter a formula in cell H4 using COUNTIFS to calculate the number of rows where the value in the InStock named range is less than five and the value in the DeliveryTime named range is greater than 7.

1. Click the Formulas tab. 2. On the Formulas tab, in the Function Library group, click the More Functions button. 3. Notice that the mouse is pointing to the Statistical category. Select the COUNTIF function. 4. In the Range argument box, type InStock. Press Enter to submit your answer. 5. In the Criteria argument box, type <5. Press Enter to submit your answer. 6. Click OK. 7. Cell H4 has been selected. Click the More Functions button again. 8. Click Statistics and then click Countifs 9. In the Criteria range 1 argument box, type InStock. Press Enter to submit your answer. 10. In the Criteria 1 argument box, type <5. Press Enter to submit your answer. 11. In the Criteria range 2 argument box, type DeliveryTime. Press Enter to submit your answer. 12. In the Criteria2 argument box, type >7. Press Enter to submit your answer. 13. Click OK.

Using PV to calculate Present Value When Payments Are Constant 1. Enter a formula using PV in cell b12 on the retirement planning worksheet to calculate the present value needed to pay a monthly annuity over the next twenty years. Cell b11 is the number of years over which the monthly payments will be made. Cell b9 is the amount of each monthly payment. Omit the Fv argument. Payment will be at the beginning of each period, so set the type argument to the appropriate value. Pay attention to the time periods for the interest rate, payout period, and payment schedule. Remember to express the Pmt argument as negative.

1. Click the Formulas tab. 2. On the Formulas tab, in the Function Library group, click the financial button. 3. select PV 4. In the Rate argument box, type b10/12 5. in the Nper argument box, type b11*12 6. In the Pmt argument box, type -b9 7. In the type argument box, type 1 8. Click Ok.

Analyzing Complex Formulas Using Evaluate Formula 1. Open the Evaluate Formula dialog. "Step in" to review the formula in the first cell reference. Click the step in button to see the value for the referenced cell. 2.

1. Click the Formulas tab. 2. On the formulas tab, in the formula auditing group, click the Evaluate Formula button. 3. click the step in button to see the value for the referenced cell. 4. Click the Step out button 5. click the step in button to go back one level 6. Click the step out button 7. click the evaluate button 8. click the close button

Using NPV to Calculate Present Value when Payments are Variable

1. Click the Formulas tab. 2. On the formulas tab, in the function library group, click the financial button. 3. Click npv 4. In the Rate argument box, type B9. 5. In the Value1 argument box, type c2:c6 6. click ok

Calculating the Number of Payments with NPER 1. In cell b5, enter a formula using NPER to calculate how long your retirement savings will last. The annual interest rate is in cell b4. The current value of the account is in cell b2. The monthly payment is in cell b3. Payments will be made at the beginning of every period. Remember to adjust the interest rate to reflect the same time period as the payments and to express the Pmt argument as a negative.

1. Click the Formulas tab. 2. On the formulas tab, in the function library group, click the financial button. 3. Select NPER 4. In the Rate argument box, type b4/12 5. In the Pmt argument box, type -b3 6. In the Pv argument box, type B2 7. In the Type argument box, type 1 8. click ok

Rounding with Functions 1. Enter a formula in cell B5 using one of the rounding functions to calculate the value of cell b1 divided by cell b2 rounded to two decimal places.

1. Click the Formulas tab. 2. On the formulas tab, in the function library group, click the math and trig button. 3. Select the Round function. 4. In the Number argument box, type b1/b2. Press Enter to submit your answer. 5. In the Num_digits box, type 2. Press Enter to submit your answer. 6. Click Ok 7. on the formulas tab, in the function library group, click the math and trig button. 8. Select the roundup function 9. in the number argument box, type b1/b2. Press enter to submit answer. 10. In the Num_digits box, type 2. Press Enter to submit your answer. 11. Click Ok 12. On the formulas tab, in the function library group, click the math and trig button. 13. Select the rounddown function. 14. In the number argument box, type b1/b2. Press Enter to submit your answer. 15. In the Num_digits box, type 2. Press Enter to submit your answer. 16. Click ok

Exploring More Logical Functions: AND and OR 1. Enter a formula in cell f2 using the AND function to return TRUE if C2 (the value in the ordered column) is equal to D2(the value in the Received column) and the value in cell E2 is yes. Hint: Remember to enclose text strings in quotation marks.

1. Click the formulas tab. 2. On the formulas tab, in the function library group, click the logical button. 3. select and 4. enter c2=d2 in thelogical1 argument box and press Enter to submit your answer. 5. enter e2=yes in the logical2 argument box and press enter to submit your answer. 6. click ok 7. cell h3 has been selected on another worksheet. 8.Click the logical button again to enter the OR function. 9. in the logical 1 argument box, type c2<>D2 and press enter to submit your answer. 10. In the Logical 2 argument box, type e2=no and press enter to submit your answer. 11. Click Ok.

Calculating the Mean Absolute Deviation (MAD) 1. Enter a formula in cell E2 to calculate the absolute value of the value in cell D2. You will be copying this formula, so be sure to use a relative reference. 2. Enter a formula in cell H6 to calculate the mean absolute deviation of the values in cells E2:E22.

1. In cell E2, type=ABS(D2). Press Enter to submit your answer. 2. The formula has been copied from cell E2 to cells E3:22. In cell H6, type =AVERAGE(E2:E22). Press enter to submit your answer.

Ranking Values in a Dataset with Rank.Eq and Rank.AVG 1. Enter a formula in cell E1 to calculate the rank of the value in cell c12 compared to the values in the range c5:c16. 2. Enter a formula in cell E2 to calculate the averaged rank of the value in cell c12 compared to the values in the range c5:c16.

1. On the Formulas tab, in the function library group, click the More Functions button. 2. Click Statistical then select Rank.EQ 3. in the number argument box, type c12 4. in the ref argument box, type c5:c16 and press enter to submit your answer. 5. click ok 6. Click the More Functions button. 7. Notice that the mouse is pointing to Statistical. Select Rank.avg. 8. in the number argument box, type c12 and press enter to submit your answer. 9. in the ref argument box, type c5:c16 and press enter to submit your answer. 10. click ok

Calculating Standard Deviation 1. Enter a formula in cell H4 to calculate the standard deviation of the values in cells C2:C22. Assume this array is a sample of a larger set of values. 2. Enter a formula in cell H5 to calculate the standard deviation of the values in cells c2:c22. This array of values is the entire data set.

1. On the Formulas tab, in the function library group, click the More Functions button. 2. Notice that the mouse is pointing to Statistical. Click STDEV.S. 3. In the Number1 box, type C2:C22. 4. click ok 5. On the Formulas tab, in the Function Library Group, click the More Functions button. 6. Notice that the mouse is pointing to Statistical. Click STDEV.P. 7. In the number 1 box, type C2:C22. 8. click ok

Using the IFS Function 1. enter a formula in cell h2 using the ifs function to display order closed if the value in cell f2 is true, order short if the value in cell d2 is less than the value in cell c2, and invoice due if the value in cell e2 is no.

1. On the formulas tab, in the function library group, click the logical button. 2. select ifs 3. In the logical test1 box, type f2=true. Press Enter to submit your answer. 4. In the Value_if_true1 box, type order closed. 5.In the logical test2 box, type D2<C2. 6. In the value if true 2 box, type Order Short. 7. in the logical test 3 box, type e2=no 8. In the value if true 3 box, type Invoice Due. Press Enter to submit your answer. 9. press ok

Calculating Totals with SUMPRODUCT 1. Enter a formula in cell b1 using the sumproduct function to calculate the total cost of the purchase order. Use the cell ranges g4:g12 and h4:h12.

1. On the formulas tab, in the function library group, click the math and trig button. 2. select sumproduct. 3. In the array 1 argument box, type G4:G12. Press Enter to submit your answer. 4. in the array 2 box, type h4:h12. Press enter to submit your answer. 5. click ok

Extracting Text with Right, Left, and Mid 1. Enter a formula in cell D4 to find the item number by extracting the last four letters of the inventory ID in cell C4. Use cell references where appropriate. 2. Enter a formula in cell E4 to find the vendor ID by extracting the first two digits of the inventory ID in cell c4. Use cell references where appropriate.

1. Press formula tab then press the text button. Then press the right button. Type c4 in text then type 4 in Num_chars then press enter or click ok. 2. Press formula tab then press the text button. Then press the left button. Type C4 in text then type 4 in Num_chars then press enter or ok. 3. Press formula tab then press text button then press the mid button. Type a4 in the text argument box. Type 7 in the Start_num argument box. Type 2 in the Num_chars argument box. Click ok.

Finding Data with Match and Index 1. In cell b2 on the find item worksheet, enter a formula using Match to look up the row position of the item listed in cell b1 in the array named ItemNames. Require an exact match. 2. In cell b3, enter a formula using Index to look up the price of the item at the row position in cell b2. use the named range UpdatedStock as the Array argument. The price is found in the third column of the array. 3. in cell d3, enter a formula using MATCH nested within Index to find the price of the item listed in cell d1. In the index function, use the named range UpdatedStock as the array argument. For the row_num argument, use the MATCH function to look up the row position of the item listed in cell d1 in the array named ItemNames. Require an exact match. The price is found in the 3rd column of the array used in the INDEX function.

1. click the formulas tab 2. On the Formulas tab, in the function library group, click the lookup and reference button. 3. Select MATCH. 4. In the lookup value argument box, type b1 5. in the lookup array argument box, type ItemNames. 6. In the Match_Type argument box, type 0 7. Click OK. 8. Cell b3 has been selected. Click the lookup and reference button again to enter the index function. 9. select index 10. the array, rownum, column num option is selected by default. Click ok. 11. In the array argument box, type UpdatedStock 12. in the row num argument box, type b2 and press enter to submit your answer. 13. In the Column_num argument box, type 3 14. click ok 15. cell d3 has been selected. click the lookup and reference button. 16. Select index 17. the array, row num, column num option is selected. Click Ok. 18. the animation shows the arguments being entered in the function arguments dialog. 19. Click the arrow in the Name box to the left of the formula bar. 20. Select Match 21. the animation shows the arguments being entered in the function arguments 22. Click Ok.

Calculating Future Value with the FV Function 1. Enter a formula in cell e6 using FV to calculate the future value of this investment. Cell e4 is the annual interest rate. CEll e5 is the number of monthly payments. Cell e3 is the amount of payment each month. Remember to express the Pmt argument as a negative and divide the annual interest rate by the number of payments per year. Omit the optional argument.

1. click the formulas tab. 2. On the formulas tab, in the function library group, click the financial button. 3. select fv 4. In the rate argument box, type e4/12 5. in the Nper argument box type E5 6. In the Pmt argument box, type -e3 7. click ok

Using SUMIF and SUMIFS 1. Enter a formula in cell D1 using SUMIF to calculate the total number of items ordered for the item number specified in cell B1. Use the range name JuneItems for the Range argument and JuneOrdered for the Sum_range argument. 2. Now enter a formula in cell D2 using SUMIFS to calculate the number of items ordered (use the named range JuneOrdered) where the value in the JuneItems named range is equal to the value in cell B1 and the value in the JunePOs named range is equal

1. click the formulas tab. 2. on the formulas tab, in the function library group, click the math and trig button. 3. select the sumif function. 4. in the range argument box type JuneItems. Press enter. 5. in criteria box type b1 then enter. 6. in the sum range box type JuneOrdered. Press enter. 7. Click Ok. 8. Cell D2 has been selected. Click the Math and Trig button again. 9. In the Sum range argument box, type June Ordered. Press Enter to submit your answer. 10. in the criteria range1 argument box, type JuneItems. Press Enter to submit your answer. 11. In the Criteria 1 argument box, type b1. Press Enter to submit your answer. 12. in the criteria range2 argument box, type JunePOs. Press Enter to submit your answer. 13. In the Criteria2 argument box, type B2. Press enter to submit your answer. 14. Click ok

Finding the Most Common Value(s) with MODE.SNGL and Mode.mult 1. Enter a formula in cell h7 to display the first mode value in the range c2:c22. 2. Enter an array formula in cells h8:h9 to display the two mode values from the range c2:c22. Remember to select the results array before entering the formula.

1. in the formula bar, type=mode.sngl(c2:c22). Press Enter to submit your answer. 2. In the formula bar, type =Mode.mult (c2:c22). 3. Press Ctrl+Shift+Enter to complete an array formula.

Using Database Functions 1. Use DSUM to calculate the total of values in the Quantity in Stock column for items with a price greater than 15 in the data array named Inventory1. Use the criteria range in cells a4:f5. Enter the appropriate criteria in the criteria range and then enter the formula in cell f1. 2. Use DAVERAGE to calculate the average of the values in the quantity in stock column for items with a price less than 20 and a recorder time greater than 10 in the data array named Inventory2. Use the criteria range in cells A4:F5. Enter the appropriate criteria in the range and then enter the formula in cell f1. 3. Use DCOUNTA to count the cells in the Quantity in Stock column in the data array named Inventory3 where the item value begins with Brush or Hair. Use the criteria range in cells A4:F6. Enter the appropriate Criteria range and then enter the formula in cell G1.

type greater than 15 (>15) in c5 then click cell F1 1. Click the Formulas tab. 2. Click the Insert Function button to open Insert Function dialog. 3. Expand the Or select a Category drop-down list. 4. Select Database 5. Select DSUM. 6. click ok 7. In the Database argument box, type Inventory1 and press Enter to submit your answer. 8. In the Field argument box, type "Quantity in Stock" 9. In the Criteria argument box, type A4:F5 and press Enter to submit your answer. 10. Click Ok. 11. On the Formulas tab, in the Function Library group, click the Insert Function button. 12. The DAVERAGE function is selected in the Select a function list. Click Ok. 13. the animation shows the arguments being typed in the function arguments dialog. 14. click ok. 15. on the formulas tab, in the function library group, click the insert function button. 16. Select the DCOUNTA function 17. Click Ok. 18. the animation shows the arguments are typed in the function arguments dialog. (database says inventory 3, field says "Quantity in Stock" and Criteria says a4:f6 19. Click Ok.