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

1. ## 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  Reply With Quote

2. ## Re: Formula Not Returning Expected Value

You are welcome.  Reply With Quote

3. ## Re: Formula Not Returning Expected Value

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

4. ## 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  Reply With Quote

5. ## 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.  Reply With Quote

6. ## Re: Formula Not Returning Expected Value

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

7. ## 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)  Reply With Quote

8. ## Re: Formula Not Returning Expected Value

all 3 return false  Reply With Quote

9. ## 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.  Reply With Quote

10. ## 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  Reply With Quote

## User Tag List

brackets, feeling, formula, incorrect, returning 