Why does SUMPRODUCT return a negative number for a positive-EV bet?

Check the signs of your outcomes. If you entered a loss as a positive number (e.g., $10 for a $10 loss instead of -$10), SUMPRODUCT treats it as a gain. Always enter net outcomes: profit minus cost. A negative EV means the bet loses money on average.

Can I use SUMPRODUCT with percentages in the probability column?

No. SUMPRODUCT multiplies the numbers as they are. If you enter 20% as 20, the product will be 20 times the outcome, not 0.2 times. Use decimal probabilities (0.20) or format the cells as percentages and let the display show 20% while the underlying value is 0.20.

What if my outcomes include text or blanks?

SUMPRODUCT treats non-numeric entries as zero. A blank cell or text in the outcome column will multiply that probability by zero, effectively dropping that row from the calculation. That is almost always an error. Keep both columns purely numeric.

How do I compute EV for a continuous distribution in Excel?

This method works only for discrete random variables. For continuous distributions (like a normal distribution), you need integration or a built-in distribution function (e.g., NORM.DIST). That is a different topic covered under variance of random variable treatments.

Why does my variance come out negative?

Variance cannot be negative. You likely computed E[X]² incorrectly, you may have squared each outcome before multiplying by probability instead of squaring the final EV. The formula is E[X²] - (E[X])². Check that your column C contains A², not (A - EV)².

Expected Value in Excel and Google Sheets

How to Calculate Expected Value in Excel

Expected value (EV) is the long-run average, not a prediction for one trial. A fair die has an EV of 3.5, but you will never roll a 3.5. The question is mechanical: how to compute EV from a probability table fast. Use the formulas for expected value excel, variance, and standard deviation, with SUMPRODUCT and SUM, and note the differences in Google Sheets.

Building the table correctly is the first step. Outcomes go in one column; their probabilities go in the adjacent column. Probabilities must be decimals (0.10, not 10%) and must sum to exactly 1. If they sum to 0.95 or 1.05, every calculation that follows is wrong. Check that sum before you compute anything else.

The Layout: Outcomes and Probabilities in Two Columns

Open a blank sheet. Label cell A1 Outcome and cell B1 Probability. In column A, list every possible outcome of the random event: dollar amounts for a bet, points for a game, or net profit for a project risk. In column B, enter the probability of each outcome as a decimal between 0 and 1.

For a simple example: a lottery ticket that costs $2 to play and pays $10 with a 0.2 probability and $0 otherwise. Your outcomes are $8 net profit (win $10 minus $2 cost) and -$2 net loss. Column A: 8, -2. Column B: 0.2, 0.8.

Do not mix positive and negative signs inconsistently. Every outcome must represent the net change to your bankroll, not the gross payout. A common failure: using the gross payout of $10 and forgetting to subtract the cost of playing. That mistake inflates the EV and makes a losing bet look profitable.

E(X) With SUMPRODUCT

The formula for expected value is Σ (outcome × probability). In Excel, one function does that in a single call: SUMPRODUCT. Place your cursor in cell D1 and enter:

=SUMPRODUCT(A2:A10, B2:B10)

Replace the ranges with your actual data. SUMPRODUCT multiplies each entry in the first array by the corresponding entry in the second array, then sums all the products. Microsoft support documents this function: SUMPRODUCT treats non-numeric entries as zero, so blank cells or text labels in the range will not crash the formula, they will silently return 0, which is almost never what you want. Keep your data clean: numbers only in the two columns.

For the lottery example: =SUMPRODUCT(A2:A3, B2:B3) returns (8 × 0.2) + (-2 × 0.8) = 1.6 - 1.6 = 0. The EV is exactly $0, a fair game. If the ticket cost $1 instead of $2, the outcomes would be 9 and -1, and the EV would be $1, a positive-EV bet that still does not guarantee a win on any single play.

The same syntax works in Google Sheets. Google Sheets SUMPRODUCT behaves identically: same function name, same treatment of non-numeric entries. Microsoft support and Google Sheets support both confirm this parity. The only difference is that Google Sheets sometimes requires explicit ranges and cannot always handle whole-column references (A:A) in older versions; use a named range or a specific cell range to be safe.

SUMPRODUCT Expected Value in Practice

For a more realistic case, a project risk register with four outcomes and probabilities that vary, the same pattern holds. Set up a table with outcomes in A2:A5, probabilities in B2:B5. The formula is =SUMPRODUCT(A2:A5, B2:B5). The result is the Expected Monetary Value (EMV) used in project risk analysis under the PMBOK Guide.

One warning: do not use SUMPRODUCT when the arrays have different lengths. Excel returns #VALUE! if the arrays are not the same size. Always verify that the number of rows in column A matches the number in column B.

If you prefer a more explicit method, you can type =A2*B2 + A3*B3 + ... manually, but that is error-prone with more than five outcomes. SUMPRODUCT is the standard tool for this job.

Variance and Standard Deviation Formulas in Excel

Expected value is only half the picture. Variance measures how spread out the outcomes are around the EV. Standard deviation (SD) puts that spread back into the same units as the outcomes: dollars, points, or whatever your data uses. The formula for variance is E[(X - μ)²], which expands to E[X²] - (E[X])².

