Embedded If statements

[Rockerdo]

I need help putting in ranges for my embedded IF's in a formula

If $A7 is = to 1-7 (meaning 1,2,3,4,5,6,7) return value 0, if $A7 is = 8-22 return value of 1, IF $A7 is 23-37 return value of 2, if $A7 is 38-52 return value of 3

Thank you

 

[Scott T]

What should be returned when a7 is less then 0 or greater then 52? The below formula returns blank.

=IF(AND(A7>=1,A7<=7),0,IF(AND(A7>=8,A7<=22),1,IF(AND(A7>=23,A7<=37),2,IF(AND(A7>=38,A7<=52),3,""))))

 

[J03xcel]

=if($a7>=38, 3, if($a7>=23,2,if($a7>=8,1,if($a7>=1,0,""))))

 

[jim may]

Not sure if you can use, but for teaching purposes it's best to set up a Lookup Value Table (My Column A-C) and refer
to it elsewhere in your Entry section... Just saying... FWIW... Copy G3 down to G6

Excel 2010

	A	B	C	D	E	F	G
Value from

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]** Controlling Table of Values **[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Your[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]Min[/TD]
[TD="align: center"]Max[/TD]
[TD="align: center"]Value[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Entry Amt[/TD]
[TD="align: center"]Table[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]22[/TD]
[TD="align: right"]1[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]23[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]2[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]38[/TD]
[TD="align: right"]52[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]48[/TD]
[TD="align: right"]3[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G3[/TH]
[TD="align: left"]=LOOKUP(F3,$A$3:$C$6)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rockerdo]

Yes thank you. If A7 is blank or 0 it will be ""
It will be rare it is over 52. I can expand the formula further to include up to the 7 IF's.

VLookup is my preferred but there are reasons we need another option.

Thank you

 

[jtakw]

Hi,

If you don't want to build a Table, instead of Nesting a bunch of IF statements, you can do this:

Book1
	A	B	C
7	2		0
8	7		0
9
10	8		1
11	22		1
12	24		2
13	37		2
14	39		3
15	52		3
16	55
17	38		3
Sheet7

Cell Formulas
Range	Formula
C7	=LOOKUP(A7,{0,1,8,23,38,53},{"",0,1,2,3,""})