9

On the CorrelationMatrix worksheet create a correlation matrix of three different variables. You have been given a random sample of customer data with age and gender along with the amount of money spent on a given visit to the spa. Since gender is listed as either male or female in the data set provided, you must first convert it to numerical nominal data using an IF function. In cell B2 use an IF function to show a1forMales and a2for Females. Copy the function down to cell B37. Use the Data Analysis add-in to create a correlation table using the data in B1:D37. Group the data by Columns and use the Labels in First Row option. Use cell F1 for the Output Range. Adjust the columns to be able to view the contents.

R C D F G H M Gender Gender (Nominal) Age Sales 2 Male 35 $63.76 3 Female 45 $181.52 Male 38 $145.08 Male 49 $153.39 6 Female 47 $233.09 Female 41 $192.25 8 Male 50 $132.59 9 Male 46 $130.28 10 Female 48 $293.97 11 Female 36 $166.98 12 Female 47 $165.18 13 Female 48 $252.05 14 Male 46 $161.94 15 Male 36 $156.14 16 Female 37 $279.18 17 Female 43 $261.67 3 Female 47 $261.25 19 Female 49 $262.73 20 Female 38 $204.02 21 Male 34 $52.44 22 Male 32 $61.21 23 Male 31 $80.12 24 Male 24 $71.00 25 Male 32 $85.74 26 Male 28 $56.27 27 Male 25 $62.89 28 Male 19 $42.63 29 Male 23 $58.35 30 Male 32 $65.74 31 Male 56 $296.88 32 Male 61 $121.88 33 Female 60 $177.52 34 Female 64 $184.46 35 Female 56 $190.96 36 Female 61 $222.53 37 Female 57 $232.54 38 CallCampaign OnlineOrder SpaTreatments RaffleAnalysis Sales Trend CorrelationMatrix