Excel Applications for Discrete and Bivariate Probability Distributions
Assessment Logistics and Resources
- Session Announcement: A session is scheduled for Thursday. Students were encouraged to review the announcement.
- Open Book Policy: Assessments are open book. Students are permitted to use their notes, Excel, and an internet connection. No separate formula sheet is required as all necessary material should be in the student's notes.
- Formula Sheets: While the assessment is open book, relevant formulas will still be provided in the assessment documents so students do not need to memorize them.
- Assessment Content (A2): The assessment will require students to perform calculations using specific probability formulas.
- Sometimes hints are provided (e.g., "use the binomial distribution").
- If no hint is provided, students must identify the distribution based on given information such as sample size (n), probability of success (π), or the mean/expected value (λ).
- Practical Sessions: Practical sessions involve revision using Excel exercises to practice formula application.
Fundamental Discrete Probability Calculations in Excel
- Expected Value (E(X)): Calculated as the sum of each outcome multiplied by its probability:
- E(X)=sum(x×P(x))
- In Excel: Multiply the column of X values by the column of P(X) values, then use
SUM() on the resulting column.
- Variance (Var(X) or σ2): There are two primary ways to express this formula:
- Var(X)=sum(X2×P(x))−mu2
- Where mu is the expected value (E(X)).
- Calculation Steps:
- Create a column for X2.
- Multiply X2 by P(X).
- Sum these values to get E(X2).
- Subtract the squared mean (E(X)2) from this sum.
- Standard Deviation (σ): Calculated as the square root of the variance:
- SD=sqrt(variance)
- Coefficient of Variation: Calculated as the standard deviation divided by the mean:
- CV=μσ
- Visualizing Distributions:
- Probability distributions can be graphed as spike charts or bar charts.
- Shape characteristics (e.g., skewed to the right) are determined by the parameters of the distribution.
Binomial Distribution in Excel
- Parameters: Requires the sample size (n) and the probability of success (π).
- Success Characteristics:
- Expected Value: E(X)=n×π
- Variance: Var(X)=n×π×(1−π)
- Excel Function:
BINOM.DIST(number_s, trials, probability_s, cumulative)number_s: The number of successes (the x value).trials: The sample size (n).probability_s: The probability of success (π).cumulative: FALSE: Calculates the probability of exactly x successes (P(X=x)).TRUE: Calculates the cumulative probability (P(X≤x)).
- Reference Management: When dragging formulas in Excel, use absolute references (dollar signs, e.g., `B$1`) for the parameters nand\pisotheyremainfixedwhilethex value changes.\n* **Convergence:** As the sample size (n)increasesor\pi nears 0.5, the binomial distribution starts to appear more symmetric and converges toward a normal distribution.\n\n# Poisson Distribution in Excel\n\n* **Application:** Used for the number of events occurring within a specific area of opportunity, typically a unit of time (per hour, per day) or space (square meters).\n* **Parameter:** Requires \lambda (lambda), which is the mean or expected value per unit.\n* **Properties:** A unique property of the Poisson distribution is that the mean and the variance are equal:\n * E(X) = ext{Var}(X) = \lambda\n* **Excel Function:** `POISSON.DIST(x, mean, cumulative)`\n * Use `FALSE` for individual probabilities and `TRUE` for cumulative probabilities (P(X \leq x)).\n* **Scientific Notation in Results:** Small probabilities may appear in scientific notation (e.g., 6 imes e^{-5}or0.00006).Ifavalueise with a negative exponent, it is a very small number close to zero.\n* **Connection to Exponential Distribution:** The Poisson distribution (number of events in time) is the inverse of the Exponential distribution (time between events).\n\n# Hypergeometric Distribution in Excel\n\n* **Application:** Used when sampling without replacement from a finite population.\n* **Parameters:**\n * Population Size (N)\n * Sample Size (n)\n * Number of successes in the population (A or Population Successes)\n* **Excel Function:** `HYPGEOM.DIST(sample_s, number_sample, population_s, number_pop, cumulative)`\n * `sample_s`: Successes in the sample (independent variable x).\n * `number_sample`: The size of the sample (n).\n * `population_s`: Successes in the population (A).\n * `number_pop`: Total population size (N).\n* **Note on Calculation:** While the mathematical formula for hypergeometric variance is complex, it is often easier in Excel to use the fundamental summation method (\sum X^2 P(X) - \mu^2).\n\n# Introduction to Bivariate Distributions\n\n* **Concept:** Looking at the relationship between two random variables (XandY) simultaneously.\n* **Joint Probabilities (P(x, y)):∗∗TheprobabilitythatXtakesaspecificvalueANDY takes a specific value. In a bivariate table, these are the individual cells.\n* **Double Summation:** The sum of all joint probabilities in the table must equal 1:\n * \sum_{x} \sum_{y} P(x, y) = 1\n* **Marginal Probabilities:** These are the row and column totals of a bivariate table.\n * Summing across a row (all yvaluesforaspecificx)givesthemarginalprobabilityofX.\n * Summing down a column (all xvaluesforaspecificy)givesthemarginalprobabilityofY.\n* **Key Statistical Measures in Bivariate Data:**\n * **Expected Values:** Separate averages for X(\mu_x)andY(\mu_y).\n * **Variances:** Separate measures of spread for X(\sigma^2_x)andY(\sigma^2_y).\n * **Covariance (\text{Cov}(X, Y)or\sigma_{xy}):** Measures how the two variables change together. \n * **Correlation (Rho, \rho):** A standardized measure of the linear relationship, calculated as:\n * \rho = \frac{\sigma_{xy}}{\sigma_x \times \sigma_y}\n* **Linear Combinations:** Bivariate distributions allow for the calculation of the expected value and variance of a sum of variables, such as E(X + Y)and ext{Var}(X + Y)$$. This is particularly useful in finance for portfolio management (comparing individual share returns and volatility against a combined portfolio).
Questions & Discussion
- Question: Will there be a formula sheet provided?
- Response: It is an open book assessment; students can use their notes, Excel, and the internet. Formulas will likely be included in the questions or as hints (e.g., using binomial distribution).
- Discussion on Sample vs. Population Variance in Excel: If working with a dataset (e.g., for the A1 assessment), use
VAR.S for a sample and VAR.P for a population.