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...
Bookmarks