Search code examples
excelfiltersignificance

filter values between -1 and 1 excel

I have an excel column with some interesting data, but a lot of positive and negative values that are insignificant.

So I would like to turn all the values -1<0<1 into zero. What would be the best way to do this?

Any help would make my day.

Solution

  • You could use a simple IF condition for this task.

    Assuming you have your data in column A, use the below formula in column B and drag it down,

    =IF(AND(A1<=1,A1>=-1),0,A1)

    enter image description here

    You could later use this column as reference. If you want to ignore 1 and -1 , then remove the = sign within the AND function.

    =IF(AND(A1<1,A1>-1),0,A1)