What excel formula needs to be used for E6-E9??

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 Reorder? Reorder Qt Standard Markup: Premium Markup: Maximum Stock Level: Minimum Stock Level: 20% 25% 30 5 Reorder Amounts Lookup Category Premium A 25 B 20 15 D 10 E 5 Regular 20 15 10 5 2 Item Codes Over Stocked: Premium Regular Item Codes to Reorder Reorders A B D E Retail Price Over Stocked $1,330.81 $2,095.02 Y $4,201.13 Y $6,490.31 $14,117.58 $1,709.76 $3,359.88 Y $14,117.58 $655.38 $1,392.84 $5,713.50 $7,074.81 $7,055.58 $6,857.76 Y $7,006.13 $7,280.88Y $6,923.70|Y $6,923.70 $6,923.70 $3,668.44 $6,113.88 Y $12,330.94 Y $3,788.28 $6,817.31 $13,250.00 Y $5,658.56 $9,238.38 $17,050.62 $5,926.62 $9,533.64 Y $17,252.28 $4,706.28 $7,616.70 $13,839.63 $5,531.88 $8,526.56 $13,447.20 $5,603.63 $8,820.81 Y $1,464.56 $8,573.50 $8,915.94 $11,653.38 $11,634.38 Y 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 Reorder? Reorder Qt Standard Markup: Premium Markup: Maximum Stock Level: Minimum Stock Level: 20% 25% 30 5 Reorder Amounts Lookup Category Premium A 25 B 20 15 D 10 E 5 Regular 20 15 10 5 2 Item Codes Over Stocked: Premium Regular Item Codes to Reorder Reorders A B D E Retail Price Over Stocked $1,330.81 $2,095.02 Y $4,201.13 Y $6,490.31 $14,117.58 $1,709.76 $3,359.88 Y $14,117.58 $655.38 $1,392.84 $5,713.50 $7,074.81 $7,055.58 $6,857.76 Y $7,006.13 $7,280.88Y $6,923.70|Y $6,923.70 $6,923.70 $3,668.44 $6,113.88 Y $12,330.94 Y $3,788.28 $6,817.31 $13,250.00 Y $5,658.56 $9,238.38 $17,050.62 $5,926.62 $9,533.64 Y $17,252.28 $4,706.28 $7,616.70 $13,839.63 $5,531.88 $8,526.56 $13,447.20 $5,603.63 $8,820.81 Y $1,464.56 $8,573.50 $8,915.94 $11,653.38 $11,634.38 Y