IF function with 14 nested levels

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

You don't need any nesting or any IFs. You need a VLOOKUP() table. See
VLOOKUP in Excel help.
--
Gary's Student


"Dan Fingerman" wrote:

> 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
>

On Sat, 03 Sep 2005 16:57:22 -0700, "Dan Fingerman"
<[email protected]> wrote:

>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.


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

On Sat, 03 Sep 2005 17:36:01 -0700, Gary's Student""
<[email protected]> wrote:

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

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

--
DTM :<|
www.danfingerman.com