Formula Not Returning Expected Value

meppwc

Active Member
Joined
May 16, 2003
Messages
422
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
 

Some videos you may like

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.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,465
Office Version
365
Platform
Windows
You are welcome.
 

meppwc

Active Member
Joined
May 16, 2003
Messages
422
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,465
Office Version
365
Platform
Windows
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
Joined
May 16, 2003
Messages
422
replaced with =IF(COUNTIFS(Q:Q,"<=" & S3,R:R,">=" & S3)>0,"Yes","No")
still get the same results
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,465
Office Version
365
Platform
Windows
It works for me.

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
53,465
Office Version
365
Platform
Windows
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
Joined
May 16, 2003
Messages
422
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,754
Messages
5,470,579
Members
406,707
Latest member
drkjz

This Week's Hot Topics

Top