SOLVED

Trouble creating formula for yes/no of values in a range

Copper Contributor

Hello! I need help with computing a formula which would answer "yes/no" in a cell if a value(s) is between a value range.

 

I have attached snapshot to help follow through. Additionally, I am unable to upload the actual spreadsheet due to proprietary information. 

 

I need Column P "Overlap search" to answer "yes" or "no" for EACH row using Columns N and O under these conditions:

It should be...:

YES: If ONE OR BOTH of the values from columns N and O contain a value between the range 400-500.

NO: If NONE of the values from columns N and O contain a value between the range 400-500.

 

I have attached another snapshot to explain through:

For example:

In row 8, 8700 (N8) and 8900 (O8), NEITHER/NONE of these numbers are between 400-500, so this is a "NO" in Column P.

 

In row 12, 225 (N12) and 400 (O12), ONE of these numbers (400) are between 400-500, even though 225 is not, ONE of the values is. so this is a "YES" in Column P.

 

In row 35, 406 (N35) and 420 (O35), BOTH of these numbers (406 and 420) are between 400-500. so this is "YES" in Column P.

 

Please help me formulate a Yes/No in column P formula using values from columns N and O for each row! .

3 Replies
Try: =IF(OR(MEDIAN(N2,400,500)=N2,MEDIAN(O2,400,500)=O2),"YES","NO")
best response confirmed by anniehoang (Copper Contributor)
Solution

@anniehoang you can use this formula

=IF(OR(AND($N1>400,$N1<500),AND($O1>400,$O1<500)),"Yes","No")

and copy down or if you have array function you can use

 

=IF(OR(AND($N1:$N100>400,$N1:$N100<500),AND($O1:$O100>400,$O1:$O100<500)),"Yes","No")

 

and just put it in the first cell and have the ranges reflect the range where the data is.

@mtarler thank you so much! this was really helpful!

1 best response

Accepted Solutions
best response confirmed by anniehoang (Copper Contributor)
Solution

@anniehoang you can use this formula

=IF(OR(AND($N1>400,$N1<500),AND($O1>400,$O1<500)),"Yes","No")

and copy down or if you have array function you can use

 

=IF(OR(AND($N1:$N100>400,$N1:$N100<500),AND($O1:$O100>400,$O1:$O100<500)),"Yes","No")

 

and just put it in the first cell and have the ranges reflect the range where the data is.

View solution in original post