If then statement

[Anna McKenzie]

I would like to set up an if then statement incorporating the language below:

If AK11 is <= 243.9 then it is grade M01
If AK11 is >=244 but less than 313 then M02
If AK11 is >=313 but less than 382 then M03
If AK11 is >=382 but less than 451 then M04
If AK11 is >=451 but less than 520 then M05

Is this possible. Pls advise.

EggHeadCafe - Software Developer Portal of Choice
XCeed Streaming Compression Library
http://www.eggheadcafe.com/tutorial...f-769ec7085cf2/xceed-streaming-compressi.aspx

 

[Glenn]

I would like to set up an if then statement incorporating the language below:

If AK11 is <= 243.9 then it is grade M01
If AK11 is >=244 but less than 313 then M02
If AK11 is >=313 but less than 382 then M03
If AK11 is >=382 but less than 451 then M04
If AK11 is >=451 but less than 520 then M05

Is this possible. Pls advise.

EggHeadCafe - Software Developer Portal of Choice
XCeed Streaming Compression Library
http://www.eggheadcafe.com/tutorial...f-769ec7085cf2/xceed-streaming-compressi.aspx

=LOOKUP(AK11,{0,243.9,313,382,451,520},{"M01","M02","M03","M04","M05","Undefined"})

 

[Glenn]

=LOOKUP(AK11,{0,243.9,313,382,451,520},{"M01","M02","M03","M04","M05","Undefined"})

Actually, you will want either 243.9000000001 or 244 in place of 243.9 in the
formula above, depending upon the number of decimal places in your data.

 

[Amish]

You could nest ifs, but it would probably be more efficient (and
easier to debug) if you used something like a vlookup instead.

 

[Pete_UK]

Set up a two-column table somewhere (eg AX1:AY6) as follows:

0 M01
244 M02
313 M03
382 M04
451 M05
520 Too large

Then you can use this formula:

=VLOOKUP(AK11,AX$1:AY$6,2)

Copy it down if required.

Hope this helps.

Pete

 

[Mattlynn via OfficeKB.com]

Col A Col B
0 M01
243.8 M01
243.9 M02
312 M02
313 M03
381 M03
382 M04
450 M04
451 M05
520 M05

In Cell D1 type =Lookup(C1,A1:B10)
The table acts as ranges for your M0 numbers. The lookup find the actual that
you input into C1 say 427, and the corresponding M0 number is retruned, in
this case it would be M04


Thanks
Matt