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.

--
DTM :<|
www.danfingerman.com