Original
Values | Substitution
Values | Basic mathematical function | Explanation |
| 1 | | =IF(A2<>0,"", [part I can't figure out] ) | If there is a non-zero value in the A column, don't do anything. |
| 0 | 1.5 | 1 + 1*( (2-1) / (1+1) )
= 1.5 | There is a non-zero value of 1 one row above the current zero-value cell, and a non-zero value of 2 one row below the current zero-value cell. The cell value should be calculated as follows:
1 (previous non-zero value) +
1 (number of rows up) * 2 (next value) - 1 (previous value) = 1 (numerator)
1 (rows up) + 1 (rows down) = 2 (denominator)
Put another way, the average change per row between 1 and 2 (1; the numerator) is 1/(1+1) or 0.5 (there are two changes in row between the non-zero values; the number in the denominator will be the number of zero-value cells, plus 1). This is multiplied by the number of rows we've moved down from the previous non-zero value, 1, and added to the previous non-zero value.
1 (previous non-zero value) + 1 (# of rows since a non-zero value) *1/2 (average change) = 1.5
The value I want to see in this cell is 1.5 |
| 2 | | =IF(A4<>0,"", [part I can't figure out] ) | If there is a non-zero value in the A column, don't do anything. |
| 0 | 2.333 | 2 + 1*( (3-2) / (2+1) )
= 2.333 | There is a non-zero value of 2 one row above the current zero-value cell, and a non-zero value of 3 two rows below the current zero-value cell. The cell value should be calculated as follows:
2 (previous non-zero value) +
1 (number of rows up) * 3 (next value) - 2 (previous value) = 1 (numerator)
1 (rows up) + 2 (rows down) = 3 (denominator)
Put another way, the average change per row between 2 and 3 (1; the numerator) is 1/(2+1) or 0.333 (there are three changes in row between the non-zero values; the number in the denominator will be the number of zero-value cells, plus 1). This is multiplied by the number of rows we've moved down from the previous non-zero value, 1, and added to the previous non-zero value.
2 (previous non-zero value) + 1 (# of rows since a non-zero value) *1/3 (average change) = 2.333
The value I want to see in this cell is 2.333 |
| 0 | 2.667 | 2 + 2 * 1/3
= 2.667 | There is a non-zero value of 2 two rows above the current zero-value cell, and a non-zero value of 3 one row below the current zero-value cell. The cell value should be calculated as follows:
2 (previous non-zero value) +
2 (number of rows up) * 3 (next value) - 2 (previous value) = 1 (numerator)
2 (rows up) + 1 (rows down) = 3 (denominator)
Put another way, the average change per row between 2 and 3 (1; the numerator) is 1/(2+1) or 0.333 (there are three changes in row between the non-zero values; the number in the denominator will be the number of zero-value cells, plus 1). This is multiplied by the number of rows we've moved down from the previous non-zero value, 2, and added to the previous non-zero value.
2 (previous non-zero value) + 2 (# of rows since a non-zero value) *1/3 (average change) = 2.667
The value I want to see in this cell is 2.667 |
| 3 | | =IF(A7<>0,"", [part I can't figure out] ) | If there is a non-zero value in the A column, don't do anything. |
| 0 | 4 | 3 + 1*( (5-3) / (1+1) )
= 4 | There is a non-zero value of 3 one row above the current zero-value cell, and a non-zero value of 5 one row below the current zero-value cell. The cell value should be calculated as follows:
3 (previous non-zero value) +
1 (number of rows up) * 5 (next value) - 3 (previous value) = 2 (numerator)
1 (rows up) + 1 (rows down) = 2 (denominator)Put another way, the average change per row between 3 and 5 (2; the numerator) is 2/(1+1) or 1 (there are two changes in row between the non-zero values; the number in the denominator will be the number of zero-value cells, plus 1). This is multiplied by the number of rows we've moved down from the previous non-zero value, 1, and added to the previous non-zero value.
3 (previous non-zero value) + 1 (# of rows since a non-zero value) *1 (average change) = 4
The value I want to see in this cell is 4 |
| 5 | | =IF(A9<>0,"", [part I can't figure out] ) | If there is a non-zero value in the A column, don't do anything. |
| 0 | 3.5 | 5 + 1*( (2-5) / (1+1) )
= 3.5 | There is a non-zero value of 5 one row above the current zero-value cell, and a non-zero value of 2 one row below the current zero-value cell. The cell value should be calculated as follows:
5 (previous non-zero value) +
1 (number of rows up) *5 (next value) - 2 (previous value) =-3 (numerator)
1 (rows up) + 1 (rows down) = 2 (denominator)Put another way, the average change per row between 5 and 2 (-3; the numerator) is -3/(1+1) or -1.5 (there are two changes in row between the non-zero values; the number in the denominator will be the number of zero-value cells, plus 1). This is multiplied by the number of rows we've moved down from the previous non-zero value, 1, and added to the previous non-zero value.
5 (previous non-zero value) + 1 (# of rows since a non-zero value) *-1.5 (average change) = 3.5
The value I want to see in this cell is 3.5 |
| 2 | | =IF(A11<>0,"", [part I can't figure out] ) | If there is a non-zero value in the A column, don't do anything. |
| 0 | 2.2 | 2 + 1*( (3-2) / (4+1) )
= 2.2 | There is a non-zero value of 2 one row above the current zero-value cell, and a non-zero value of 3 four rows below the current zero-value cell. The cell value should be calculated as follows:
2 (previous non-zero value) +
1 (number of rows up) * 3 (next value) - 2 (previous value) = 1 (numerator)
1 (rows up) + 4 (rows down) = 5 (denominator)Put another way, the average change per row between 2 and 3 (1; the numerator) is 1/(4+1) or 0.2 (there are five changes in row between the non-zero values; the number in the denominator will be the number of zero-value cells, plus 1). This is multiplied by the number of rows we've moved down from the previous non-zero value, 1, and added to the previous non-zero value.
2 (previous non-zero value) + 1 (# of rows since a non-zero value) * 0.2 (average change) = 2.2
The value I want to see in this cell is 2.2 |
| 0 | 2.4 | 2 + 2 * 1/5
= 2.4 | There is a non-zero value of 2 two rows above the current zero-value cell, and a non-zero value of 3 three rows below the current zero-value cell. The cell value should be calculated as follows:
2 (previous non-zero value) +
2 (number of rows up) * 3 (next value) - 2 (previous value) = 1 (numerator) 2 (rows up) + 3 (rows down) = 5 (denominator)
Put another way, the average change per row between 2 and 3 (1; the numerator) is 1/(2+3) or 0.2 (there are five changes in row between the non-zero values; the number in the denominator will be the number of zero-value cells, plus 1). This is multiplied by the number of rows we've moved down from the previous non-zero value, 2, and added to the previous non-zero value.
2 (previous non-zero value) + 2 (# of rows since a non-zero value) * 0.2 (average change) = 2.4
The value I want to see in this cell is 2.4 |
| 0 | 2.6 | 2 + 3 * 1/5
= 2.6 | There is a non-zero value of 2 three rows above the current zero-value cell, and a non-zero value of 3 two rows below the current zero-value cell. The cell value should be calculated as follows:
2 (previous non-zero value) +
3 (number of rows up) * 3 (next value) - 2 (previous value) = 1 (numerator)
3 (rows up) + 2 (rows down) = 5 (denominator)
Put another way, the average change per row between 2 and 3 (1; the numerator) is 1/(3+2) or 0.2 (there are five changes in row between the non-zero values; the number in the denominator will be the number of zero-value cells, plus 1). This is multiplied by the number of rows we've moved down from the previous non-zero value, 3, and added to the previous non-zero value.
2 (previous non-zero value) + 3 (# of rows since a non-zero value) * 0.2 (average change) = 2.6
The value I want to see in this cell is 2.6 |
| 0 | 2.8 | 2 + 4 * 1/5
= 2.8 | There is a non-zero value of 2 four rows above the current zero-value cell, and a non-zero value of 3 one row below the current zero-value cell. The cell value should be calculated as follows:
2 (previous non-zero value) +
4 (number of rows up) * 3 (next value) - 2 (previous value) = 1 (numerator)
4 (rows up) + 1 (rows down) = 5 (denominator)
Put another way, the average change per row between 2 and 3 (1; the numerator) is 1/(3+2) or 0.2 (there are five changes in row between the non-zero values; the number in the denominator will be the number of zero-value cells, plus 1). This is multiplied by the number of rows we've moved down from the previous non-zero value, 3, and added to the previous non-zero value.
2 (previous non-zero value) + 4 (# of rows since a non-zero value) * 0.2 (average change) = 2.8
The value I want to see in this cell is 2.8 |
| 3 | | =IF(A16<>0,"", [part I can't figure out] ) | If there is a non-zero value in the A column, don't do anything. |
