1) On the worksheet named Fixed, fill in the cells in the table from D9 - F10. Each product belongs to a division which is indicated by the 4th character in the product code. (For example, the first product is in division B as B is the 4th character in 123B456). a) In cells D9 and D10 calculate the number of products in each division. (For the data supplied, there are 2 in each.) b) In cells E9 and E 10 calculate the total weight in pounds of the products in each division. You can assume that there are exactly 3 digits in each size. After the size is a K or # indicating the size is expressed in kilograms or pounds. (This is almost exactly the same as the last problem we did in class). Use 2.204 as the kilograms to pounds conversion factor. c) In cells F9 and F10 calculate the average package size in pounds of the products in each division. 2) On the worksheet named Any calculate the total pounds of all the products. Note that you cannot assume that the number of digits in the package size is 3. Hint: think about using the LEN function to tell you the total number of digits in the package size and then use this to extract the numeric part of the package size. 3) On the worksheet named Password1 create a password for each person using the following scheme: a. The password is at most 8 characters long consisting of the first 6 characters of the person's last name followed by the first 2 characters of their first name. If the last name is less than 6 characters, use the entire last name followed by the first 2 characters of the first name. (For example, if the name is Bill Smith, the password will be SMITHBI.) b. The password should be all upper case letters. Use the function UPPER to convert any letter to its upper case equivalent. (For example =UPPER("Dave") will produce DAVE.) You could also have a cell location as the parameter to UPPER. The password for my name would be ROSENTDA. Note that when you use LEFT, RIGHT or MID and you ask for more characters than exist in the string, the function will return as many characters as there are. For example if cell B2 had jones, LEFT(B2,6) will return jones. Put the password in any column after the Last Name. 4) On the worksheet named Password2 create a password for each person with the same rules as problem 3 but if the last name has less than 6 characters, then use additional characters from the first name in order to make the password 8 total characters. For example, for first name edward and last name jones, the password would be JONESEDW. Put the password in any column after the Last Name. Remember all your formulas should work even if the data that was supplied changes. Total # of Division Products Total Pounds A B 2) Total pounds 9419.4 3) 4) 2 2 First Name Last Name pwd David Rosenthal edward jones ROSENTDA JONESED Average Package 5710.4 822.8 789.2 234.8 First Name Last Name PWD David Rosenthal ROSENTDA edward Jones JONESEDW Product Code Package Size Qty 123B456 145K 2 632A123 351K 4 777B111 150# 1 712A111 872# 3 Division A B Average Total # of Total Package Products Pounds Size Product Code Size Qty 123B456 1K 632A123 22K 24 777B111 333# 1 712A111 4444# 2 Total pounds First Name Last Name David Rosenthal edward jones First Name Last Name David Rosenthal edward jones