Need something other than IF function

  • Thread starter Thread starter PrinceAli
  • Start date Start date
P

PrinceAli

=IF(AE196<=8,$Z$275,IF(AE196<=10,$Z$276,IF(AE196<=12,$Z
$277,IF(AE196<=14,$Z$278,IF(AE196<=16,$Z$279,IF(AE196<=18,$Z
$280,IF(AE196<=20,$Z$281,IF(AE196<=22,$Z$282,"DEEP"))))))))

AE196 is the depth of the structure

If the depth is less than X, then the trench width will be whatever
Z2XX is.

Unfortunately I cannot do more than 8 IF statements. Is there any
other better alternative to this?

Thanks.
 
Put the data in a table and use vlookup

http://www.contextures.com/xlFunctions02.html

scroll down until you see

"Create a VLookup formula for a range of values"

Insert a new column to the left of the Z column, of course if Y is unused
you don't need to do that and in Y275 put 0 (assuming the values cannot be
less than zero)
in Y276 put 10 and so on, then use

=IF(AE196="","",VLOOKUP(AE196,Y275:Z282,2))

change Y275:Z282 to what your final table dimension will be

--


Regards,


Peo Sjoblom
 
=IF(AE16<=8,$Z$275,IF(AE16>22,"DEEP",INDEX($Z$276:$Z$282,ROUNDUP((AE16-8)/2,0))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
 
Put the data in a table and use vlookup

http://www.contextures.com/xlFunctions02.html

scroll down until you see

"Create a VLookup formula for a range of values"

Insert a new column to the left of the Z column, of course if Y is unused
you don't need to do that and in Y275 put 0 (assuming the values cannot be
less than zero)
in Y276 put 10 and so on, then use

=IF(AE196="","",VLOOKUP(AE196,Y275:Z282,2))

change Y275:Z282 to what your final table dimension will be
Click to expand...


Just as an alternative, that you can try if it is not convenient to
use the Y column (or any other column to the left of column Z) for
your lookup vector, you can use the LOOKUP function that allows you to
put the lookup vector at any place, even in another sheet.

Lars-Åke
 
=IF(AE16<=8,$Z$275,IF(AE16>22,"DEEP",INDEX($Z$276:$Z$282,ROUNDUP((AE16-8)/2,0))))

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)
Click to expand...

Thank you!
 
Back
Top