Average Difference and Direction Between Values in Excel with Blanks
- by 114
I have a sheet that looks something like this:
Sheet 1
1 2 3 4 5 6 7 8 9 10 11
1 6
2 3 5
3
4 2 4 9 4
5
6 4 6 6
7 5 3 3 3 10 8 4 8
8
9 4 11 12 12 6
10
11 8 5 5 4 9 4 7 6
What I would like to be able to do is find the average difference and direction between values in each column. For example, the first 4 rows would look like:
Average Difference # + Movements # -Movements
1
2 2 1 0
3
4 (2+5+5)/3 2 1
Blanks represent N/A values due to insufficient information, and differences are calculated successively i.e. col2-col1, col3-col2, col4-col3
If I just take the differences and make a duplicate table with the formula =C2-B2 copied across issues arise whenever there is a blank space between two values or at the beginning of the row. Is there an easy way to fix this or another way to do this that I might be missing?
