look up the closest numeric value above, and undertake "x"

[eejwo]

I am trying to undertake some crude data filtering. I have a single column of data, which requires filtering based on three rules:

1) if the current value is numeric and equal to the previous numeric value, replace with #N/A, otherwise keep current numeric value.
2) if the current value is numeric and greater or less than 0.5 difference from the previous numeric value, replace with #N/A, otherwise keep current numeric value.
3) if the previous value is #N/A or non-numeric, look up the closest previous numeric value above, and undertake 1) and 2).

Obviously 1) and 2) are very easy to achieve, but I'm struggling with 3). Any ideas for a solution (formula or VBA)? Thanks in advance.

Example raw data and the desired filtered result for illustrative purposes:

Book1
	A	B
1	raw	filtered
2	0.5
3	1	1
4	1.5	1.5
5	2	2
6	2.5	2.5
7	2.5	#N/A
8	3.1	#N/A
9	3.2	#N/A
10	3.3	#N/A
11	2.6	2.6
Sheet1

 

[mikerickson]

Perhaps this formula in B8 and copied to the other cells in the column would work for you.
=IF(ABS(LOOKUP(99e+99,B$1:B7)-A8)<.5,"N/A",A8)

 

[eejwo]

=IF(ABS(LOOKUP(99e+99,B$1:B7)-A8)<.5,"N/A",A8)

Hi Mik,

Have just tried your suggested formula in cell B8 and copied to others and this doesn't solve the problem. This produces all values as #N/A.

Should note also - whilst the snippet I provided above is just ten rows of data, I need it to work for n number of rows.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

As well as the above can you provide the expected results for this sample, particularly in relation to rule 3.
As I understand it cell B3 would be #N/A (rule 1) so when you get to B4, how do you "look up the closest previous numeric value above," when there are no numbers (directly) above?

Is it correct that B2 would always be blank or should/could it contain the A2 value?

21 06 19.xlsm
	A	B
1	raw	filtered
2	1.2
3	1.2
4	1.5
5	2
6	2.5
7	2.5
8	3.1
9	3.2
10	3.3
11	2.6
Sample

 

[eejwo]

Thanks for making me aware, I've updated my account details.

The example sample you have provided would indeed break the filtering, i.e. as you suggested, because cell B3 would be #N/A, B4 will have no numeric value to lookup. So yes, if we can add the assumption that B2 = A2, rather than blank, that should work.

I've completed your sample below with this extra assumption, I hope this adds clarity.

Book2
	A	B
1	raw	filtered
2	1.2	1.2
3	1.2	#N/A
4	1.5	1.5
5	2	2
6	2.5	2.5
7	2.5	#N/A
8	3.1	#N/A
9	3.2	#N/A
10	3.3	#N/A
11	2.6	2.6
Sheet1

 

[Peter_SSs]

Thanks for the clarification. See if this is it.

21 06 19.xlsm
	A	B	C	D	E
1	raw	filtered		raw	filtered
2	0.5	0.5		1.2	1.2
3	1	1		1.2	#N/A
4	1.5	1.5		1.5	1.5
5	2	2		2	2
6	2.5	2.5		2.5	2.5
7	2.5	#N/A		2.5	#N/A
8	3.1	#N/A		3.1	#N/A
9	3.2	#N/A		3.2	#N/A
10	3.3	#N/A		3.3	#N/A
11	2.6	2.6		2.6	2.6
Lookup

Cell Formulas
Range	Formula
B2,E2	B2	=A2
B3:B11,E3:E11	B3	=IF(OR(A3=LOOKUP(9^9,B$2:B2),ABS(LOOKUP(9^9,B$2:B2)-A3)>0.5),NA(),A3)

 

[eejwo]

Great, that does the trick, thanks!

Being pernickety, is it possible to build the clause for cell B2 into the formula that is applied thereafter, so that the one formula can be copied down for the whole column? (I'm working with an excel add-in which extracts data from a database. When setting up templates, for calculated data columns, I just apply the formula to the first two rows in the template. When querying the database it then copies the formula down in the calculated column alongside the n number of rows of data from the database).

 

[eejwo]

Great, that does the trick, thanks!

Being pernickety, is it possible to build the clause for cell B2 into the formula that is applied thereafter, so that the one formula can be copied down for the whole column? (I'm working with an excel add-in which extracts data from a database. When setting up templates, for calculated data columns, I just apply the formula to the first two rows in the template. When querying the database it then copies the formula down in the calculated column alongside the n number of rows of data from the database).

Ignore me, incorporated with "ROW" no problem.

 

[Peter_SSs]

You're welcome. Glad you got the last bit sorted out.