If statement - multiple values and ranges

[ryanduck]

if I wanted to say:

If A1 is 01-02, 16-23, 36-37

or

If A1 is 03-05, 24-31, 38-40, 56-57

how would I write that?

I've been trying =if((or(range 1, range 2, range 3),"Yes","no"))

with no luck... represented 16-23 as "16<A1<23"

 

[West Man]

Try: =IF(OR(AND(A1>=1,A1<=5),AND(A1>=16,A1<=31),AND(A1>=36,A1<=40),AND(A1>=56,A1<=57)),"yes","no")

 

[Jonmo1]

You will get much better results if you post

What you want to do IF A1 is within each range..

01-02 then Return x
03-05 then return y
etc..

I would imagine a lookup would work well for this.

 

[jasonb75]

Maybe

=IF(MATCH(A1,{1,16,36})+MATCH(A1,{37,23,2},-1)=4,"yes","No")

 

[ryanduck]

You will get much better results if you post

What you want to do IF A1 is within each range..

01-02 then Return x
03-05 then return y
etc..

I would imagine a lookup would work well for this.

Ok.

if A1 is within any of these ranges (01-02, 16-23, 36-37) I want to return "X"
if A1 is within any of these ranges (03-05, 24-31, 38-40, 56-57) I want to return "Y"
if A1 is within any of these ranges (06-09, 32-34, 41-48, 58-60, 76-77) I want to return "Z"
if A1 is within any of these ranges (10-12, 35, 49-54, 61-66, 78-81) I want to return "A"
if A1 is within any of these ranges (13-15, 55, 67-75, 82-95) I want to return "B"

 

[Jonmo1]

Something like this perhaps..

Excel Workbook
	A	B	C	D	E	F
1	37		X		1	X
2					3	Y
3					6	Z
4					10	A
5					13	B
6					16	X
7					24	Y
8					32	Z
9					35	A
10					36	X
11					38	Y
12					41	Z
13					49	A
14					55	B
15					56	Y
16					58	Z
17					61	A
18					67	B
19					76	Z
20					78	A
21					82	B
Sheet1

 

[ryanduck]

awesome, thank you. Learn something new every day

 

[jasonb75]

Maybe extend the table jonmo suggested to F22

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 46px"><COL style="WIDTH: 46px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>E</TD><TD>F</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">22</TD><TD style="TEXT-ALIGN: right">96</TD><TD>#N/A</TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>F22</TD><TD>=NA()</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>
to stop values greater than 95 returning a "B" result.