SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-1954261%22%20slang%3D%22en-US%22%3ETrouble%20creating%20formula%20for%20yes%2Fno%20of%20values%20in%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1954261%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%20I%20need%20help%20with%20computing%20a%20formula%20which%20would%20answer%20%22yes%2Fno%22%20in%20a%20cell%20if%20a%20value(s)%20is%20between%20a%20value%20range.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20snapshot%20to%20help%20follow%20through.%20Additionally%2C%20I%20am%20unable%20to%20upload%20the%20actual%20spreadsheet%20due%20to%20proprietary%20information.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20Column%20P%20%22Overlap%20search%22%20to%20answer%20%22yes%22%20or%20%22no%22%20for%20EACH%20row%20using%20Columns%20N%20and%20O%20under%20these%20conditions%3A%3C%2FP%3E%3CP%3EIt%20should%20be...%3A%3C%2FP%3E%3CP%3EYES%3A%20If%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3EONE%26nbsp%3B%3C%2FEM%3E%3CSTRONG%3EOR%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3EBOTH%3C%2FEM%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eof%20the%20values%20from%20columns%20N%20and%20O%20contain%20a%20value%20between%20the%20range%20400-500.%3C%2FP%3E%3CP%3ENO%3A%20If%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CEM%3ENONE%3C%2FEM%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eof%20the%20values%20from%20columns%20N%20and%20O%20contain%20a%20value%20between%20the%20range%20400-500.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20another%20snapshot%20to%20explain%20through%3A%3C%2FP%3E%3CP%3EFor%20example%3A%3C%2FP%3E%3CP%3EIn%20row%208%2C%208700%20(N8)%20and%208900%20(O8)%2C%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3ENEITHER%2FNONE%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eof%20these%20numbers%20are%20between%20400-500%2C%20so%20this%20is%20a%20%22%3CSTRONG%3ENO%22%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ein%20Column%20P.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20row%2012%2C%20225%20(N12)%20and%20400%20(O12)%2C%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EONE%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eof%20these%20numbers%20(400)%20are%20between%20400-500%2C%20even%20though%20225%20is%20not%2C%20ONE%20of%20the%20values%20is.%20so%20this%20is%20a%20%22%3CSTRONG%3EYES%22%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ein%20Column%20P.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20row%2035%2C%20406%20(N35)%20and%20420%20(O35)%2C%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3EBOTH%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eof%20these%20numbers%20(406%20and%20420)%20are%20between%20400-500.%20so%20this%20is%20%22%3CSTRONG%3EYES%22%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ein%20Column%20P.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help%20me%20formulate%20a%20Yes%2FNo%20in%20column%20P%20formula%20using%20values%20from%20columns%20N%20and%20O%20for%20each%20row!%20.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1954261%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1954381%22%20slang%3D%22en-US%22%3ERe%3A%20Trouble%20creating%20formula%20for%20yes%2Fno%20of%20values%20in%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1954381%22%20slang%3D%22en-US%22%3ETry%3A%20%3DIF(OR(MEDIAN(N2%2C400%2C500)%3DN2%2CMEDIAN(O2%2C400%2C500)%3DO2)%2C%22YES%22%2C%22NO%22)%3C%2FLINGO-BODY%3E
Occasional 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 (Occasional 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!