Lookup, Reference & Dynamic Arrays
Add an Average Line to a Chart: Constant Helper Column, Variance & +1 SD Control Band
Plot a horizontal mean benchmark line across any column or line chart by anchoring =AVERAGE($B$2:$B$7) in a helper series, plus compute point-vs-mean variance and +1 SD upper control limits.
Why a locked =AVERAGE($B$2:$B$7) helper column is the cleanest way to plot a horizontal mean line!
Unlike scatter trendlines that tilt with slope, a horizontal Average Line (or SLA Target Line) in a combo Column + Line chart requires a second series with the exact same constant mean repeated alongside every category row! Writing =AVERAGE($B$2:$B$7) with absolute row locks ($2:$7) ensures row 2 through row 7 all return the identical series mean (e.g., 125.0 units) instead of shrinking the window as you copy down!
Better yet, adding an Upper +1 SD Control Band =AVERAGE($B$2:$B$7) + STDEV($B$2:$B$7) immediately separates normal month-to-month noise from statistically standout months! And in Arte Sheets, once your Actuals and Average_Line columns sit side by side in A1:C7, you can plot a line or column chart directly—or ask the AI Assistant (on an eligible plan) to create a chart on explicit range A1:C7!
| Combo Chart Helper Column (B2:B7 = [118, 132, 121, 145, 109, 125]) | Anchored Worksheet Formula | Result |
|---|---|---|
| Horizontal Mean Benchmark Series | =ROUND(AVERAGE($B$2:$B$7), 2) | 125.00 across every row |
| Month 4 Variance vs. Series Mean | =ROUND(B5 - AVERAGE($B$2:$B$7), 2) | +20.00 (145 - 125) |
| Upper +1 SD Benchmark Line | =ROUND(AVERAGE($B$2:$B$7) + STDEV($B$2:$B$7), 2) | 137.41 (Month 4 = 145 exceeds +1 SD!) |
Anchored Chart Average-Line Series =ROUND(AVERAGE($B$2:$B$7), 2)
Locks $B$2:$B$7 so every plotted row receives the exact overall period mean.
=IF(COUNT($B$2:$B$7) = 0, "No Data", ROUND(AVERAGE($B$2:$B$7), 2))Count of Periods Exceeding the +1 SD Upper Benchmark Band
Counts how many observations in B2:B7 exceed Mean + 1 Sample Standard Deviation.
=IF(COUNT($B$2:$B$7) < 2, "Need >= 2 Points", COUNTIF($B$2:$B$7, ">" & (AVERAGE($B$2:$B$7) + STDEV($B$2:$B$7))))Try the example
Use the original values or change them to test your understanding.