| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
PLEASE HELP!! # of Nested If's - Have 7 but Excel Still Won't Acce
Can someone look at this and see why Excel won't accept this formula? Says
it contains an error and hilites the 2nd to last 'match' in the formula. (Note: Range names are used throughout, and they are all valid.) =IF(O3<"","", IF(L3=9,AT3, IF(L3=10,OFFSET(SMBBASE,MATCH(G3,SMBPROD,0),MATCH( L3,SMBSL,0)), IF(L3<5,OFFSET(SNTBASE,MATCH(G3,SNTPROD,0),MATCH(L 3,SNTSL,0)), IF(L3<9,OFFSET(SNTOSBASE,MATCH(G3,SNTOSPROD,0),MAT CH(L3,SNTOSSL,0)), IF(L3<15,OFFSET(IPSBASE,MATCH(G3,IPSPROD,0),MATCH( L3,IPSSL,0)), IF(L3<19,OFFSET(IPSOSBASE,MATCH(G3,IPSOSPROD,0),MA TCH(L3,IPSOSSL,0)),""))))))) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PLEASE HELP!! # of Nested If's - Have 7 but Excel Still Won't Acce
You can only use seven levels of nested functions in Excel.
-- Regards, Luc. "Festina Lente" "Paige" wrote: Can someone look at this and see why Excel won't accept this formula? Says it contains an error and hilites the 2nd to last 'match' in the formula. (Note: Range names are used throughout, and they are all valid.) =IF(O3<"","", IF(L3=9,AT3, IF(L3=10,OFFSET(SMBBASE,MATCH(G3,SMBPROD,0),MATCH( L3,SMBSL,0)), IF(L3<5,OFFSET(SNTBASE,MATCH(G3,SNTPROD,0),MATCH(L 3,SNTSL,0)), IF(L3<9,OFFSET(SNTOSBASE,MATCH(G3,SNTOSPROD,0),MAT CH(L3,SNTOSSL,0)), IF(L3<15,OFFSET(IPSBASE,MATCH(G3,IPSPROD,0),MATCH( L3,IPSSL,0)), IF(L3<19,OFFSET(IPSOSBASE,MATCH(G3,IPSOSPROD,0),MA TCH(L3,IPSOSSL,0)),""))))))) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PLEASE HELP!! # of Nested If's - Have 7 but Excel Still Won't Acce
Use VLOOKUP function instead
"Paige" wrote: Can someone look at this and see why Excel won't accept this formula? Says it contains an error and hilites the 2nd to last 'match' in the formula. (Note: Range names are used throughout, and they are all valid.) =IF(O3<"","", IF(L3=9,AT3, IF(L3=10,OFFSET(SMBBASE,MATCH(G3,SMBPROD,0),MATCH( L3,SMBSL,0)), IF(L3<5,OFFSET(SNTBASE,MATCH(G3,SNTPROD,0),MATCH(L 3,SNTSL,0)), IF(L3<9,OFFSET(SNTOSBASE,MATCH(G3,SNTOSPROD,0),MAT CH(L3,SNTOSSL,0)), IF(L3<15,OFFSET(IPSBASE,MATCH(G3,IPSPROD,0),MATCH( L3,IPSSL,0)), IF(L3<19,OFFSET(IPSOSBASE,MATCH(G3,IPSOSPROD,0),MA TCH(L3,IPSOSSL,0)),""))))))) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PLEASE HELP!! # of Nested If's - Have 7 but Excel Still Won't
Maybe that's why I'm confused; I only used 7 'if' statements, isn't that what
they are talking about re the 7 limit? "Teethless mama" wrote: Use VLOOKUP function instead "Paige" wrote: Can someone look at this and see why Excel won't accept this formula? Says it contains an error and hilites the 2nd to last 'match' in the formula. (Note: Range names are used throughout, and they are all valid.) =IF(O3<"","", IF(L3=9,AT3, IF(L3=10,OFFSET(SMBBASE,MATCH(G3,SMBPROD,0),MATCH( L3,SMBSL,0)), IF(L3<5,OFFSET(SNTBASE,MATCH(G3,SNTPROD,0),MATCH(L 3,SNTSL,0)), IF(L3<9,OFFSET(SNTOSBASE,MATCH(G3,SNTOSPROD,0),MAT CH(L3,SNTOSSL,0)), IF(L3<15,OFFSET(IPSBASE,MATCH(G3,IPSPROD,0),MATCH( L3,IPSSL,0)), IF(L3<19,OFFSET(IPSOSBASE,MATCH(G3,IPSOSPROD,0),MA TCH(L3,IPSOSSL,0)),""))))))) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PLEASE HELP!! # of Nested If's - Have 7 but Excel Still Won't
Your OFFSET(...MATCH(..)) adds two more levels.
"Paige" wrote: Maybe that's why I'm confused; I only used 7 'if' statements, isn't that what they are talking about re the 7 limit? "Teethless mama" wrote: Use VLOOKUP function instead "Paige" wrote: Can someone look at this and see why Excel won't accept this formula? Says it contains an error and hilites the 2nd to last 'match' in the formula. (Note: Range names are used throughout, and they are all valid.) =IF(O3<"","", IF(L3=9,AT3, IF(L3=10,OFFSET(SMBBASE,MATCH(G3,SMBPROD,0),MATCH( L3,SMBSL,0)), IF(L3<5,OFFSET(SNTBASE,MATCH(G3,SNTPROD,0),MATCH(L 3,SNTSL,0)), IF(L3<9,OFFSET(SNTOSBASE,MATCH(G3,SNTOSPROD,0),MAT CH(L3,SNTOSSL,0)), IF(L3<15,OFFSET(IPSBASE,MATCH(G3,IPSPROD,0),MATCH( L3,IPSSL,0)), IF(L3<19,OFFSET(IPSOSBASE,MATCH(G3,IPSOSPROD,0),MA TCH(L3,IPSOSSL,0)),""))))))) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
PLEASE HELP!! # of Nested If's - Have 7 but Excel Still Won't
Thanks, did not realize that. Thank you all for your help; have a great day!
"JMB" wrote: Your OFFSET(...MATCH(..)) adds two more levels. "Paige" wrote: Maybe that's why I'm confused; I only used 7 'if' statements, isn't that what they are talking about re the 7 limit? "Teethless mama" wrote: Use VLOOKUP function instead "Paige" wrote: Can someone look at this and see why Excel won't accept this formula? Says it contains an error and hilites the 2nd to last 'match' in the formula. (Note: Range names are used throughout, and they are all valid.) =IF(O3<"","", IF(L3=9,AT3, IF(L3=10,OFFSET(SMBBASE,MATCH(G3,SMBPROD,0),MATCH( L3,SMBSL,0)), IF(L3<5,OFFSET(SNTBASE,MATCH(G3,SNTPROD,0),MATCH(L 3,SNTSL,0)), IF(L3<9,OFFSET(SNTOSBASE,MATCH(G3,SNTOSPROD,0),MAT CH(L3,SNTOSSL,0)), IF(L3<15,OFFSET(IPSBASE,MATCH(G3,IPSPROD,0),MATCH( L3,IPSSL,0)), IF(L3<19,OFFSET(IPSOSBASE,MATCH(G3,IPSOSPROD,0),MA TCH(L3,IPSOSSL,0)),""))))))) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| unhide menu bar in excel - just disappeared | Setting up and Configuration of Excel | |||
| nested if functions in Excel 2002 | Excel Worksheet Functions | |||
| Allow more than 7 nested IF THEN tests in EXCEL. Allow 30 or more | Excel Worksheet Functions | |||
| Do nested subtotals have an errror in Excel 2003 | Excel Discussion (Misc queries) | |||
| Excel nested IF formula question | Excel Discussion (Misc queries) | |||
Linear Mode
