IF THEN statement in Excel 4

[cmpgeek]

i want to set a cell up in an Excel worksheet that will show a value depending on what appears in another cell...
there are 4 variables that will be involved...
ex:
if D1 is < 5 then W
else if D1 < 10 then X
else if D1 < 20 then Y
else if D1 > 20 then Z

does Excel have a basic IF/THEN ability that would work here, or do i need to go to the VBE and set it up there?

my vital stats: Crystal 8.5 / Oracle database / Windows XP...
... always in a state of ... inspiring to some day experience ...

 

[carrr]

See if this might help:

http://www.ozgrid.com/Excel/excel-nested-if-function.htm

&quot;'Tis an ill wind that blows no minds.&quot; - Malaclypse the Younger

 

[carrr]

To make it easy, I think this will work for you.

=IF(D1<5,&quot;W&quot;,IF(D1<10,&quot;X&quot;,IF(D1<20,&quot;Y&quot;,IF(D1>20,&quot;Z&quot)))

&quot;'Tis an ill wind that blows no minds.&quot; - Malaclypse the Younger

 

[KenWright]

Numerous choices.

Typical IF/THEN structure:-

=IF(D1<5,&quot;W&quot;,IF(D1<10,&quot;X&quot;,IF(D1<20,&quot;Y&quot;,&quot;Z&quot))

Limited to 7 nested levels. Often better performed using either LOOKUP, VLOOKUP, HLOOKUP or INDEX/MATCH or OFFSET/MATCH. Can also be accommodated using CHOOSE, eg:-

=CHOOSE(MIN(INT(D1/5)+1,5),&quot;W&quot;,&quot;X&quot;,&quot;Y&quot;,&quot;Y&quot;,&quot;Z&quot

Might look longer initially, but scales better. VLOOKUP is usually a better choice though, and can either be built around a table in the workbook, or all wrapped up in a single cell.

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------

 

[JVerdaasdonk]

Hi

=IF(D1<=5;&quot;W&quot;;IF(D1<=10;&quot;X&quot;;IF(D1<=20;&quot;Y&quot;;IF(D1>=20;&quot;Z&quot)))

I’ve added the smaller or equal to operator to make it more accurate. In you’re equation there would be an error if the value was 20 then the result would be false.

This works just fine

Enjoy,

Joost verdaasdonk
Note this should work, I'm working in another language! (but I think this nested If will work!)

 

[TonyJollans]

Hi cmpgeek,

The format of an IF Function in a cell is:

=if(logical_test,value_if_true,value_if_false)

If you enter =IF (or any other function) in a cell and press <CTRL><A> you will get a dialog which helps you enter things in the right format. If, instead, you enter <CTRL><SHIFT><A> you will get abbreviated help (which is where I copied the above definition from).

Functions can be nested (up to 7 levels deep) so the value_if_true and/or the value_if_false can themselves be IFs. In your case the end result is ..

=IF(D1<5,&quot;W&quot;,IF(D1<10,&quot;X&quot;,IF(D1<20,&quot;Y&quot;,&quot;Z&quot))

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[cmpgeek]

thanks to all of you for responding so quickly and being so helpful... i didnt want to leave any of you out so i gave you each a star...

i had seen the
=if(logical_test,value_if_true,value_if_false)
option in the function list, but it looked as if there could only be two variables... that was why i decided to post the question here...

i really try to not post questions here that i can answer for myself, i apologize for bothering yall with this one...


my vital stats: Crystal 8.5 / Oracle database / Windows XP...
... always in a state of ... inspiring to some day experience ...

 

[GeekGirlau]

To do the same thing with VLookup, create a range as follows:

0 W
5 X
10 Y
20 Z

Name this range (eg &quot;lkpCode&quot. Then if your value is in D1, your formula is

=Vlookup(D1,lkpCode,2,True)

The &quot;True&quot; tells Excel to lookup the closest value to D1 in the named range, rather than matching it exactly. Having a lookup range is a lot easier to maintain then hardcoding the values into your formula.

 

[mbarron]

Another option, if you don't want to add tables for vlookups or have more than 7 conditions that you want to check.

=LOOKUP(A1,{0,5,10,20},{&quot;W&quot;,&quot;X&quot;,&quot;Y&quot;,&quot;Z&quot;})

Mike

 

[cmpgeek]

luckily, in this situation anyway, there were only 4 options, and the ranges (0-5 / 5-10 / 10-20 / 20+) are not going to change anytime soon, which is why i was ok with hardcoding the values... if the ranges had some &quot;play&quot; in them, then i would have defintiely gone with some type of lookup function...

thanks again to everyone for their advice...

my vital stats: Crystal 8.5 / Oracle database / Windows 2000 Professional Edition...
... always in a state of ... inspiring to some day experience ...