# Formula Not Returning Expected Value

#### meppwc

##### Active Member
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

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

You are welcome.

#### meppwc

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

#### meppwc

##### Active Member
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

#### Joe4

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.

#### meppwc

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

#### Joe4

It works for me.

Place these formulas in any blank cells and tell me what they return:
=ISNUMBER(S3)
=ISNUMBER(Q4)
=ISNUMBER(R4)

#### meppwc

##### Active Member
all 3 return false

#### Joe4

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:

#### meppwc

##### Active Member
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