In Excel, you need two SUMPRODUCT calls. First compute E[X²]: create a third column (C2:C10) where each cell is the square of the outcome: =A2^2. Then compute =SUMPRODUCT(C2:C10, B2:B10). That gives you E[X²]. Subtract the square of the EV you already computed: =SUMPRODUCT(C2:C10, B2:B10) - (SUMPRODUCT(A2:A10, B2:B10))^2. That is the variance.

The standard deviation is the square root of the variance: =SQRT(variance). For the lottery example, outcomes 8 and -2 with probabilities 0.2 and 0.8: E[X²] = (64 × 0.2) + (4 × 0.8) = 12.8 + 3.2 = 16. E[X] = 0, so variance = 16 - 0 = 16. SD = 4. That means the typical outcome is $4 away from the mean, a huge spread relative to the $0 EV. This is the kind of bet that can lose many times in a row even though the long-run average is break-even.

The same formulas work in Google Sheets without any syntax change. Use the same SUMPRODUCT calls. Google Sheets also supports SQRT identically.

Checking Probabilities Sum to 1

Before you trust any result, verify that your probabilities total exactly 1. A single missing decimal point or a mistyped 0.05 as 0.5 will throw off every calculation. In cell D3 (or any empty cell), enter:

=SUM(B2:B10)

Microsoft support documents SUM as a standard function that adds all numbers in the range. If the result is not 1, stop. Rounding differences to 0.9999 or 1.0001 are acceptable for most classroom work, but for real betting or project decisions, the probabilities must sum to exactly 1. If they do not, you have either omitted an outcome or assigned a probability incorrectly.

A common failure: using percentages as whole numbers (like 20 instead of 0.2) and getting a sum of 100 instead of 1. If your sum is 100, you have entered percentages as whole numbers. Divide every probability by 100 or re-enter them as decimals.

Google Sheets Differences for Expected Value Calculations

Google Sheets and Excel share the same SUMPRODUCT and SUM syntax, but there are three practical differences that matter for expected value work.

First, Google Sheets is stricter about array dimensions in some older versions. If you use whole-column references like SUMPRODUCT(A:A, B:B), Google Sheets may return an error because it cannot handle the entire column as an array in older implementations. Use explicit ranges: SUMPRODUCT(A2:A100, B2:B100). For most modern Sheets instances (post-2020), whole-column references work fine, but explicit ranges are safer.

Second, Google Sheets handles empty cells differently. In Excel, an empty cell in the range is treated as zero. In Google Sheets, an empty cell is also treated as zero for SUMPRODUCT, but the behavior can differ when the empty cell is in the middle of a range and you are using array formulas. Test with a small dataset first.

Third, if you need to compute variance using the alternative formula (subtracting the mean from each outcome before squaring), Google Sheets has a SUMPRODUCT that works with array arithmetic. You can write =SUMPRODUCT((A2:A10 - mean)^2, B2:B10) provided you have the mean stored in a cell. This is cleaner than creating a separate squared-difference column, but it requires array-aware evaluation. In both Excel and Google Sheets, this syntax works if you wrap it in an array formula or use modern Excel (365) where array formulas are native.

Downloadable Template and Copyable Formulas

Download the template (a link is provided by the system).

The template contains three sheets: one for a basic two-outcome lottery, one for a project risk register with up to ten outcomes, and one blank sheet for your own data. All formulas are pre-entered. You only need to type your outcomes in column A and probabilities in column B. The template computes EV, variance, and standard deviation automatically.

The formulas to copy directly into your own sheet are:

Expected value: =SUMPRODUCT(A2:A10, B2:B10)
Variance: =SUMPRODUCT(C2:C10, B2:B10) - (SUMPRODUCT(A2:A10, B2:B10))^2 (where column C contains the square of column A)
Standard deviation: =SQRT(variance_cell)
Probability sum check: =SUM(B2:B10)

The template also includes a variance in excel probability distribution column that computes (outcome - EV)² × probability for each row, then sums them as a cross-check. Use that to verify your manual variance calculation if the numbers seem off.

Who This Subject Suits and Who Should Skip It

This method suits anyone who needs to compute expected value from a known probability distribution: statistics students preparing for exams, bettors evaluating lottery or casino odds, and project managers calculating EMV for risk registers. The spreadsheet formulas above give you the EV, variance, and standard deviation of a table of outcomes and probabilities in under a minute.

They do not guarantee what will happen on a single trial. Expected value does not predict one play. Also skip if you need investment advice based solely on EV, expected value ignores risk tolerance, utility, and the Kelly Criterion formula for optimal bet sizing. Those require a separate treatment of portfolio theory and utility.

The single thing that most often goes wrong: confusing a positive EV with a guaranteed win. A positive-EV bet can lose money many times in a row if variance is high. The spreadsheet gives you the variance and standard deviation so you can see the risk. If you ignore those numbers and bet your entire bankroll on a high-variance positive-EV bet, you will go broke even though the math was on your side.