Limit to IF conditional in Excel? Can't get past 8 places

[Knight777]

Hi all,

thanks in advance for your help. I'm using this formula:

=IF(C4=1,"20",IF(C4=2,"15",IF(C4=3,"12",IF(C4=4,"10",IF(C4=5,"7",IF(C4=6,"5",IF(C4=7,"4",IF(C4=8,"3","0"))))))))

But when I add one more check, I get an error. For example, this gives
me an error:

=IF(C4=1,"20",IF(C4=2,"15",IF(C4=3,"12",IF(C4=4,"10",IF(C4=5,"7",IF(C4=6,"5",IF(C4=7,"4",IF(C4=8,"3",IF(C4=9,"2","0")))))))))

I'm pretty sure I've got it setup correctly. Any ideas? Thanks!

 

[CyberTaz]

Excel is limited to nesting 7 functions. In order to accomplish what
you are trying to do you might want to take a look at the use of AND,
OR, and NOT functions.

They are much more efficient & easier to deal with for accomodating
multiple conditions. There may also be other options available for the
purpose, so check back for other replies & perhaps share more about the
nature of your data & intended purpose.

HTH |:>)

 

[PhilD]

Excel is limited to nesting 7 functions. In order to accomplish what
you are trying to do you might want to take a look at the use of AND,
OR, and NOT functions.

And if (no pun) you still need your nested IF statements, do the
calculation in several parts. That is, Column A would have IF[lots of
things], Column B would have IF[lots more things], and Column C a final
IF to choose between Columns A and B. This final part could go in
Column B, too, if there's space. Then hide Column A (and B if
appropriate).

Verging on the clumsy, but this will get round the nesting limit if you
still want to use IF statements.

PhilD

 

[CyberTaz]

Hello again -

After taking a closer look at what you're nesting it seems that an even
better approach may be to create a table of values (1-9 per your
example) & their corresponding counterparts in adjacent cells. Then use
a VLOOKUP to return the appropriate result from the table.

The following returns the value from the second column of the lookup
table (cells J1:K9) if the value in C4 is 1-9. If the value in C4 is
greater than 9, 0, or blank the formula returns an X.

=IF(OR(ISNA(VLOOKUP(C4,J1:K9,2)),C4>9),"X",VLOOKUP(C4,J1:K9,2))

HTH |:>)

 

[JE McGimpsey]

Hello again -

After taking a closer look at what you're nesting it seems that an even
better approach may be to create a table of values (1-9 per your
example) & their corresponding counterparts in adjacent cells. Then use
a VLOOKUP to return the appropriate result from the table.

The following returns the value from the second column of the lookup
table (cells J1:K9) if the value in C4 is 1-9. If the value in C4 is
greater than 9, 0, or blank the formula returns an X.

=IF(OR(ISNA(VLOOKUP(C4,J1:K9,2)),C4>9),"X",VLOOKUP(C4,J1:K9,2))

VLOOKUP is definitely the way to go.

Note that the OR isn't necessary if the range_lookup argument = FALSE:

=IF(ISNA(VLOOKUP(C4,J:K,2,FALSE)),0,VLOOKUP(C4,J:K,2,FALSE))


While it's not a significant change, I generally prefer to use MATCH
rather than VLOOKUP in the comparison, since it's a tiny bit more
efficient in that it doesn't need to return a value. It'll save a few
nanoseconds, but may be significant if there are thousands of VLOOKUPs
in the worksheet:

=IF(ISNA(MATCH(C4,J:J,FALSE)),0,VLOOKUP(C4,J:K,2,FALSE))

 

[CyberTaz]

Excellent! I'd never used MATCH (although I can envision any number of
ocassions where it would have been useful), and I fear rust had covered
the FALSE argument.

Regards |:>)

 

[JE McGimpsey]

VLOOKUP is definitely the way to go.

Whoops - Looking back at the OP, I'll revise that slightly.

If there are 30 or less options for C4, starting at 1 and increasing by
1, this is a little simpler:

=CHOOSE(C4,20,15,12,10,7,5,4,3,2,1,0)

Likewise, for more flexibility, and again, as long as the C4 values
start at 1 and increase by 1, one could use a range to hold the values:

J K L M
1 20 15 12 10

and use

=INDEX(J1:Z1,C4)


Lots of ways to skin the cat...