macro or a formula which could find the approximate figure of oposite sign

[harry2908]

Hi,
Can someone please advise, I need a macro or a formula which could find the approximate figure of oposite sign with a diffence of 4000.

For example:-

I have a amount 50,000 in cell A1, in the same coulmn if there is any figure in oposite sign with a differnce of +/-4000, so it should find/color 47000 or 53000

 

[Roli001]

One way of doing this would be to put the following formula into cells B2 and copying down. Any value that fits the criteria of 46000 - 54000 would have a value of 1.

=IF(AND(A2>=$A$1-4000,A2<=$A$1+4000),1,0)

Hope this helps