| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Too many IF functions
I know there is a limit on how many nested IF functions a formula can have,
but is there a "work-around" for this? Here is my example: =IF(D33<='Leave tracker FY10'!C4,'Leave tracker FY10'!C5,IF(D33<='Leave tracker FY10'!D4,'Leave tracker FY10'!D5,IF('Leave Map'!D33<='Leave tracker FY10'!E4,'Leave tracker FY10'!E5,IF(D33<='Leave tracker FY10'!F4,'Leave tracker FY10'!F5,IF(D33<='Leave tracker FY10'!G4,'Leave tracker FY10'!G5,IF(D33<='Leave tracker FY10'!H4,'Leave tracker FY10'!H5,IF(D33<='Leave tracker FY10'!I4,'Leave tracker FY10'!I5,IF(D33<='Leave tracker FY10'!J4,'Leave tracker FY10'!J5,)))))))) But I need to add in the last 3 months. Looking to compare todays date (D33) to a specific date in each month ('Leave tracker FY10'!C4) to reveal the amount of vacation time(tracker FY10'!C5) someone has. The above mess works for the first 9 months, but to add in the last 3 months it fails, saying I have too many nested IF's. Not sure I can use VLOOKUP as the date falls into a range. I'm using Excel 2007. -- G Hall |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too many IF functions
Based on the logic of your formula something like this should work...
Array entered** : =INDEX('Leave tracker FY10'!C5:N5,MATCH(TRUE,D33<='Leave tracker FY10'!C4:N4,0)) ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER. -- Biff Microsoft Excel MVP "GHall" wrote in message ... I know there is a limit on how many nested IF functions a formula can have, but is there a "work-around" for this? Here is my example: =IF(D33<='Leave tracker FY10'!C4,'Leave tracker FY10'!C5,IF(D33<='Leave tracker FY10'!D4,'Leave tracker FY10'!D5,IF('Leave Map'!D33<='Leave tracker FY10'!E4,'Leave tracker FY10'!E5,IF(D33<='Leave tracker FY10'!F4,'Leave tracker FY10'!F5,IF(D33<='Leave tracker FY10'!G4,'Leave tracker FY10'!G5,IF(D33<='Leave tracker FY10'!H4,'Leave tracker FY10'!H5,IF(D33<='Leave tracker FY10'!I4,'Leave tracker FY10'!I5,IF(D33<='Leave tracker FY10'!J4,'Leave tracker FY10'!J5,)))))))) But I need to add in the last 3 months. Looking to compare todays date (D33) to a specific date in each month ('Leave tracker FY10'!C4) to reveal the amount of vacation time(tracker FY10'!C5) someone has. The above mess works for the first 9 months, but to add in the last 3 months it fails, saying I have too many nested IF's. Not sure I can use VLOOKUP as the date falls into a range. I'm using Excel 2007. -- G Hall |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too many IF functions
Thanks Barry and T. Between the two of you, and a little tweaking, it works
great! -- Gary Hall "GHall" wrote: I know there is a limit on how many nested IF functions a formula can have, but is there a "work-around" for this? Here is my example: =IF(D33<='Leave tracker FY10'!C4,'Leave tracker FY10'!C5,IF(D33<='Leave tracker FY10'!D4,'Leave tracker FY10'!D5,IF('Leave Map'!D33<='Leave tracker FY10'!E4,'Leave tracker FY10'!E5,IF(D33<='Leave tracker FY10'!F4,'Leave tracker FY10'!F5,IF(D33<='Leave tracker FY10'!G4,'Leave tracker FY10'!G5,IF(D33<='Leave tracker FY10'!H4,'Leave tracker FY10'!H5,IF(D33<='Leave tracker FY10'!I4,'Leave tracker FY10'!I5,IF(D33<='Leave tracker FY10'!J4,'Leave tracker FY10'!J5,)))))))) But I need to add in the last 3 months. Looking to compare todays date (D33) to a specific date in each month ('Leave tracker FY10'!C4) to reveal the amount of vacation time(tracker FY10'!C5) someone has. The above mess works for the first 9 months, but to add in the last 3 months it fails, saying I have too many nested IF's. Not sure I can use VLOOKUP as the date falls into a range. I'm using Excel 2007. -- G Hall |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Too many IF functions
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "GHall" wrote in message ... Thanks Barry and T. Between the two of you, and a little tweaking, it works great! -- Gary Hall "GHall" wrote: I know there is a limit on how many nested IF functions a formula can have, but is there a "work-around" for this? Here is my example: =IF(D33<='Leave tracker FY10'!C4,'Leave tracker FY10'!C5,IF(D33<='Leave tracker FY10'!D4,'Leave tracker FY10'!D5,IF('Leave Map'!D33<='Leave tracker FY10'!E4,'Leave tracker FY10'!E5,IF(D33<='Leave tracker FY10'!F4,'Leave tracker FY10'!F5,IF(D33<='Leave tracker FY10'!G4,'Leave tracker FY10'!G5,IF(D33<='Leave tracker FY10'!H4,'Leave tracker FY10'!H5,IF(D33<='Leave tracker FY10'!I4,'Leave tracker FY10'!I5,IF(D33<='Leave tracker FY10'!J4,'Leave tracker FY10'!J5,)))))))) But I need to add in the last 3 months. Looking to compare todays date (D33) to a specific date in each month ('Leave tracker FY10'!C4) to reveal the amount of vacation time(tracker FY10'!C5) someone has. The above mess works for the first 9 months, but to add in the last 3 months it fails, saying I have too many nested IF's. Not sure I can use VLOOKUP as the date falls into a range. I'm using Excel 2007. -- G Hall |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Multiple functions, conditional functions | Excel Worksheet Functions | |||
| How to convert cell formula functions to code functions | Excel Discussion (Misc queries) | |||
| formula/functions for average and if functions | Excel Worksheet Functions | |||
| efficiency: database functions vs. math functions vs. array formula | Excel Discussion (Misc queries) | |||
| Looking for a site with functions that substitute the ATP functions | Excel Worksheet Functions | |||
Linear Mode
