Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting Functions with more than 7 arguements
Help! The current formula is listed below and works just fine. However, I
have altered our worksheet and now have 15 options instead of just 7. Where do I go from here if I can't nest any further than this. I've used VLOOKUP in the past but can't seem to get a grasp on how to add the formula into the result. =IF(D11=0,0,IF('Area Specs.'!L11=1,(D11*'Staffing & Pricing'!$F$65),(IF('Area Specs.'!L11=2,D11*'Staffing & Pricing'!$G$65,(IF('Area Specs.'!L11=3,D11*'Staffing & Pricing'!$H$65,(IF('Area Specs.'!L11=4,D11*'Staffing & Pricing'!$I$65,IF('Area Specs.'!L11=5,D11*'Staffing & Pricing'!$J$65,(IF('Area Specs.'!L11=6,D11*'Staffing & Pricing'!$K$65,(IF('Area Specs.'!L11=7,D11*'Staffing & Pricing'!$L$65,'Bid Schedule'!F11))))))))))))) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Nesting Functions with more than 7 arguements
Since the Staffing & Pricing references seem to vary directly with the value
of L11, maybe you could try something like this, instead: =IF(D11=0,0,IF(SUM(COUNTIF('Staffing & Pricing'!L11,{1,2,3,4,5,6,7})),D11*INDEX('Staffing & Pricing'!$F$65:$L$65,'Staffing & Pricing'!L11),'Bid Schedule'!F11)) To increase the number of options, just add values to this: {1,2,3,4,5,6,7} and change this reference: $F$65:$L$65 Is that something you can work with? *********** Regards, Ron XL2002, WinXP "tammyj" wrote: Help! The current formula is listed below and works just fine. However, I have altered our worksheet and now have 15 options instead of just 7. Where do I go from here if I can't nest any further than this. I've used VLOOKUP in the past but can't seem to get a grasp on how to add the formula into the result. =IF(D11=0,0,IF('Area Specs.'!L11=1,(D11*'Staffing & Pricing'!$F$65),(IF('Area Specs.'!L11=2,D11*'Staffing & Pricing'!$G$65,(IF('Area Specs.'!L11=3,D11*'Staffing & Pricing'!$H$65,(IF('Area Specs.'!L11=4,D11*'Staffing & Pricing'!$I$65,IF('Area Specs.'!L11=5,D11*'Staffing & Pricing'!$J$65,(IF('Area Specs.'!L11=6,D11*'Staffing & Pricing'!$K$65,(IF('Area Specs.'!L11=7,D11*'Staffing & Pricing'!$L$65,'Bid Schedule'!F11))))))))))))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
nesting issue in functions | Excel Worksheet Functions | |||
How do u emulate nesting seven IF functions in a cell? | Excel Worksheet Functions | |||
Nesting functions in the functions dialog box | Excel Worksheet Functions | |||
What is the syntax for nesting functions such as IF AND? | Excel Worksheet Functions | |||
nesting 18 x functions | Excel Worksheet Functions |