A scatter diagram plots paired observations \((x_i, y_i)\) as points on the coordinate plane. It reveals the direction (positive / negative), form (linear / curved) and strength of the relationship between two variables.
In Excel: select the two columns → Insert → Scatter (X Y) → choose "Scatter with only markers".
Hours studied vs marks for 7 students shows points rising left-to-right → a positive relationship.
Price vs quantity demanded shows points falling left-to-right → a negative relationship.
Right-click any point on a scatter chart → Add Trendline → choose the type, and tick "Display Equation on chart" and "Display R-squared value on chart".
fitted by least squares; \(b\) is the slope, \(a\) the intercept. (Equivalent Excel functions:
SLOPE(y,x), INTERCEPT(y,x), or LINEST.)
choose the order \(k\) (2 = quadratic, 3 = cubic). Higher order fits more wiggles but risks over-fitting.
linear in the logs, so it is fitted by least squares on \(\log x, \log y\). Requires all \(x,y > 0\).
\(R^2 \in [0,1]\) is the proportion of variation in \(y\) explained by the fitted curve. \(R^2 = 1\) means a perfect fit; \(R^2\) near 0 means the curve explains little. Compare candidate curves and pick the one with the highest \(R^2\) that is not over-fitted.
Advertising spend \(x\) (₹'000): 2, 4, 6, 8, 10; Sales \(y\) (₹'000): 15, 23, 28, 36, 43. Excel's linear trendline displays \(y = 3.4x + 8.5\) with \(R^2 = 0.992\) — a very strong linear fit; each extra ₹1,000 of ads is associated with ₹3,400 more sales.
For growth data that curves upward, a linear trendline gives \(R^2 = 0.95\) but a 2nd-order polynomial gives \(R^2 = 0.998\). The polynomial fits better, so its displayed equation \(y = 0.5x^2 + 1.2x + 3\) is preferred for prediction within the data range.
=FORECAST.LINEAR(new_x, known_y, known_x) — predicts a single \(y\) for a given \(x\)
using the linear fit. (Older Excel: FORECAST.)=TREND(known_y, known_x, new_x) — returns predicted \(y\) values (can be an array) along
the least-squares line.Using the advertising data above, =FORECAST.LINEAR(12, y-range, x-range) predicts sales for
₹12,000 spend: \(3.4\times 12 + 8.5 = 49.3\) → ₹49,300.
=TREND(B2:B6, A2:A6, {11;12;13}) returns the fitted sales for the next three spend levels at
once, useful for filling a forecast column.
It is an Excel add-in: File → Options → Add-ins → Manage: Excel Add-ins → Go → tick Analysis ToolPak → OK. A Data Analysis button then appears on the Data tab.
It provides ready-made statistical procedures: Descriptive Statistics, Histogram, Correlation, Regression, t-tests, F-test, ANOVA (one- and two-way), and more — each via a dialog box, no formulas required.
Procedure: Data → Data Analysis → t-Test… → select the two input ranges, set the hypothesised mean difference (usually 0) and \(\alpha\) (0.05) → OK. The output reports \(t\)-stat, df, one- and two-tail \(p\)-values and critical \(t\).
Yields under two fertilisers: A = 20, 22, 19, 24, 25; B = 28, 26, 30, 27, 29. The ToolPak two-sample t-test (equal variances) returns \(t = -4.7\), two-tail \(p = 0.0015\). Since \(p < 0.05\), the mean yields differ significantly — B is higher.
Blood pressure before and after a drug for 6 patients: the paired t-test returns \(t = 3.2\), \(p = 0.024\) → significant reduction at the 5% level.
One-way ANOVA compares the means of three or more groups simultaneously, testing \(H_0: \mu_1 = \mu_2 = \cdots = \mu_k\) against the alternative that at least one mean differs.
Reject \(H_0\) if \(F > F_{\alpha}(k-1, N-k)\) or equivalently if \(p < \alpha\).
Procedure: arrange each group in its own column → Data → Data Analysis → Anova: Single Factor → select the range, set \(\alpha\) → OK. The output gives the ANOVA table (SS, df, MS, F, \(p\)-value, F-crit).
Marks under three teaching methods (each n = 5). The ToolPak returns \(F = 6.84\), \(p = 0.011\). Since \(p < 0.05\), the methods differ significantly in mean marks.
Three machines' output: \(F = 1.9\), \(p = 0.19 > 0.05\) → do not reject \(H_0\); no significant difference between the machines.
The \(p\)-value is the probability, assuming \(H_0\) is true, of obtaining a test statistic at least as extreme as the one observed. It measures the strength of evidence against \(H_0\).
\(p = 0.011\) at \(\alpha = 0.05\): reject \(H_0\). Interpretation: if the groups truly had equal means, a difference this large would occur only about 1.1% of the time — strong evidence of a real difference.
\(p = 0.19\): do not reject \(H_0\). A difference this large (or larger) would happen 19% of the time by chance even if the means were equal — too common to call significant.