Compare regional order values with a one-way ANOVA in Excel. Follow the workbook layout, reproduce the sums of squares, and interpret the result with its assumptions intact.
ANOVA analysis in Excel tests whether the means of several groups differ beyond the variation within those groups. Put independent order values into regional columns, run a one-way test, and reconcile its output to the source rows. A small p-value supports a difference somewhere among the means; it does not explain why it exists.
Take an order export with order ID, customer ID, region, order date, currency, and net order value. Select one comparable period and one currency. Collapse order lines to one order before selecting observations, and retain the IDs on a source sheet.
Decide what the observation represents. If the same customer places many related orders, treating those orders as independent can overstate the evidence. You may need customer-level summaries or a different statistical design. Regional differences can also reflect product mix, contract terms, or customer size.
The ordinary one-way test assumes independent errors, approximately normal errors within groups, and a common error variance. NIST states these distributional assumptions. Inspect group distributions and unusual records before running it; a worksheet of numbers cannot establish independence.
For setup of the add-in itself, use the Analysis ToolPak guide. This walkthrough focuses on the group comparison and the arithmetic that proves its output.
A US distributor compares independent orders from East, Central, and West. Enter these values in A1:C5. Each cell below a heading is a separate order; values sharing a worksheet row are not paired observations.
| East order value (USD) | Central order value (USD) | West order value (USD) |
|---|---|---|
| 90 | 110 | 130 |
| 100 | 120 | 140 |
| 110 | 130 | 150 |
| 100 | 120 | 140 |
For East, enter these checks in A7:A10, then copy across to C:
=COUNT(A2:A5)
=SUM(A2:A5)
=AVERAGE(A2:A5)
=VAR.S(A2:A5)
The four formulas return the following summary. Variances are in squared dollars and rounded to two decimals.
| Region | Orders | Total (USD) | Mean (USD) | Sample variance |
|---|---|---|---|---|
| East | 4 | 400 | 100 | 66.67 |
| Central | 4 | 480 | 120 | 66.67 |
| West | 4 | 560 | 140 | 66.67 |
| All | 12 | 1,440 | 120 |
Check the combined order count and dollar total against the retained export. These checks should pass before any test result is interpreted. Equal sample variances in this small illustration do not prove that real-world population variances are equal.
In desktop Excel, open Data > Data Analysis > Anova: Single Factor, the group-comparison tool described in Microsoft's ToolPak documentation. Select A1:C5, group by columns, select Labels in First Row, and use a separate output location. Set alpha to 0.05 before examining the result.
The test compares variation between the regional means with variation inside each region:
F = Between-group mean square / Within-group mean square
NIST's ANOVA table guidance defines the mean squares and their degrees of freedom. With three groups and twelve orders, between-group degrees of freedom are 3 - 1 = 2; within-group degrees of freedom are 12 - 3 = 9.
The grand mean is =AVERAGE(A2:C5), or $120. Between-group sum of squares is:
=4*(AVERAGE(A2:A5)-AVERAGE(A2:C5))^2+4*(AVERAGE(B2:B5)-AVERAGE(A2:C5))^2+4*(AVERAGE(C2:C5)-AVERAGE(A2:C5))^2
This gives 4 × (100 - 120)^2 + 4 × (120 - 120)^2 + 4 × (140 - 120)^2 = 3,200.
Within-group sum of squares is:
=DEVSQ(A2:A5)+DEVSQ(B2:B5)+DEVSQ(C2:C5)
It gives 200 + 200 + 200 = 600. The resulting table is:
| Source | Sum of squares | Degrees of freedom | Mean square | F |
|---|---|---|---|---|
| Between groups | 3,200 | 2 | 1,600.00 | 24.00 |
| Within groups | 600 | 9 | 66.67 | |
| Total | 3,800 | 11 |
Calculate F using unrounded values: (3200/2)/(600/9) = 24. The p-value formula is:
=F.DIST.RT(24,2,9)
It returns approximately 0.000247, rounded to six decimal places. Microsoft documents the right-tailed F distribution, including its numerator and denominator degrees of freedom. Covirage is not affiliated with Microsoft.
The total sum of squares must equal the sum of the two components:
=DEVSQ(A2:C5)
That returns 3,800 = 3,200 + 600. Degrees of freedom also reconcile: 11 = 2 + 9. Keep full precision in cells; round only displayed outputs.
Because 0.000247 is below the chosen alpha of 0.05, reject the equal-means hypothesis under the assumptions. The observed regional means are $100, $120, and $140. Ask whether product mix or customer composition explains the gap before changing regional targets. The result does not establish that geography caused the differences.
Bring the order workbook or Google Sheet with its IDs, regions, and net values to inspect the underlying group summaries. Covirage maps the columns; its tools compute and check the figures, and the external AI model explains them with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.
See Excel analysis with Covirage for more on working from your workbook.
It provides evidence that the population means are not all equal, under the test assumptions. It does not identify which pairs differ or establish a cause. Use a suitable multiple-comparison procedure before making statistical claims about individual pairs.
Only if the observation structure suits the test. Consecutive monthly totals can share seasonality and other dependencies. Repeated observations on the same regions may require a repeated-measures or other appropriate method rather than an ordinary independent-groups ANOVA.
Review the distribution, group sizes, and observation design before interpreting the ordinary test. A Welch ANOVA may be appropriate for unequal variances, but it requires a separate procedure. Do not relabel the standard ToolPak output as a Welch result.