+ Reply to Thread
Results 1 to 6 of 6

Trouble using ISNUMBER and SEARCH within a range

  1. #1
    Registered User
    Join Date
    04-18-2013
    Location
    Collierville, Tennessee
    MS-Off Ver
    Excel 2010
    Posts
    27

    Trouble using ISNUMBER and SEARCH within a range

    I have cells (A1:A5) that have various number values in them. Some of these values may change to zero at a later date.

    Question1: In A6, I would like to have a formula that searches A1:A5 to see if one of the values has changed to zero. If so, then I want A6 to show the value found in B1. If not, then A6 can be zero (or blank - it doesn't matter).

    I tried this formula but it did not work:

    =IF(ISNUMBER(SEARCH("0",A1:A5)),B1,0)


    Question 2: In A7, I would like to have a formula that does the same thing as A6, but only if 2 or more of the cells in A1:A5 have been changed to zero.

    Thanks in advance for your help.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Trouble using ISNUMBER and SEARCH within a range

    1.
    =if(countif(a1:a5,0)=1,b1,0)
    2.
    =if(countif(a1:a5,0)>1,b1,0)

  3. #3
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    365
    Posts
    1,256

    Re: Trouble using ISNUMBER and SEARCH within a range

    Q1.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    q2.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Hope that helps,

    Colin

    RAD Excel Blog

  4. #4
    Registered User
    Join Date
    04-18-2013
    Location
    Collierville, Tennessee
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Trouble using ISNUMBER and SEARCH within a range

    Colin -

    Thanks. That did work, except that I wasn't clear about one thing in my original question.

    If there is only 1 zero, then the value in A6 should change. If there are 2 or more zeros, then the value in A7 should also change, but I need the new value in A6 (which changed when there was one zero) to remain there when A7 changes.

    Any thoughts on this? Sorry for not making that clear originally.
    When

  5. #5
    Forum Expert Colin Legg's Avatar
    Join Date
    03-30-2008
    Location
    UK
    MS-Off Ver
    365
    Posts
    1,256

    Re: Trouble using ISNUMBER and SEARCH within a range

    Sure, so A6 should show the value in B1 if there are 1 or more zeroes, meaning you can simplify the formula to this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    04-18-2013
    Location
    Collierville, Tennessee
    MS-Off Ver
    Excel 2010
    Posts
    27

    Re: Trouble using ISNUMBER and SEARCH within a range

    Ok. I'm an idiot. I should have known that - I think I have been working in this stupid chart way too long today.

    Thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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