Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Excel / Setup / March 2007

Tip: Looking for answers? Try searching our database.

How can I compare more than 7 items in a conditional statement?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
if'd out - 15 Mar 2007 05:48 GMT
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?
Roger Govier - 15 Mar 2007 12:34 GMT
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.
Signature

Regards

Roger Govier

> I'm using Excel and trying to test items in conditional "if"
> statements for a
[quoted text clipped - 3 lines]
> to
> accomplish what I need?
if''d out - 16 Mar 2007 06:00 GMT
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.

> Hi
>
[quoted text clipped - 10 lines]
> > to
> > accomplish what I need?
Roger Govier - 16 Mar 2007 10:01 GMT
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.
Insert>Name>Define>  Name     Salaries        Refers to
=Sheet2!$A$1:$B$20

then
=VLOOKUP(D1,salaries,2)

Signature

Regards

Roger Govier

> Thanks for the response Roger.  I have about 20 steps in the salary
> schedule
[quoted text clipped - 32 lines]
>> > to
>> > accomplish what I need?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.