Back to Dates in Excel archive index

Back to archive home

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

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

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

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

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!

also found this info http://www.cpearson.com/excel/nested.htm

but haven't figured out how to do it yet!

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

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...

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.

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

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

thanks alot aladin...

wil give it a try

wil give it a try

that did the trick..thanks....

This archive is from the original message board at www.MrExcel.com.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.

All contents © 1998-2004 MrExcel.com.

Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.

Microsoft Excel is a registered trademark of the Microsoft Corporation.

MrExcel is a registered trademark of Tickling Keys, Inc.