Hi All

Hope some-one can help me. I need a FUNCTION that will take values between 4 and 6 and change them into 2. I am thinking the use of IF and OR, would do the trick.
One problem is the numbers have decimal values as well. e.g 4.05, 4.85, 4.1, 5.5 ...... so between 4 and 6 there maybe up to 200 different possibilities.

Thanking you in advance.



If I understand you correct, you need to use that formula:

= IF(AND(A1 >= 4, A1 <= 6), 2, A1)

or if you want to exclude 4 and 6, change it to that:

= IF(AND(A1 > 4, A1 < 6), 2, A1)

If I'm not - tell me more details...


Best regards.

Blank Cells

Hi Manny

Your formulae given above is working very well, I have actually employed a nested if. e.g

=IF(A1=0,1,IF(AND(A1>=0.01,A1<=1),2,IF ......

Which is working except when the cell is blank it returns a
1, which is wrong, I would much prefer if it returned a B, then I could identify blanks in the data. Hope this make sense
and you have a solution.


RE: Blank Cells


In Excel the condition A1 = 0 return TRUE for both a blank cells and these that actually contains zero. To workaround this first check if cell is a blank cell and then if contain zero, like that:

=IF(ISBLANK(A1), "B", IF(A1=0 , 1 , IF(AND(A1 >= 0.01, A1 <= 1), 2, IF...


Best regards.


Thanks, again. :)


Thanks Manny, that has fixed my problem. Much appreciated. :)