function nesting limitations

  • Thread starter Thread starter Bitter Clinger
  • Start date Start date
B

Bitter Clinger

I'm trying to write a formula to check for sales totals and then calculate
the correct comission. I need to test for nine conditions but Excel limits
the nesting to seven. How do I get around this limitation?
 
It looks as if VLOOKUP may work but I have other questions now. In using an
array, do I have to use constants, or can I use formulas? I need to take
the sales figure and find the range it is in and then multiply it by a set
percentage to come up with the comission. As the sales figure goes higher,
so does the comission percentage. So how would I use VLOOKUP to find the
range the sales fits into, and then calculate the correct comission and
place it in the proper cell? And can the array reside on another sheet in
the workbook, and if so, how would you reference the other sheet?

Sorry for so many questions.
 
Hi,

The limit in Excel 2003 and earlier the limit is 7 levels although there are
a number of ways to beat that. In 2007 that limit is 64!

Even so the correct approach is VLOOKUP(Key,Table,Column,Type)

You would set up a Table like this in C1:D3:

1000 5%
2000 7%
3000 11%

If you want the rate for $1540 in cell A1 the formula would be
=VLOOKUP(A1,C1:D3,2,TRUE)
2 means you want the value back from column 2 of the table. TRUE means you
are doing an approximate match, that is if the value 1540 isn't found in the
first column then the one above it (a lower row number) is used. In our
example 5% is returned. when you are using an approximate match the table is
always sorted ascending on the first column, this is how Excel knows which
one to pick.
 
Hi Bitter Clinger,

Depending on what you're doing, you may not even need the IF function.

For example, suppose you want to test the value in A1 and, depending on the value there, multiply the value in B1 by the value in
another cell. For that you could use something like:
=((A1=0)*D3+(A1=1)*D4+(A1=2)*D5+(A1=3)*D6)*B1
instead of:
=IF(A1=0,D3,IF(A1=1,D4,IF(A1=2,D5,IF(A1=3,D6,0))))*B1
 
Back
Top