Calculate the number of itemsto reorder if a reorder is required. If not the calculation should return 0 (not 0). Reorder quantities are based on two factors, the category shown in column C and whether or not the item is premium or standard, the lookup table in M10:O14 shows the appropriate number for each combination. For example: for a Category A premium item we need to reorder 25 whereas a regular item we only reorder 20.

This is a challenge question so limited help can be given, but you may choose a simpler option and just return the standard amount for all items that need reordering, and if correct you will still receive 3 marks.

What excel formula is used for these?

E6-E9 Need to be done.

C K L 0 1 SateINET Network Equipment Inventory 204 Standard Map Premium Mario: Maimum Stock Level: Minimum Stock Level: 3D Foorde imounts Loop Category Premium 25 20 25 o 10 5 20 15 10 F 2 ker Code Over Stocles Readers Premium Rezala 3 F 3 hem Code ben Description Category Mun In Sood On Backard 4 -cz.T1000 HF 24 10-100 +2 Con Ghr A 13 68c297375 BF 45 10.100 + 2 CUCLE A 32 Y 8 FIA-G1917 30 FF1009 - CFGh 31 Y 7 24270 48.pert 20.000 with up lu t-ul urbe Plakala C Y 8 E-CZ-65591 BF with 8 port FP momplete w 0 8 1.-23394 BUDE SUPMMC 7 Y 19 106 F 9637. 20GbE LAPSE e 35 11 10G-2271 10hE ER UTP aptek 22.1. Connector D 11 12 IV 19:14 5.6 MMF SFP A 17 13 FIV-X-R1730 X SAF SFP 21 14 5496 LASED 15 FIV-CW.27746 akm147mm, Ionato ... c 21 18 VW US B 143 C 17 ELV-CW-72258 BOKm 151 nr. consecto S. 33 18 LV-CW-32006205 m, 1930nr, Lenector 25 19 El CW36736 om 15 nr. IC con ccto 35 20 DIV-CW-12120m, 1570nr, senect C 33 21 E1V W 28743 om 1:9, Conco 7 22 FIV-CW-11095 om 11 or, in win c 11 23 FES CA 128 2492Cfw1AC Y 24 FF-07-23915 18, INC C 35 25 PLS.CA 2634 D 31 25 FES DC 62971 240, 2 OF & IDC 32 27 TES-EC 1400 1pZCA DC C 25 FES DC 59050 969, 4CIF & DC 0 34 20 FT-71-700G Fre24,2 CTX 11C 25 30 FESPA 91962 Prem185 2146 C 9 31 FTS-P1-1712 Prem 16 CRNE D A Y 32 10.12.27652 Pran 24, 2017 & 1 DC 35 FFS.PARE Prem 18,2 CFR INC 32 34 FES-PM-1972 Pranb5 4CA X UC D 24 35 FE W. 12909 FES por 10/100BXe TXIRI45) Nis 2 por: 1000ease Tard 160 100% @ 4 38 TES-WATU-par: 19/1 CI-TX 45 Blus 2-por: 1000e-Tord 100C 23 37 FES W. 57764 F33 por: 10/100BXe TX R45) Alus 4 por:1000ease Tard 100 0 14 38 FIS-WL-59701 within gated TRAP WAN, 125W and 2-part 10 1 21 39 FES W.25721 SD Whil TOP WANAL SW and 48 port 04 12 Y 40 FES-W-IGRES DE weh in makPWANA Want 96-part 10 21 Y 41 Ft 10.61098 SPD wintea THOMAS WAN, 2 and 24.04 13 42 FFS. 4400 FESPO within and TRONPI WA SW and 48-port 10/1 c 31 43 PESAN THONunt WLAN uudet for TS 5.ch A 5 41 FES CZ 4492] 126 C.FW1AC C 45 TLS-110.92029 22 A wiec C 11 45 FES PA 6:2:5 126 CIF Pray WAAC C 6 47 FTS-P2-7EGG 125 CP w1CC C 11 7 Overview Information A HR Data Completed Jobs Cor Premium? Retail Pike | Over Stocked Reorder? Reorder or SIDAG $1,100 RI $1.74585 $2.-05.02 53 3090 Y $1,70113 5.1.225 $5,490.21 $11.76465 514.11759 $14243D $2,JUD. 52.79990 $3,359 Y $11.75468 $14.111.10 5524.30 $655 14 51,100.70 $1,1931 $5 21:50 55.659 AS $7,070 1 ! 38) 5. 55.71480 $6,857 76 Y 559 $3,005.13 55824,70 $7,280.6 55.769.75 $5,023 35269.25 $5:22.70 55,700.75 $E$21.70 $1,165.44 5509 $6,112 Y 1984.75 $12,450.00 53.15690 $9,785 28 5549 4 5,112.31 $.0600.00 513,250.00 5:56 Y $5,EGEEG 37638.65 $9.235. $1420AAS 517.06.12 -499885 $5,26.12 57.912 70 $9.531 Y 8:48/690 -11,252.28 5392190 $4,70629 5681723 $3,316.30 $.1071.70 513,639.63 54435.50 $5,501. 3682125 $5526.56 $11.706.00 517,447 30 344:29 4 $5,503 570566S $8.520 BY 1.1/1.55 $2.464.50 568588 $5,573 50 57.112.75 $1,915. 5932220 511,65:28 59.397.50 511,00 Labour Costs Inventory JOB_4636 Journals Stats + E6 4 0 In column calculate the number of items to reorder if a reorder is required. If not the calculation should return 0 (not "O"). Reorder quantities are based on two factors, the category shown in column C and whether or not the item is premium or standard, the lookup table in M10:014 shows the appropriate number for each combination. For example: for a Category A premium item we need to reorder 25 whereas a regular item we only reorder 20. This is a challenge question so limited help can be given, but you may choose a simpler option and just return the standard amount for all items that need reordering, and if correct you will still receive 3 marks. Note reorder values in the lookup table can change, so don't type them, your calculation must be consistent and work regardless of values or sort order.) In 016 create a formula to count how many of the item codes have been overstocked. E7 2 Question Instruction Marks Done E8 1 E9 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.) 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.) 3 C K L 0 1 SateINET Network Equipment Inventory 204 Standard Map Premium Mario: Maimum Stock Level: Minimum Stock Level: 3D Foorde imounts Loop Category Premium 25 20 25 o 10 5 20 15 10 F 2 ker Code Over Stocles Readers Premium Rezala 3 F 3 hem Code ben Description Category Mun In Sood On Backard 4 -cz.T1000 HF 24 10-100 +2 Con Ghr A 13 68c297375 BF 45 10.100 + 2 CUCLE A 32 Y 8 FIA-G1917 30 FF1009 - CFGh 31 Y 7 24270 48.pert 20.000 with up lu t-ul urbe Plakala C Y 8 E-CZ-65591 BF with 8 port FP momplete w 0 8 1.-23394 BUDE SUPMMC 7 Y 19 106 F 9637. 20GbE LAPSE e 35 11 10G-2271 10hE ER UTP aptek 22.1. Connector D 11 12 IV 19:14 5.6 MMF SFP A 17 13 FIV-X-R1730 X SAF SFP 21 14 5496 LASED 15 FIV-CW.27746 akm147mm, Ionato ... c 21 18 VW US B 143 C 17 ELV-CW-72258 BOKm 151 nr. consecto S. 33 18 LV-CW-32006205 m, 1930nr, Lenector 25 19 El CW36736 om 15 nr. IC con ccto 35 20 DIV-CW-12120m, 1570nr, senect C 33 21 E1V W 28743 om 1:9, Conco 7 22 FIV-CW-11095 om 11 or, in win c 11 23 FES CA 128 2492Cfw1AC Y 24 FF-07-23915 18, INC C 35 25 PLS.CA 2634 D 31 25 FES DC 62971 240, 2 OF & IDC 32 27 TES-EC 1400 1pZCA DC C 25 FES DC 59050 969, 4CIF & DC 0 34 20 FT-71-700G Fre24,2 CTX 11C 25 30 FESPA 91962 Prem185 2146 C 9 31 FTS-P1-1712 Prem 16 CRNE D A Y 32 10.12.27652 Pran 24, 2017 & 1 DC 35 FFS.PARE Prem 18,2 CFR INC 32 34 FES-PM-1972 Pranb5 4CA X UC D 24 35 FE W. 12909 FES por 10/100BXe TXIRI45) Nis 2 por: 1000ease Tard 160 100% @ 4 38 TES-WATU-par: 19/1 CI-TX 45 Blus 2-por: 1000e-Tord 100C 23 37 FES W. 57764 F33 por: 10/100BXe TX R45) Alus 4 por:1000ease Tard 100 0 14 38 FIS-WL-59701 within gated TRAP WAN, 125W and 2-part 10 1 21 39 FES W.25721 SD Whil TOP WANAL SW and 48 port 04 12 Y 40 FES-W-IGRES DE weh in makPWANA Want 96-part 10 21 Y 41 Ft 10.61098 SPD wintea THOMAS WAN, 2 and 24.04 13 42 FFS. 4400 FESPO within and TRONPI WA SW and 48-port 10/1 c 31 43 PESAN THONunt WLAN uudet for TS 5.ch A 5 41 FES CZ 4492] 126 C.FW1AC C 45 TLS-110.92029 22 A wiec C 11 45 FES PA 6:2:5 126 CIF Pray WAAC C 6 47 FTS-P2-7EGG 125 CP w1CC C 11 7 Overview Information A HR Data Completed Jobs Cor Premium? Retail Pike | Over Stocked Reorder? Reorder or SIDAG $1,100 RI $1.74585 $2.-05.02 53 3090 Y $1,70113 5.1.225 $5,490.21 $11.76465 514.11759 $14243D $2,JUD. 52.79990 $3,359 Y $11.75468 $14.111.10 5524.30 $655 14 51,100.70 $1,1931 $5 21:50 55.659 AS $7,070 1 ! 38) 5. 55.71480 $6,857 76 Y 559 $3,005.13 55824,70 $7,280.6 55.769.75 $5,023 35269.25 $5:22.70 55,700.75 $E$21.70 $1,165.44 5509 $6,112 Y 1984.75 $12,450.00 53.15690 $9,785 28 5549 4 5,112.31 $.0600.00 513,250.00 5:56 Y $5,EGEEG 37638.65 $9.235. $1420AAS 517.06.12 -499885 $5,26.12 57.912 70 $9.531 Y 8:48/690 -11,252.28 5392190 $4,70629 5681723 $3,316.30 $.1071.70 513,639.63 54435.50 $5,501. 3682125 $5526.56 $11.706.00 517,447 30 344:29 4 $5,503 570566S $8.520 BY 1.1/1.55 $2.464.50 568588 $5,573 50 57.112.75 $1,915. 5932220 511,65:28 59.397.50 511,00 Labour Costs Inventory JOB_4636 Journals Stats + E6 4 0 In column calculate the number of items to reorder if a reorder is required. If not the calculation should return 0 (not "O"). Reorder quantities are based on two factors, the category shown in column C and whether or not the item is premium or standard, the lookup table in M10:014 shows the appropriate number for each combination. For example: for a Category A premium item we need to reorder 25 whereas a regular item we only reorder 20. This is a challenge question so limited help can be given, but you may choose a simpler option and just return the standard amount for all items that need reordering, and if correct you will still receive 3 marks. Note reorder values in the lookup table can change, so don't type them, your calculation must be consistent and work regardless of values or sort order.) In 016 create a formula to count how many of the item codes have been overstocked. E7 2 Question Instruction Marks Done E8 1 E9 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.) 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.) 3