wind.size | |||||||||
3 | 0 | - | 8 | 4 | 3 | - | 2 | 1 | 27 |
4 | 5 | 3 | 2 | 10 |
<tbody>
</tbody>
1) I want to create a moving average (going to the right) with a defined window size (A2).
2) I don't want to return a number if the character above is a dash.
3) For the moving average I want to skip cells that contain a dash above (i.e. moving average of non-dash cells).
2) C3 doesn't return anything because the character above is a dash.
3) B3 returns 4 because (B2+D2+E2)/A2 = (0+8+4)/3 = 4.
I have found a solution for 1) and 2).
Code:
=IF(B2<>"-",AVERAGE(OFFSET(A2,0,1,1,$A2)),"")
IF(B2<>"-" solves 1), and AVERAGE(OFFSET(A2,0,1,1,$A2)) solves 2).
Does anyone have a solution for 3) ?
Last edited: