Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.setup
|
|||
|
|||
How can I compare more than 7 items in a conditional statement?
I'm using Excel and trying to test items in conditional "if" statements for a
payroll worksheet with about 20 steps to test. Excel will only allow 7 nested "if's". Is there a way to override this limit or another way to accomplish what I need? |
#2
Posted to microsoft.public.excel.setup
|
|||
|
|||
How can I compare more than 7 items in a conditional statement?
Hi
There are techniques to get around the 7 level nesting limit in Excel, but you would probably be better served by having a lookup table. Post some more details about what you are trying to achieve, and someone will be able to help you. -- Regards Roger Govier "if'd out" <if'd wrote in message ... I'm using Excel and trying to test items in conditional "if" statements for a payroll worksheet with about 20 steps to test. Excel will only allow 7 nested "if's". Is there a way to override this limit or another way to accomplish what I need? |
#3
Posted to microsoft.public.excel.setup
|
|||
|
|||
How can I compare more than 7 items in a conditional statement
Thanks for the response Roger. I have about 20 steps in the salary schedule
which gives a different annual raise based on the step. As a simple illustration, moving from step 0 to step 1 gives the employee a $100 per year raise. From step 1 to step 2 gives a $200 raise, step 2 to step 3 a $300 raise, etc. to step 20. With all employees set up in an Excel spreadsheet, I am wanting my formula to read the step level of each employee and increase the pay of each employee by the appropriate amount according to the employee's step for the next year's budget prepartation. A lookup table might be more appropriate but I have never used one and don't know where to start. "Roger Govier" wrote: Hi There are techniques to get around the 7 level nesting limit in Excel, but you would probably be better served by having a lookup table. Post some more details about what you are trying to achieve, and someone will be able to help you. -- Regards Roger Govier "if'd out" <if'd wrote in message ... I'm using Excel and trying to test items in conditional "if" statements for a payroll worksheet with about 20 steps to test. Excel will only allow 7 nested "if's". Is there a way to override this limit or another way to accomplish what I need? |
#4
Posted to microsoft.public.excel.setup
|
|||
|
|||
How can I compare more than 7 items in a conditional statement
Hi
If your steps were exactly as you say throughout the range, then you wouldn't need IF's or Vlookup's but I suspect they don't follow an exactly linear increment from 1 to 20. Set up a table in A1:B20 0 0 1 100 2 200 .. .. 20 1200 Then with the step value in D1 =VLOOKUP(D1,$A$1:$B$20,2) The table can be placed anywhere, even on another sheet and would be better if it were a named range. InsertNameDefine Name Salaries Refers to =Sheet2!$A$1:$B$20 then =VLOOKUP(D1,salaries,2) -- Regards Roger Govier "if''d out" wrote in message ... Thanks for the response Roger. I have about 20 steps in the salary schedule which gives a different annual raise based on the step. As a simple illustration, moving from step 0 to step 1 gives the employee a $100 per year raise. From step 1 to step 2 gives a $200 raise, step 2 to step 3 a $300 raise, etc. to step 20. With all employees set up in an Excel spreadsheet, I am wanting my formula to read the step level of each employee and increase the pay of each employee by the appropriate amount according to the employee's step for the next year's budget prepartation. A lookup table might be more appropriate but I have never used one and don't know where to start. "Roger Govier" wrote: Hi There are techniques to get around the 7 level nesting limit in Excel, but you would probably be better served by having a lookup table. Post some more details about what you are trying to achieve, and someone will be able to help you. -- Regards Roger Govier "if'd out" <if'd wrote in message ... I'm using Excel and trying to test items in conditional "if" statements for a payroll worksheet with about 20 steps to test. Excel will only allow 7 nested "if's". Is there a way to override this limit or another way to accomplish what I need? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using IF statement to compare text | Excel Worksheet Functions | |||
How do I compare two lists and choose the items that are in both? | Excel Worksheet Functions | |||
Formula to compare two lists and separate non-recurring items? | Excel Worksheet Functions | |||
Compare 2 columns, and create a list of items that are in both lists | Excel Worksheet Functions | |||
compare time in IF statement | Excel Worksheet Functions |