[enter image description here] Cell values in Excel like column A

If the number in cell A above is equal or higher only for max 2, then put 1 in cell B

If the number in cell A above is lower, do nothing

If the number in cell A above is higher that 2, do nothing

Anyone that can help me? Thanks :)

CodePudding user response：

You need 2 IF statements, the first to check if the cell above is higher or lower than the current cell and the second one to perform the check for the difference between them. I believe this formula will work for you

=IF(A2>=A1,IF(A2-A1<=2,1,""),"")

CodePudding user response：

What you describe is not "formatting". It needs a formula that returns the number into the cell.

There are different ways to calculate that. You can do a comparison with "greater than" and "less than", but a much simpler way is subtracting the number from the cell above from the current row. If the remainder is 2 or less, then return a 1, if it is greater than 2 return a blank.

Start in cell B2 with

=IF(A2-A1<=2,1,"")

Copy down.

If the numbers are not consecutively increasing in column A, you also need to include a condition to check that the current row value is bigger or equal to the previous row's value.

=IF(AND(A2>=A1,A2-A1<=2),1,"")