| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
function nesting limitations
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? |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
function nesting limitations
Look in the help index for LOOKUP or VLOOKUP
-- Don Guillett Microsoft MVP Excel SalesAid Software "Bitter Clinger" wrote in message ... 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? |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
function nesting limitations
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. "Don Guillett" wrote in message ... Look in the help index for LOOKUP or VLOOKUP -- Don Guillett Microsoft MVP Excel SalesAid Software "Bitter Clinger" wrote in message ... 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? |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
function nesting limitations
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. -- Thanks, Shane Devenshire "Bitter Clinger" wrote: 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. "Don Guillett" wrote in message ... Look in the help index for LOOKUP or VLOOKUP -- Don Guillett Microsoft MVP Excel SalesAid Software "Bitter Clinger" wrote in message ... 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? |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
function nesting limitations
There is also an interesting solution at 'this site' (http://www.mcgimpsey.com/excel/variablerate.html) using SUMPRODUCT -- Pecoflyer ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=24127 |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
function nesting limitations
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 -- Cheers macropod [MVP - Microsoft Word] "Bitter Clinger" wrote in message ... 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? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Nesting Networkdays function inside and If function | Excel Worksheet Functions | |||
| Indirect function - Limitations | Excel Worksheet Functions | |||
| function cell range limitations | Excel Worksheet Functions | |||
| How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions | |||
| Nesting A Function | Excel Worksheet Functions | |||
Linear Mode
