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
1	** Controlling Table of Values **					Your	Value from
2	Min	Max	Value			Entry Amt	Table
3	1	7	0			8	1
4	8	22	1			22	1
5	23	37	2			30	2
6	38	52	3			48	3

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula G3 =LOOKUP(F3,$A$3:$C$6) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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,""})