E7

In O16 create a formula to count how many of the item codes have been overstocked.

E8

A named range called Category has been created, but it does not include all the data in the Category column. Edit the named range so that it refers to the cells C4:C1245. (Do not add a new named range, change the existing one.)

E9

In N20 create a formula to count how many premium item codes of category A need to be reordered. Use appropriate cell referencing so that the formula can be dragged down and across to complete the table. (Note: for full marks you will need to use correct mixed cell references.)

D E F G H I J K M N o A B 1 SafeTNET Network Equipment Inventory Num In Stoch On Backorde Reorder? - Reorder Qty - Category Premium? Y 9 Standard Markup: Premium Markup: Maximum Stock Level: Minimum Stock Level: 20% 25% 30 33 Y B 3 Y 5 3 Y D 25 Y 29 Y Y B 14 Y Regular 20 15 D 1 Y Y A 19 Reorder Amounts Lookup Category Premium 25 B 20 15 D 10 E 5 Y 2 3 Item Code 4 EIF-CZ-73600 5 EIF-CZ-97375 6 EIF-A-64872 7 EIF-CZ-48270 8 EIF-CZ-66531 9 10G-XF-75398 10 10G-XF-96371 11 10G-XF-92273 12 E1M-SX-85914 13 E1M-LX-85720 14 E1M-LH-64896 15 E1M-CW-32246 16 E1M-CW-78899 17 E1M-CW-72258 18 E1M-CW-28966 19 E1M-CW-36738 20 E1M-CW-40232 21 E1M-CW-28783 22 E1M-CW-31995 23 FES-CZ-51280 24 FES-CZ-23915 10 A 7 Item Description EIF 24 10/100 +2 Combo GbE EIF 48 10/100 + 2 Combo GbE 20 FE/1000 +4 C/F GbE 48-port 10/100/1000 with up to 4-port combo SFP slots EIF with 8-port XFP slots and complete L2 SW. 10GbE SR XFP, MMF, LC 10GbE LR XFP, SMF, LC 10GbE ER XFP optics, SMF, LC connector SX MMF SFP LX SMF SFP LHA SMF SFP 80Km, 1470nm, LC connector 80Km, 1490nm, LC connector 80Km, 1510nm, LC connector 80Km, 1530nm, LC connector 80Km, 1550nm, LC connector 80Km, 1570nm, LC connector 80Km, 1590nm, LC connector 80Km, 1610nm, LC connector 24p 2 C/Fw 1 AC 48p, 2 C/F & 1 AC 5 31 Y Cost Pri $1,044.75 $1,762.80 $3,426.80 $5,241.70 $11,544.75 $1,438.50 $2,799.90 $11,654.70 $524.30 $1,160.70 $4,614.75 $5,659.85 $5,714.80 $5,769.75 $5,714.80 $5,659.85 $5,824.70 $5,604.90 $5,659.85 $2,934.75 $5,144.85 doorn 101 Y Retail Price Over Stocked $208.95 $352.56 Y $856.70 $1,310.43 $2,886.19 $287.70 $559.98 $2,330.94 $104.86 $290.18 $922.95 Y $1,414.96 $1,428.70 $1,153.95 $1,142.96 $1,131.97 $1,164.94 $1,120.98 $1,131.97 $586.95 $1,286.21 de 0000 B 2 20 Y 10 Item Codes Over Stocked: 19 Y Y 2 Y Y C 18 Y Item Codes to Reorder Reorders Premium A B Regular 17 Y Y C 8 Y 24 Y B Y Y 2 20 C Y rra Franc B D E F G H . A 1 SafeTNET Network Equipment Inventory Num In Stocl On Backorde - Reorder? Category A A Premium? Y Reorder Qt 0 9 33 Y 0 B Y 20 3 3 3 C Y 15 Item Description EIF 24 10/100 + 2 Combo GbE EIF 48 10/100 + 2 Combo GbE 20 FE/1000 + 4 C/F GbE 48-port 10/100/1000 with up to 4-port combo SFP slots EIF with 8-port XFP slots and complete L2 SW. 10GbE SR XFP, MMF, LC 10GbE LR XFP, SMF, LC 10GbE ER XFP optics, SMF, LC connector SX MMF SFP 25 Y 0 D A 29 Y Y 0 B 14 Y 0 D 1 Y 10 Y Y 19 0 A LX SMF SFP 7 0 0 B 31 Y Y C 20 Y lo lo 10 19 Y Y 0 2 Y Y 15 2 3 Item Code 4 EIF-CZ-73600 5 EIF-CZ-97375 6 EIF-A-64872 7 EIF-CZ-48270 8 EIF-CZ-66531 9 10G-XF-75398 10 10G-XF-96371 11 10G-XF-92273 12 E1M-SX-85914 13 E1M-LX-85720 14 E1M-LH-64896 15 EIM-CW-32246 16 E1M-CW-78899 17 E1M-CW-72258 18 E1M-CW-28966 19 E1M-CW-36738 20 E1M-CW-40232 21 E1M-CW-28783 22 E1M-CW-31995 23 FES-CZ-51280 24 FES-CZ-23915 25 FES-CZ-65136 26 FES-DC-62571 27 FES-DC-25460 28 FES-DC-59060 29 FES-PR-77806 30 FES-PR-91987 31 FES-PR-88772 32 FES-PR-27658 33 FES-PR-84308 34 FES-PR-78728 35 FES-WL-42909 36 FES-WL-97334 C 18 Y Y 0 Cost Pri $1,044.75 $1,762.80 $3,426.80 $5,241.70 $11,544.75 $1,438.50 $2,799.90 $11,654.70 $524.30 $1,160.70 $4,614.75 $5,659.85 $5,714.80 $5,769.75 $5,714.80 $5,659.85 $5,824.70 $5,604.90 $5,659.85 $2,934.75 $5,144.85 $9,958.70 $3,249.75 $5,612.70 $10,400.00 $4,482.90 $7,482.80 $14,622.70 $5,130.65 $7,644.90 $14,658.80 $4,037.25 $6,286.80 Retail Price Over Stocked $208.95 $352.56 Y $856.70 $1,310.43 $2,886.19 $287.70 $559.98 $2,330.94 $104.86 $290.18 $922.95 Y $1,414.96 $1,428.70 $1,153.95 $1,142.96 $1,131.97 $1,164.94 $1,120.98 $1,131.97 $586.95 $1,286.21 $1,991.74 $812.44 $1,122.54 $2,080.00 $896.58 $1,496.56 Y $2,924.54 $1,026.13 $1,528.98 Y $2,931.76 $1,009.31 $1,571.70 C 17 Y Y 0 C Y 8 24 0 lo C Y B 2 Y Y 20 C 20 Y 0 D 26 Y LHA SMF SFP 80Km, 1470nm, LC connector 80Km, 1490nm, LC connector 80km, 1510nm, LC connector 80Km, 1530nm, LC connector 80Km, 1550nm, LC connector 80Km, 1570nm, LC connector 80Km, 1590nm, LC connector 80Km, 1610nm, LC connector 24p 2 c/F w 1 AC 48p, 2 C/F & 1 AC 96p, 4 C/F & AC 24p, 2 C/F & 1 DC 48p, 2 C/F & 1 DC 96p, 4 C/F & DC Prem 24p, 2 C/F & 1 AC Prem 48p, 2 C/F & 1 AC Prem 96p, 4 C/F & AC Prem 24p, 2 C/F & 1 DC Prem 48p, 2 C/F & 1 DC Prem 96p, 4 C/F & DC FES-port 10/100Base-TX (RJ45) plus 2-port 1000Base-T and 1000 FES-port 10/100Base-TX (RJ45) plus 2-port 1000Base-T and 1000 LAAR Y 0 B 2 Y 20 C 23 Y 0 D 10 Y 0 0 B 21 Y 0 C 34 Y lo D Y Y Y 10 3 15 B Y 0 34 Y Y 0 D 20 Y 0 B 6 0 C 3 Y 15 ---- ... === TULLE IL M N Reorder Qty 20% 25% 0 Standard Markup: Premium Markup: Maximum Stock Level: Minimum Stock Level: 10 30 20 5 15 0 0 0 10 Reorder Amounts Lookup Category Premium A 25 B 20 D 10 E 5 Regular 20 15 10 5 2 0 15 0 0 0 0 Item Codes Over Stocked: 0 15 0 Regular 10 Item Codes to Reorder Reorders Premium A B 0 0 20 D 0 E 0 20