Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | Stock | Average last 20 days | ||
2 | MSFT | 409.58 | ||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B2 | B2 | =AVERAGE(TAKE(STOCKHISTORY(A2,TODAY()-40,TODAY(),0,,1),-20)) |
Thanks AhoyNC, that should work!See if this works for you.
I used the starting date in the STOCKHISTORY as TODAY - 40 days. This gets the last 20 days plus sum extra days of closing prices.
I then used the TAKE function to get the most current 20 days and then took the AVERAGE.
Book1
A B 1 Stock Average last 20 days 2 MSFT 409.58 Sheet2
Cell Formulas Range Formula B2 B2 =AVERAGE(TAKE(STOCKHISTORY(A2,TODAY()-40,TODAY(),0,,1),-20))