jwalkerday
New Member
- Joined
- May 1, 2018
- Messages
- 18
I calculate a three-month moving average in hundreds of sheets using the formula below.
April (4) is always an anomaly so I want to exclude it from any affected averages. For example- for may's average, I want to use May, March and February. I looked at trimmean but I don't think that works in this instance.
Any ideas on how I could do this please?
April (4) is always an anomaly so I want to exclude it from any affected averages. For example- for may's average, I want to use May, March and February. I looked at trimmean but I don't think that works in this instance.
Any ideas on how I could do this please?
| Book1 | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 2 | MOVING AV | 3 | ||||||||||||
| 3 | ||||||||||||||
| 4 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | ||
| 5 | 545 | 600 | 650 | 1000 | 625 | 605 | 617 | 629 | 641 | 653 | 665 | 677 | ||
| 6 | ||||||||||||||
| 7 | 545 | 600 | 598.3333 | 750 | 758.3333 | 743.333333 | 615.6667 | 617 | 629 | 641 | 653 | 665 | ||
Sheet1 | ||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| A7:L7 | A7 | =IF(COLUMNS($A5:A5)<$B$2,A5,AVERAGE(OFFSET(A5,0,0,1,-$B$2))) |
