Average change in a range

[Phil Hageman]

Need a formula to calculate the average daily change as a new day is posted.

For example:
Day 1: E2 = 230, beginning value
Day 2: E3 = 224, change is 6, average change is 6
Day 3: E4 = 216, change is 8, average change is 7
Day 4: E5 = 216, change is 0, average change is 4.7
Day 5: E6 = 215, change is 1, average change is 3.75
Day 6: E7 = 212, change is 3, average change is 3.6
Day 7: E8:E230 yet to be posted - blank, average change remains 3.6 until a new value is posted, then a new average is calculated.

 

[arthurbr]

Perhaps in F3 =IF($E3<>"",($E3-$E$2)/(COUNT($E$2:$E3)-1),"") and pull down as needed