|
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"
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""
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 |
| All times are GMT +1. The time now is 10:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com