I have 3 columns with numbers in them and would like to compare if the number in the first column is between the two numbers in the remaining two columns. For example,

col A:52230810, col B:52230803, col C:52230813, because 52230803(B)<52230810(A)<52230813(C), so I will claim 52230810 TRUE. I used the following formula but somehow all comparison results show "FALSE".

IF(AND(D$1>$B2,D$1<$C2),"TRUE","FALSE")

D1 is where the "center" number is. B and C columns contain the lower and upper bound numbers. Where did I do wrong? Great many thanks.

I'm confused on a couple of levels. First, you say that the numbers are in Column A, Column B and Column C, then you say that D1 is where the center number is. What happened to Column A?

Second, I copied your numbers directly from your post into B2, C2 and D1 and your formula returned TRUE. That tells me that you don't really have the values you posted in your cells. Something must be different...maybe an extra space, so one of the value is text, or something like that. I'd manually enter your numbers into a another cell, say F1 and then use =F1=B2 and see if it returns TRUE as a means to see if your cells contain what you think they do.

Finally, when you put quotes around TRUE and FALSE, you are asking the IF function to return the text strings TRUE or FALSE. That is different than leaving off the quotes which will return the logical values TRUE or FALSE.

They are not the same thing and depending on what you plan to do with the values returned by the IF, it could make a difference.

To see this for yourself, try this:

A1: =IF(1=1, "TRUE","FALSE")

B1: =IF(1=1, TRUE, FALSE)

C1: =A1=B1Both A1 and B1 will contain TRUE, but you will find that C1 will be FALSE.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Ask Your Question

Weekly Poll