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))))))))))))) |
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))))))))))))) |
All times are GMT +1. The time now is 09:32 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com