 SOLVED

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

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 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

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

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

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

@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.

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

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