Excel Formulas - Nested IF Restrictions

Cork Skate

Folks,

I need to run a nested if to sort a column of data (lots of columns, lots of data) and i'm restricted with the amounts of possible searchs/checks with a nested if (i can only put in around 10-12 values or whatever, basically the function doesn't work when i go over the threshold).

Is there a way to have a list of 50+ values to sort data (by that i mean put a value in the next column and sort that way)?

This will save me loads (days) of time so i need to nail it.

One good thing about the nested if is once its search for the first value, it excludes it for the rest of the search which is good when a part of a value is another value, if you know what i mean.

Mr.Torrent

Cork Skate wrote: »

Folks,

I need to run a nested if to sort a column of data (lots of columns, lots of data) and i'm restricted with the amounts of possible searchs/checks with a nested if (i can only put in around 10-12 values or whatever, basically the function doesn't work when i go over the threshold).

Is there a way to have a list of 50+ values to sort data (by that i mean put a value in the next column and sort that way)?

This will save me loads (days) of time so i need to nail it.

One good thing about the nested if is once its search for the first value, it excludes it for the rest of the search which is good when a part of a value is another value, if you know what i mean.

Excel's IF function provides some simple decision-making capability to a worksheet. The IF function accepts three arguments:

The condition being evaluated (should result in either TRUE or FALSE)
The value to display if the condition is TRUE
The value to display if the condition is FALSE
The formula below, for example, returns 1 if cell A1 contains "A". If cell A1 does not contain "A", the formula returns an empty string.

=IF(A1="A",1,"")
For more decision-making power, you can "nest" IF functions within a formula. In other words, you can use an IF function as the second argument for an IF function. Here's an example:

=IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,"")))
This formula checks cell A1. If it contains "A", the formula returns 1. If it doesn't contain "A", then the second argument is evaluated. The second argument contains another IF function that determines if A1 contains a "B". If so, the formula returns 2; if not, the formula evaluates the IF function contained in the second argument and checks to see if A1 contains "C". If so, it returns 3; otherwise, it returns an empty string.

Excel allows up to seven levels of nested IF functions. The formula below works correctly, but Excel will not allow you to nest the IF functions any deeper than this.

=IF(A1="A",1,IF(A1="B",2,IF(A1="C",3,IF(A1="D",4,
 IF(A1="E",5,IF(A1="F",6,IF(A1="G",7,IF(A1="H",8,""))))))))
The sections that follow present various ways to get around the limit of seven nested IF functions. Be aware that these techniques may not be appropriate for all situations.

Note:
Excel 2007 and later allows up to 64 nesting levels
Using a VLOOKUP formula

In many cases, you can avoid using IF functions and use a VLOOKUP function. This will require a separate table in your worksheet. In the figure below, the lookup table is in B1:C10. The formula in A2 is:

=VLOOKUP(A1,B1:C10,2)


Using the CHOOSE function

In some cases, you can use the CHOOSE function. The first argument is an integer, and the value determines which of the subsequent arguments is evaluated and returned.

Using defined names

Another way to overcome the nested IF function limit is to use named formulas. Chip Pearson describes this technique at his web site, so I won't repeat it here.

Nested Functions, at cpearson.com
Using the CONCATENATE function

Yet another option was suggested to me by B. Ganesh: Use the CONCATENATE function. In this case, each argument for CONCATENATE consists of an IF function. Here's an example:

=CONCATENATE(IF(A1="A",1,""),IF(A1="B",2,""),IF(A1="C",3,""),
IF(A1="D",4,""),IF(A1="E",5,""),IF(A1="F",6,""),IF(A1="G",7,""),
IF(A1="H",8,""),IF(A1="I",9,""),IF(A1="J",10,""))
The CONCATENATE function can handle as many as 30 arguments -- which equates to testing 30 different conditions.

And, as Alan Williams pointed out, you can avoid using the CONCATENATE function and use the concatenation operator (&):

=IF(A1="A",1,"")&IF(A1="B",2,"")&IF(A1="C",3,"")
&IF(A1="D",4,"")&IF(A1="E",5,"")&IF(A1="F",6,"")
&IF(A1="G",7,"")&IF(A1="H",8,"")&IF(A1="I",9,"")
&IF(A1="J",10,"")
This method is not limited to 30 comparisons.

Use Boolean multiplication

Another alternative, suggest by Daniel Filer is to use Boolean multiplication. This technique takes advantage of the fact that, when multiplying, TRUE is treated as 1 and FALSE is treated as 0. Here's an example:

=(A1="A")*1+(A1="B")*2+(A1="C")*3+(A1="D")*4+(A1="E")*5
+(A1="F")*6+(A1="G")*7+(A1="H")*8+(A1="I")*9+(A1="J")*10
Creating a custom VBA function

The final alternative is to create a custom worksheet function, using VBA. The advantage is that you can customize the function to meet your requirements, and your formulas can be simplified.

Irish Steve

What do you want to do with the sorted result, it may be quicker and easier to sort the whole data file, or create a new field that is a composite of several fields and then sort on that field.

If the original file can't be changed in any way, then it may be possible to create another file that contains a link to the records in the first file and sort on that data.

It all depends on what you're trying to achieve as the end result.

beauf

Maybe you should bring it in to Access or SQL sort it, then export it out.