Formula Not Returning Expected Value

meppwc

Well-known Member
Joined
May 16, 2003
Messages
604
Office Version
  1. 365
Platform
  1. Windows
Can someone tell me why this formula is returning "FALSE" opposed to "YES" or "NO" ?
I have a feeling the brackets are incorrect

=IF(AND(($F$2>=A4),($C$2<=B4)),OR(IF(AND(($F$2>=A5),($C$2<=B5)),"YES","NO")))
 
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
 
Upvote 0

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Thanks so much Joe4
This will save alot of time in future
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
replaced with =IF(COUNTIFS(Q:Q,"<=" & S3,R:R,">=" & S3)>0,"Yes","No")
still get the same results
 
Upvote 0
It works for me.

Place these formulas in any blank cells and tell me what they return:
=ISNUMBER(S3)
=ISNUMBER(Q4)
=ISNUMBER(R4)
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top