IF & Then statement

Fahsid1230

Active Member
Joined
Dec 26, 2006
Messages
282
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

ExcelChampion

Well-known Member
Joined
Aug 12, 2005
Messages
976
You could use a LOOKUP formula to this and it would be easier.
Book1
ABCDE
1LookupTableLookupvalueFormula
201769224
33192
45208
57224
610240
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

Well-known Member
Joined
Jun 23, 2006
Messages
7,128
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
  2. Mobile
  3. Web
=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

Legend
Joined
Feb 18, 2002
Messages
13,690
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.
 

Forum statistics

Threads
1,181,056
Messages
5,927,864
Members
436,573
Latest member
CMR237

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top