# Combined AND function not reading output of the function of another cell

1. ## Combined AND function not reading output of the function of another cell

Hope I'm in the right forum for this as it's my first question on here.

So, maybe I'm just stupid but I haven't been able to find the answer to this on any "how to use AND and IF" articles and google searches and even a search on this forum are not pulling up anything slightly relevant (I probably don't know the correct terms for this issue) BUT, my combined IF and AND function in a cell does not appear to be able to read the output of a function in another cell.

In the first cell I have a formula that I'm using to check for a character within another cell and then print the next few characters after that into this particular cell. That works fine (though it's probably more messy than it needs to be). Then I want to be able to take that result and check if it lies within a certain range (user defined - but I'll simplify my example in a moment).

So the first cell would be something like:
=IF(ISNUMBER(SEARCH("(",A3)), MID(A3, SEARCH("(", A3)+1, 5), )

This returns a result of "0.298"

The second cell would be something like
=IF(AND(B34>0.2, B34<0.3),B:B, #N/A)

OR:
=AND(A:A>=\$E\$33,A:A <\$F\$33) (where E33 and F33 are .2 and .3 respectively)

Now, if I manually type in 0.298 in the cell where the first function is both of these work perfectly: returning the number in the first example and "TRUE" in the second.

If I use the first function they no longer work and return results of #N/A and FALSE even though I can see the number is correct. I've even tested this with an even simpler "=A1" sort of formula and the "AND" function fails to work then as well.

I guess my first question is: Do "AND" conditionals not work when referencing cells with formulas in them?
Secondly, if this is true: How can I get around it? If it's not, what am I doing incorrectly?

NB: Cell A3 has the text BD123(0.298) in it.

Any help would be appreciated. Still a bit of a newbie at these more intricate Excel ways...

2. ## Re: Combined AND function not reading output of the function of another cell

I could be wrong, but I think your problem is that the MID() function will return text, and you are then comparing that text string to the numbers 0.2 and 0.3. In debugging this, I'd first suggest you make sure your comparison is exactly what you want (including comparing text string to string or comparing number to number).

3. ## Re: Combined AND function not reading output of the function of another cell

Mr. Shorty is correct. Probably the easiest way to solve this is in your original equation

=IF(ISNUMBER(SEARCH("(",A3)), MID(A3, SEARCH("(", A3)+1, 5), )

change that to
=IF(ISNUMBER(SEARCH("(",A3)), MID(A3, SEARCH("(", A3)+1, 5)+0, )
The "+0" forces excel to convert the text back into a number.
Hope that helps.

4. ## Re: Combined AND function not reading output of the function of another cell

You two are gentlemen and scholars!

Thanks very much. I hadn't realised the MID function was returning text!

There are currently 1 users browsing this thread. (0 members and 1 guests)