{"id":32523,"date":"2025-06-22T15:08:14","date_gmt":"2025-06-22T15:08:14","guid":{"rendered":"https:\/\/gaviki.com\/blog\/?p=32523"},"modified":"2025-06-22T15:08:15","modified_gmt":"2025-06-22T15:08:15","slug":"college-students-weekly-earnings-in-dollars","status":"publish","type":"post","link":"https:\/\/gaviki.com\/blog\/college-students-weekly-earnings-in-dollars\/","title":{"rendered":"College Students Weekly Earnings in Dollars"},"content":{"rendered":"\n

College Students Weekly Earnings in Dollars (n = 5) (a) Make an Excel worksheet to calculate SSxx, SSyy, and SSxy (the same worksheet you used in exercises 12.2 and 12.3). (b) Use the formulas to calculate the slope and intercept. (c) Use your estimated slope and intercept to make a worksheet to calculate SSE, SSR, and SST. (d) Use these sums to calculate the R2. (e) To check your answers, make an Excel scatter plot of X and Y, select the data points, right-click, select Add Trendline, select the Options tab, and choose Display equation on chart and Display R-squared value on chart.<\/p>\n\n\n\n

The Correct Answer and Explanation is:<\/mark><\/strong><\/p>\n\n\n\n

To analyze the relationship between two variables (such as hours worked and weekly earnings for college students), we can follow the outlined steps in Microsoft Excel. Let\u2019s assume you have the following data for 5 students:<\/p>\n\n\n\n

X (Hours Worked)<\/th>Y (Weekly Earnings in $)<\/th><\/tr><\/thead>
10<\/td>150<\/td><\/tr>
15<\/td>200<\/td><\/tr>
20<\/td>260<\/td><\/tr>
25<\/td>310<\/td><\/tr>
30<\/td>370<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
\n\n\n\n

(a) Create Excel Worksheet for SSxx, SSyy, SSxy<\/h3>\n\n\n\n
    \n
  1. In Excel, create columns for X, Y, X\u00b2, Y\u00b2, and XY.<\/li>\n\n\n\n
  2. Use formulas like =A2^2<\/code> for X\u00b2, =B2^2<\/code> for Y\u00b2, and =A2*B2<\/code> for XY.<\/li>\n\n\n\n
  3. At the bottom, use the =SUM()<\/code> function to total each column.<\/li>\n<\/ol>\n\n\n\n

    Then compute:<\/p>\n\n\n\n

      \n
    • SSxx = \u03a3(X\u00b2) \u2212 (\u03a3X)\u00b2 \/ n<\/li>\n\n\n\n
    • SSyy = \u03a3(Y\u00b2) \u2212 (\u03a3Y)\u00b2 \/ n<\/li>\n\n\n\n
    • SSxy = \u03a3(XY) \u2212 (\u03a3X)(\u03a3Y) \/ n<\/li>\n<\/ul>\n\n\n\n
      \n\n\n\n

      (b) Calculate the Slope and Intercept<\/h3>\n\n\n\n
        \n
      • Slope (b)<\/strong> = SSxy \/ SSxx<\/li>\n\n\n\n
      • Intercept (a)<\/strong> = \u0232 \u2212 bX\u0304
        Where \u0232 and X\u0304 are the means of Y and X respectively.<\/li>\n<\/ul>\n\n\n\n
        \n\n\n\n

        (c) Use Slope and Intercept to Compute SSE, SSR, and SST<\/h3>\n\n\n\n

        Create a new column for predicted Y:<\/p>\n\n\n\n

          \n
        • \u0176 = a + bX<\/code><\/li>\n<\/ul>\n\n\n\n

          Then:<\/p>\n\n\n\n

            \n
          • SST = SSyy<\/li>\n\n\n\n
          • SSR = \u03a3(\u0176 \u2212 \u0232)\u00b2<\/li>\n\n\n\n
          • SSE = \u03a3(Y \u2212 \u0176)\u00b2<\/li>\n<\/ul>\n\n\n\n

            Use formulas in Excel for each squared deviation.<\/p>\n\n\n\n

            \n\n\n\n

            (d) Calculate R\u00b2<\/h3>\n\n\n\n
              \n
            • R\u00b2 = SSR \/ SST<\/strong><\/li>\n<\/ul>\n\n\n\n

              This value explains the proportion of variation in Y explained by X.<\/p>\n\n\n\n

              \n\n\n\n

              (e) Create Scatter Plot and Trendline<\/h3>\n\n\n\n
                \n
              1. Select X and Y data.<\/li>\n\n\n\n
              2. Insert a scatter plot.<\/li>\n\n\n\n
              3. Right-click on a data point and choose \u201cAdd Trendline.\u201d<\/li>\n\n\n\n
              4. In the trendline options, check \u201cDisplay Equation on Chart\u201d and \u201cDisplay R-squared value on chart.\u201d<\/li>\n<\/ol>\n\n\n\n

                This graphical display will confirm the slope, intercept, and R\u00b2 value calculated.<\/p>\n\n\n\n

                This entire process helps students build foundational knowledge in linear regression using Excel without requiring advanced statistical software.<\/p>\n\n\n\n

                \"\"<\/figure>\n","protected":false},"excerpt":{"rendered":"

                College Students Weekly Earnings in Dollars (n = 5) (a) Make an Excel worksheet to calculate SSxx, SSyy, and SSxy (the same worksheet you used in exercises 12.2 and 12.3). (b) Use the formulas to calculate the slope and intercept. (c) Use your estimated slope and intercept to make a worksheet to calculate SSE, SSR, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-32523","post","type-post","status-publish","format-standard","hentry","category-quiz-questions"],"_links":{"self":[{"href":"https:\/\/gaviki.com\/blog\/wp-json\/wp\/v2\/posts\/32523","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gaviki.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/gaviki.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/gaviki.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/gaviki.com\/blog\/wp-json\/wp\/v2\/comments?post=32523"}],"version-history":[{"count":2,"href":"https:\/\/gaviki.com\/blog\/wp-json\/wp\/v2\/posts\/32523\/revisions"}],"predecessor-version":[{"id":32526,"href":"https:\/\/gaviki.com\/blog\/wp-json\/wp\/v2\/posts\/32523\/revisions\/32526"}],"wp:attachment":[{"href":"https:\/\/gaviki.com\/blog\/wp-json\/wp\/v2\/media?parent=32523"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/gaviki.com\/blog\/wp-json\/wp\/v2\/categories?post=32523"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/gaviki.com\/blog\/wp-json\/wp\/v2\/tags?post=32523"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}