The Patagonia Exploration Club International (PECO) is a group of diverse individuals across many countries. Jennifer Cardozo, the club`s President, has taken up the challenge to systematize the membership. In a very short time, she has pulled together a lot of information!
An extract of 50 such records is available on the tab, CLUBDATA. This is where PECI needs your Excel expertise...
Starting with the tab, CLUBDATA, Jennifer would like you to help her solve the following:
INSTRUCTIONS:
Part 1: Populate the FULLNAME consisting of the following fields ONLY, in the prescribed format: PREFIX FIRSTNAME LASTNAME (lowercase) (uppercase) LASTNAME (lowercase) COUNTRYCODE.INDEX Convert only Columns 2, 10, and 11 to numerical values. (No formulas must be visible.) This is a good time to verify we have met the following objectives: A. All members who speak English have an email address. B. All members who don`t speak English have a username. C. No members have both an email address and a username.
Part 2: Convert height from inches to centimeters and weight from pounds to kilograms. Round the calculated number to one significant digit after the decimal. Using data computed in Column 27, use VLOOKUP to populate PECI-CODE from the tab FITNESS TABLE. Use AUTOFILL. Right align the results. This is a good time to verify we have met the following objectives: A. All members have their height listed in inches in the format ##.#. B. All members have their weight listed in pounds in the format ###.#. C. All members have their PECI-SCORE as a whole number in the format ###.
Part 3: Create a PIVOT table in the tab, PIVOT, starting at cell B3, with the following details: In COLUMNS (Group: YEARS only. Remove QUARTERS and BIRTHDATE.) In ROWS (Group: SEX, COUNTRY.) In VALUES (Calculate AVERAGE OF HEIGHT IN INCHES, formatted as ##.#). Remove GRAND TOTAL. Freeze both panes from cell D5. Adjust COLUMN WIDTH from D to the end of the PIVOT table to the value 8.
Part 4: Insert the following details into the HEADER using the PAGE SETUP: In LEFT section: Enter your FIRSTNAME LASTNAME in Calibri 12 BOLD. In CENTER section: Insert link for FILENAME and SHEETNAME in Calibri 12 BOLD. In RIGHT section: Insert link for TIME and DATE in Calibri 12 BOLD. Insert the following details into the FOOTER using the PAGE SETUP: In CENTER section: Insert path to FILENAME in Calibri 10 BOLD.