Discussion Re: Trouble creating formula for yes/no of values in a range in Excel
https://techcommunity.microsoft.com/t5/excel/trouble-creating-formula-for-yes-no-of-values-in-a-range/m-p/1954595#M82834
<P><LI-USER uid="570951"></LI-USER> thank you so much! this was really helpful!</P>Thu, 03 Dec 2020 22:55:33 GMTanniehoang2020-12-03T22:55:33ZTrouble creating formula for yes/no of values in a range
https://techcommunity.microsoft.com/t5/excel/trouble-creating-formula-for-yes-no-of-values-in-a-range/m-p/1954261#M82824
<P>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.</P><P> </P><P>I have attached snapshot to help follow through. Additionally, I am unable to upload the actual spreadsheet due to proprietary information. </P><P> </P><P>I need Column P "Overlap search" to answer "yes" or "no" for EACH row using Columns N and O under these conditions:</P><P>It should be...:</P><P>YES: If<SPAN> </SPAN><EM>ONE </EM><STRONG>OR</STRONG><SPAN> </SPAN><EM>BOTH</EM><SPAN> </SPAN>of the values from columns N and O contain a value between the range 400-500.</P><P>NO: If<SPAN> </SPAN><EM>NONE</EM><SPAN> </SPAN>of the values from columns N and O contain a value between the range 400-500.</P><P> </P><P>I have attached another snapshot to explain through:</P><P>For example:</P><P>In row 8, 8700 (N8) and 8900 (O8),<SPAN> </SPAN><STRONG>NEITHER/NONE</STRONG><SPAN> </SPAN>of these numbers are between 400-500, so this is a "<STRONG>NO"</STRONG><SPAN> </SPAN>in Column P.</P><P> </P><P>In row 12, 225 (N12) and 400 (O12),<SPAN> </SPAN><STRONG>ONE</STRONG><SPAN> </SPAN>of these numbers (400) are between 400-500, even though 225 is not, ONE of the values is. so this is a "<STRONG>YES"</STRONG><SPAN> </SPAN>in Column P.</P><P> </P><P>In row 35, 406 (N35) and 420 (O35),<SPAN> </SPAN><STRONG>BOTH</STRONG><SPAN> </SPAN>of these numbers (406 and 420) are between 400-500. so this is "<STRONG>YES"</STRONG><SPAN> </SPAN>in Column P.</P><P> </P><P>Please help me formulate a Yes/No in column P formula using values from columns N and O for each row! .</P>Thu, 03 Dec 2020 20:55:32 GMThttps://techcommunity.microsoft.com/t5/excel/trouble-creating-formula-for-yes-no-of-values-in-a-range/m-p/1954261#M82824anniehoang2020-12-03T20:55:32ZRe: Trouble creating formula for yes/no of values in a range
https://techcommunity.microsoft.com/t5/excel/trouble-creating-formula-for-yes-no-of-values-in-a-range/m-p/1954381#M82826
Try: =IF(OR(MEDIAN(N2,400,500)=N2,MEDIAN(O2,400,500)=O2),"YES","NO")Thu, 03 Dec 2020 21:18:23 GMThttps://techcommunity.microsoft.com/t5/excel/trouble-creating-formula-for-yes-no-of-values-in-a-range/m-p/1954381#M82826JMB172020-12-03T21:18:23ZRe: Trouble creating formula for yes/no of values in a range
https://techcommunity.microsoft.com/t5/excel/trouble-creating-formula-for-yes-no-of-values-in-a-range/m-p/1954384#M82828
<P><LI-USER uid="889333"></LI-USER> you can use this formula</P><LI-CODE lang="excel">=IF(OR(AND($N1>400,$N1<500),AND($O1>400,$O1<500)),"Yes","No")</LI-CODE><P>and copy down or if you have array function you can use</P><P> </P><LI-CODE lang="excel">=IF(OR(AND($N1:$N100>400,$N1:$N100<500),AND($O1:$O100>400,$O1:$O100<500)),"Yes","No")</LI-CODE><P> </P><P>and just put it in the first cell and have the ranges reflect the range where the data is.</P>Thu, 03 Dec 2020 21:21:15 GMThttps://techcommunity.microsoft.com/t5/excel/trouble-creating-formula-for-yes-no-of-values-in-a-range/m-p/1954384#M82828mtarler2020-12-03T21:21:15ZRe: Trouble creating formula for yes/no of values in a range
https://techcommunity.microsoft.com/t5/excel/trouble-creating-formula-for-yes-no-of-values-in-a-range/m-p/1954595#M82834
<P><LI-USER uid="570951"></LI-USER> thank you so much! this was really helpful!</P>Thu, 03 Dec 2020 22:55:33 GMThttps://techcommunity.microsoft.com/t5/excel/trouble-creating-formula-for-yes-no-of-values-in-a-range/m-p/1954595#M82834anniehoang2020-12-03T22:55:33Z