IF function with 14 nested levels

D

Dan Fingerman

I have a nested IF function with 14 levels of nesting. Unfortunately,
Excel tells me the formula has an error when I give it more than 8
levels. I know my coding is correct because the function works as
expected when I enter only 8 levels (it works as expected whether I give
it the first 8 or the last 8).

Is there any way around that 8-level limitation? I am using Excel 2002 on
WinXP.

If there is no way around that 8-level limitation, perhaps someone can
suggest an alternate way to do what I need. I have 14 employees who bill
hourly. Column 5 contains a code for each employee, and column 6 contains
that employee's hourly rate. Other columns contain records for a
particular billing entry: a date, the employee code, and the number of
hours. I want a formula that will take the employee code in each record,
look up that employee's hourly rate, and multiply it by the number of
hours in that record.


My IF formula is copied below, in case my narrative explanation is
unclear. The cell RC[-1] contains the number of hours. The cell RC[-2]
contains the employee code. C5 is the list of all possible employee
codes, and C6 is the list of billing rates that correspond to each code.

The IF formula is:

=IF(ISBLANK(RC[-1]),"",RC[-1]*IF(RC[-2]=R02C5,R02C6,IF(RC[-2]=R03C5,R03C6,IF(RC[-2]=R04C5,R04C6,IF(RC[-2]=R05C5,R05C6,IF(RC[-2]=R06C5,R06C6,IF(RC[-2]=R07C5,R07C6,IF(RC[-2]=R08C5,R08C6,IF(RC[-2]=R09C5,R09C6,IF(RC[-2]=R10C5,R10C6,IF(RC[-2]=R11C5,R11C6,IF(RC[-2]=R12C5,R12C6,IF(RC[-2]=R13C5,R13C6,IF(RC[-2]=R14C5,R14C6,0))))))))))))))


That formula may be easier to read this way:

=IF(ISBLANK(RC[-1]),"",RC[-1]*
IF(RC[-2]=R02C5,R02C6,
IF(RC[-2]=R03C5,R03C6,
IF(RC[-2]=R04C5,R04C6,
IF(RC[-2]=R05C5,R05C6,
IF(RC[-2]=R06C5,R06C6,
IF(RC[-2]=R07C5,R07C6,
IF(RC[-2]=R08C5,R08C6,
IF(RC[-2]=R09C5,R09C6,
IF(RC[-2]=R10C5,R10C6,
IF(RC[-2]=R11C5,R11C6,
IF(RC[-2]=R12C5,R12C6,
IF(RC[-2]=R13C5,R13C6,
IF(RC[-2]=R14C5,R14C6,0))))))))))))))


Thanks in advance for any suggestions.
 
G

Gary's Student

You don't need any nesting or any IFs. You need a VLOOKUP() table. See
VLOOKUP in Excel help.
 
R

Ron Rosenfeld

I have a nested IF function with 14 levels of nesting. Unfortunately,
Excel tells me the formula has an error when I give it more than 8
levels. I know my coding is correct because the function works as
expected when I enter only 8 levels (it works as expected whether I give
it the first 8 or the last 8).

Is there any way around that 8-level limitation? I am using Excel 2002 on
WinXP.

If there is no way around that 8-level limitation, perhaps someone can
suggest an alternate way to do what I need. I have 14 employees who bill
hourly. Column 5 contains a code for each employee, and column 6 contains
that employee's hourly rate. Other columns contain records for a
particular billing entry: a date, the employee code, and the number of
hours. I want a formula that will take the employee code in each record,
look up that employee's hourly rate, and multiply it by the number of
hours in that record.


My IF formula is copied below, in case my narrative explanation is
unclear. The cell RC[-1] contains the number of hours. The cell RC[-2]
contains the employee code. C5 is the list of all possible employee
codes, and C6 is the list of billing rates that correspond to each code.

The IF formula is:

=IF(ISBLANK(RC[-1]),"",RC[-1]*IF(RC[-2]=R02C5,R02C6,IF(RC[-2]=R03C5,R03C6,IF(RC[-2]=R04C5,R04C6,IF(RC[-2]=R05C5,R05C6,IF(RC[-2]=R06C5,R06C6,IF(RC[-2]=R07C5,R07C6,IF(RC[-2]=R08C5,R08C6,IF(RC[-2]=R09C5,R09C6,IF(RC[-2]=R10C5,R10C6,IF(RC[-2]=R11C5,R11C6,IF(RC[-2]=R12C5,R12C6,IF(RC[-2]=R13C5,R13C6,IF(RC[-2]=R14C5,R14C6,0))))))))))))))


That formula may be easier to read this way:

=IF(ISBLANK(RC[-1]),"",RC[-1]*
IF(RC[-2]=R02C5,R02C6,
IF(RC[-2]=R03C5,R03C6,
IF(RC[-2]=R04C5,R04C6,
IF(RC[-2]=R05C5,R05C6,
IF(RC[-2]=R06C5,R06C6,
IF(RC[-2]=R07C5,R07C6,
IF(RC[-2]=R08C5,R08C6,
IF(RC[-2]=R09C5,R09C6,
IF(RC[-2]=R10C5,R10C6,
IF(RC[-2]=R11C5,R11C6,
IF(RC[-2]=R12C5,R12C6,
IF(RC[-2]=R13C5,R13C6,
IF(RC[-2]=R14C5,R14C6,0))))))))))))))


Thanks in advance for any suggestions.
Click to expand...


Not quite sure how your data is set up, but VLOOKUP is probably the worksheet
formula to use -- much less cumbersome than nested IF's; and more easily
expanded.


--ron
 
D

Dan Fingerman

You don't need any nesting or any IFs. You need a VLOOKUP()table. See
VLOOKUP in Excel help.
Click to expand...

Thank you -- this is exactly what I needed! That is much simpler than
nesting so many IFs.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

CONCATENATE with CHAR(10) IF NOT ISBLANK 6
Modify The Code For Importing Text File into an Excel File 5
Code with formula interfering in worksheet change event 2
Concatenate Formula Using Nested IF(ISBLANK) 3
CONCATENATE with CHAR(10) IF NOT ISBLANK 2
Copying Data Down the Column But Only Based on What's in Column A 3
Timeline Start date forward 1
Change to Worksheet Change Event 2

Top