Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 28

Thread: Formula Not Returning Expected Value
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    Board Regular
    Join Date
    May 2003
    Posts
    386
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Not Returning Expected Value

    I am going to give this a try a bit later in the day.............but want to thank you for all of your time and willingness to assist me with this. If will come into great use for time to come with the project I am working on

  2. #12
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Formula Not Returning Expected Value

    You are welcome.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #13
    Board Regular
    Join Date
    May 2003
    Posts
    386
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Not Returning Expected Value

    Thanks so much Joe4
    This will save alot of time in future

  4. #14
    Board Regular
    Join Date
    May 2003
    Posts
    386
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Not Returning Expected Value

    Joe4

    Ran into a problem
    Q is my converted From range
    R is my converted To range
    S is the value I am looking up
    D is where the formula resides

    In cell D3 I have =IF(COUNTIFS(Q:Q,">=" & S3,R:R,"<=" & S3)>0,"Yes","No")

    The converted value in S3 is 1012619321
    The converted value in Q4 is 1012619321
    The converted value in R4 is 1012619322

    The formula should return "Yes" because the value in S3 is >= to Q4 value

  5. #15
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Formula Not Returning Expected Value

    You have the ">" and "<" signs reversed (take a look at the original formula I posted in post 10).
    I know it seems a little backwards, but it really is not, if you break down the formula and really think about it.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #16
    Board Regular
    Join Date
    May 2003
    Posts
    386
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Not Returning Expected Value

    replaced with =IF(COUNTIFS(Q:Q,"<=" & S3,R:R,">=" & S3)>0,"Yes","No")
    still get the same results

  7. #17
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Formula Not Returning Expected Value

    It works for me.

    Place these formulas in any blank cells and tell me what they return:
    =ISNUMBER(S3)
    =ISNUMBER(Q4)
    =ISNUMBER(R4)
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  8. #18
    Board Regular
    Join Date
    May 2003
    Posts
    386
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Not Returning Expected Value

    all 3 return false

  9. #19
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    49,667
    Post Thanks / Like
    Mentioned
    51 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Formula Not Returning Expected Value

    That means that none of those values are numbers, but they are text.
    How exactly are you converting those IP addresses to numbers?
    Are you using the User Defined Function I gave you, or are you doing it another way?
    My function will return a number, not text, so I suspect you are doing it some other way.
    Last edited by Joe4; Jun 19th, 2019 at 03:06 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #20
    Board Regular
    Join Date
    May 2003
    Posts
    386
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula Not Returning Expected Value

    I made sure that the From, To and Search numbers are all valid numbers. I did a =VALUE(X) against all of them and used the resulting data

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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