AND formula not giving correct result

[Chris Nel]

Hi All,

I am using a simple formula using the AND function and it will not give me correct results.

For example:

In cell N20, I have the text 1-30 as the result of this formula:-

=IFERROR(INDEX(Connectivity!$L$2:$L$945,MATCH(N19,Connectivity!$M$2:$M$945,0),1),"")

This result is correct.

The result could be 2-02, 3-45, 5-115, etc. depending on the value of N19. I am interested in the number after the hyphen.

I need to establish which range the number falls within so my first step is to strip the preceding number and hyphen from the resultr using this formula, in cell N21:-

=RIGHT(N20,LEN(N20)-2)

This formula correctly gives me the value of 30.

The next step is to establish which range of numbers this value falls into. I used this formula, in cell N22, to establish if it fell between the range 1 - 32:-

=AND(N21>0,N21<33)

The result is FALSE which is obviously incorrect.

Using =(N21>0) gives me TRUE, correctly.

I believe the reason is due to the formatting of the value in cell in that it recognises the value in cell is non-zero but is not recognising its numerical value. To check this line of thinking, I used the following formulas seperately:-

=(N20=30) and =(N20<33) with the result of FALSE. These are patently incorrect results and confirmed my suspicions.

I have tried formatting cell N21 as General and Number to no avail. This is really annoying because I am sure there is a simple answer.

Any bright ideas?

[#2]

PS I have manually changed the value in cell N21 to 30 and the AND formula works. So, how does one analyse the numerical result of a formula with the AND function?

[#3 · Accepted solution]

Hi Chris,

Try to replace this formula:

=AND(N21>0,N21<33)

By this:

=AND(VALUE(N21)>0,VALUE(N21)<33)

[#4]

Thank you Haytham! 'Value' - Of course. Now for the next part, using this formula in conditional formatting!

[#5]

The problem that you have is the numbers in your worksheets are considered as texts; because you got them through the text function RIGHT!

So, you have to parse them and convert them to actual numbers to get the right results.

You can surround those numbers by VALUE function, you can also use NUMBERVALUE function if you have Excel 2013 and later.