Forum Discussion
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 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! .
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.
3 Replies
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!
- Try: =IF(OR(MEDIAN(N2,400,500)=N2,MEDIAN(O2,400,500)=O2),"YES","NO")
