The formula
3-period SMA (start in row 4, fill down) (fill down)ExcelGoogle Sheets
=AVERAGE(B2:B4)In C4 → 101.00
3-period EMA (seed with the SMA, fill down) (fill down)ExcelGoogle Sheets
=B5*(2/(3+1))+D4*(1-2/(3+1))In D5 → 103.00
Average of the last 3 valuesExcelGoogle Sheets
=AVERAGE(OFFSET(B2,COUNT(B2:B100)-3,0,3))In F2 → 107.33
✓ Tested. All 12 Excel results in this example were recalculated in LibreOffice Calc 25.2 and matched the values shown.
Worked example
Paste the data below into a blank sheet starting at cell A1 (or download the cheat sheet, where every example is set up). Highlighted cells hold the formulas and show the expected results.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Day | Close | ||||
| 2 | 1 | 100 | 107.33 | |||
| 3 | 2 | 102 | ||||
| 4 | 3 | 101 | 101.00 | 101.00 | ||
| 5 | 4 | 105 | 102.67 | 103.00 | ||
| 6 | 5 | 107 | 104.33 | 105.00 | ||
| 7 | 6 | 104 | 105.33 | 104.50 | ||
| 8 | 7 | 108 | 106.33 | 106.25 | ||
| 9 | 8 | 110 | 107.33 | 108.12 |
How it works
- AVERAGE(B2:B4)
- Relative range: moves down one row as you fill.
- 2/(3+1)
- EMA smoothing factor for N = 3. For a 20-period EMA use 2/21.
- D4
- EMA seed: the first SMA value.
- OFFSET(B2,COUNT(…)-3,0,3)
- A 3-row range ending at the last number in the column.
Common errors and fixes
EMA differs from your charting platform?
Platforms seed the EMA differently; values converge after a few dozen periods.
OFFSET returns #REF!?
Fewer than N values in the column.
Does this work in Google Sheets?
Yes. The formulas on this page work the same way in Google Sheets.