IF & Then statement

[Fahsid1230]

Folks, Good Morning
Need your help
here is the logic:
<3 years =176
3 - 5 yeras = 192
5 - 7 years = 208
7 -10 years = 224
>10 years = 240

This is all comparing to colum which has 1 -10 (in years).
So folks, If I need to try the first logic (<3 years =176) I can write a statemen like this =if(E2<3, 176) but I dont know how to use the IF & Then statement for 3 - 5 years for a cap for 192 and so forth. Remember E is the colum to which we are comparing with.
Any help?
thx

 

[ExcelChampion]

You could use a LOOKUP formula to this and it would be easier.

Book1
	A	B	C	D	E
1	LookupTable			Lookupvalue	Formula
2	0	176		9	224
3	3	192
4	5	208
5	7	224
6	10	240
Sheet1

If you don't want to create a lookup table you could hard code the values into the formula as in:

=VLOOKUP(D2,{0,176;3,192;5,208;7,224;10,240},2)

 

[dave3009]

=IF(B18<=3,176,IF(AND(B18>3,B18<=5),192,IF(AND(B18>5,B18<=7),208,IF(AND(B18>7,B18<=10),224,IF(B18>10,240,)))))

Where B18 is your reference cell.

Kind Regards

Dave

 

[lenze]

A simple version

=IF(B18<=3,176,IF(B18<=5,192,IF(B18<=7,208,IF(B18<=10,224,240))))

Note that the ANDs are not necessary.

lenze

BTW: Todd's Lookup solution is quite viable and might be the best way to go depending on how large your dataset is. VLOOKUP will probably execute faster than the nested IFs.

 

[Fahsid1230]

thx so much.