+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Registered User
    Join Date
    09-13-2012
    Location
    Malta
    MS-Off Ver
    Excel 2007
    Posts
    29

    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. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,371

    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. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,525

    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.
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    09-13-2012
    Location
    Malta
    MS-Off Ver
    Excel 2007
    Posts
    29

    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1