Program Statement

Perform correlation and regression analysis on multiple datasets using Excel statistical functions.

---

Dataset 1: City Population vs Annual Income

(Population in B2:B9, Income in C2:C9)

=== a. Average Population ===
=AVERAGE(B2:B9)

=== Average Annual Income ===
=AVERAGE(C2:C9)

=== b. Standard Deviation of Population ===
=STDEV(B2:B9)

=== Standard Deviation of Annual Income ===
=STDEV(C2:C9)

=== c. Correlation Coefficient ===
=CORREL(B2:B9, C2:C9)

=== d. Slope (m) of Regression Line ===
=SLOPE(C2:C9, B2:B9)

=== Intercept (c) of Regression Line ===
=INTERCEPT(C2:C9, B2:B9)

=== Regression Equation ===
Y = m*X + c  →  Income = SLOPE * Population + INTERCEPT

Interpretation: If CORREL > 0.7, there is a strong positive relationship between population and income.

Scatter Plot: Select Population & Income → Insert → Scatter → Add Trendline (Linear)

---

Dataset 2: Student Study Hours vs Marks

(Study Hours in E2:E11, Marks in F2:F11)

=== a. Average Study Hours ===
=AVERAGE(E2:E11)

=== b. Average Marks ===
=AVERAGE(F2:F11)

=== c. Std Dev of Study Hours ===
=STDEV(E2:E11)

=== d. Std Dev of Marks ===
=STDEV(F2:F11)

=== e. Correlation Coefficient ===
=CORREL(E2:E11, F2:F11)

=== f. Slope (m) ===
=SLOPE(F2:F11, E2:E11)

=== Intercept (c) ===
=INTERCEPT(F2:F11, E2:E11)

=== Regression Equation ===
Marks = SLOPE * StudyHours + INTERCEPT

=== Predict marks for 4.2 hours ===
=SLOPE(F2:F11,E2:E11)*4.2 + INTERCEPT(F2:F11,E2:E11)

Interpretation of Correlation:

• r close to +1 → Strong positive correlation (more study → higher marks)
• r close to 0 → No relationship
• Limitation: Study hours alone cannot predict marks; factors like aptitude, health, and teaching quality also matter.

---

Dataset 3: Schools vs Candidates Distribution

(Schools in H2:H6, Candidates in I2:I6)

=== Average number of students in distribution ===
=AVERAGE(I2:I6)

=== Standard Deviation ===
=STDEV(I2:I6)

=== Correlation between Schools and Candidates ===
=CORREL(H2:H6, I2:I6)