"if "formula

Posted by Dave on July 21, 2001 5:46 PM

I need the IF formula, but need to be able to "nest " more than 7 arguments.....any ideas or alternatives??
I am making a spread sheet with text entries in one column and I wish to have the corresponding numeric code pop up in the adjacent cell.... the IF formula seems the best but I have about twenty different text entries and their corresponding #'s.
thanx

Posted by Cory on July 21, 2001 6:34 PM

I know one way off hand, but it's more of tricking excel into thinking there's only seven conditions at a time when there's really how ever many you want. I'm sure there's a more technical term for this technique, but I call it "chaining" formulas. Try this.

Assume you want your result in A1:

in A1 type your usual seven layer nested if statement, like so...

=If(C1=1,"One",If(C1=2,"Two",If(C1=3,"Three",If(C1=4,"Four",If(C1=5,"Five",If(C1=6,"Six",If(C1=7,"Seven",B1)))))))

Notice that last argument "B1". In B1 you would write the continuation of your formula; the next seven layers. You can keep going like that unlimited. If you make the last argument in B1's formula "C1", then you can continue another set of layers in C1. Make sense? I'm not sure if I even made sense to myself.

There is another way where you can nest up to 28 "conditions" with a different command, but I'm going to have to take a look at one of my sheets first. I'll post it later...

Hope I helped some...

Cory

Posted by Aladin Akyurek on July 21, 2001 10:04 PM

Can you be more specific about what you want to do?

Posted by Dave on July 22, 2001 4:57 AM

cory is on to what I am after....I will try that and see if it works....does it make more sense aladin having seen cory's post?
also found this info http://www.cpearson.com/excel/nested.htm
but haven't figured out how to do it yet!

Posted by Aladin Akyurek on July 22, 2001 6:08 AM

I've seen Cory's post, sure thing. I admit I argued at this bord more than once against nesting more than 3 IFs and proposed alternatives using VLOOKUP. If you wish to set up a formula containing 20 nested IFs and you're willing to debug such a formula when the need arises, please do so.

Aladin

Posted by dave on July 22, 2001 7:01 AM

no....don't want that much difficulty!...my excel skills are limited at best...don't even know how to use vlookup....did the pearson web site make any sense to you?....I tried to use that tecnique, but it was over my head as well...not sure where I was supposed to enter all the various formulas...

Posted by Aladin Akyurek on July 22, 2001 7:52 AM

I wish Pearson had removed that IF thing from his web site. Otherwise he has great collection of matters Excel. But, why not answer questions that I posed in my initial post regarding your problem?

Regards.

Posted by Dave on July 23, 2001 6:43 AM

thanx for the replies aladin
I have a medical spreadsheet with a column for procedures. Each of these has a corresponding numeric code that I would like to appear in the adjacent cell automatically...so that I do not have to type it in each time....there are about 2 dozen procedures, hence the problem with the 7 limit with nesting.
hope that is clearer. thanx again
Dave

Posted by Aladin Akyurek on July 23, 2001 7:43 AM

Dave,

Set up a 2-column table on a seaprate sheet like this:

procedure-1 numeric-code-1
procedure-2 numeric-code-2
etc.

Select the cells of this table, go to the Name Box, and type PROCEDURES and enter.

Now go the worksheet where you have all those procedures. Lets say that A1 is the first cell containing a procedure.

In B1 enter: =VLOOKUP(A1,PROCEDURES,2,0)

This formula retrieves the associated numeric code from PROCEDURES.

Note that this formula expects each procedure to be unique.

Copy down this formula as far as needed.

Aladin

Posted by Dave on July 23, 2001 8:48 AM

thanks alot aladin...
wil give it a try

Posted by dave on July 23, 2001 6:39 PM

that did the trick..thanks....