Your request is poorly worded and thus we can't figure out how you calculated each 'Substitution Value.' Please enlighten us by providing the calculations for each.
Sorry, I thought I had been pretty clear. I am trying to find a function to put in column B that figures an average (or not) based on column A.
In all of the explanations below, the two parts I can't figure out are:
1) How to tell excel to count how many rows you have to go up and how many rows down (in column A) to find a non-zero value, and
2) How to tell excel to look for and return the value of the first non-zero values (in column A) both above and below the current zero-value row.
Technically, in the data I am working with there won't be any negative numbers, but I don't think that effects the formula.
I will say it every way I can think of; see if this helps:
[TABLE="class: grid, width: 900"]
<tbody>[TR]
[TD]Original
Values[/TD]
[TD]Substitution
Values[/TD]
[TD]Basic mathematical function[/TD]
[TD]Explanation[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]=IF(A2<>0,"", [part I can't figure out] )[/TD]
[TD]If there is a non-zero value in the A column, don't do anything.[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]1.5[/TD]
[TD]1 + 1*( (2-1) / (1+1) )
= 1.5[/TD]
[TD]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[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]=IF(A4<>0,"", [part I can't figure out] )[/TD]
[TD]If there is a non-zero value in the A column, don't do anything.[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]2.333[/TD]
[TD]2 + 1*( (3-2) / (2+1) )
= 2.333[/TD]
[TD]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[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]2.667[/TD]
[TD]2 + 2 * 1/3
= 2.667[/TD]
[TD]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[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]=IF(A7<>0,"", [part I can't figure out] )[/TD]
[TD]If there is a non-zero value in the A column, don't do anything.[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]4[/TD]
[TD]3 + 1*( (5-3) / (1+1) )
= 4[/TD]
[TD]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[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]=IF(A9<>0,"", [part I can't figure out] )[/TD]
[TD]If there is a non-zero value in the A column, don't do anything.[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]3.5[/TD]
[TD]5 + 1*( (2-5) / (1+1) )
= 3.5[/TD]
[TD]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[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]=IF(A11<>0,"", [part I can't figure out] )[/TD]
[TD]If there is a non-zero value in the A column, don't do anything.[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]2.2[/TD]
[TD]2 + 1*( (3-2) / (4+1) )
= 2.2[/TD]
[TD]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[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]2.4[/TD]
[TD]2 + 2 * 1/5
= 2.4[/TD]
[TD]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[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]2.6[/TD]
[TD]2 + 3 * 1/5
= 2.6[/TD]
[TD]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[/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]2.8[/TD]
[TD]2 + 4 * 1/5
= 2.8[/TD]
[TD]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[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]=IF(A16<>0,"", [part I can't figure out] )[/TD]
[TD]If there is a non-zero value in the A column, don't do anything.[/TD]
[/TR]
</tbody>[/TABLE]
