Nesting Functions with more than 7 arguements

T

tammyj

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)))))))))))))
 
R

Ron Coderre

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Repeat formula within a cell 5
nesting more than 7 functions? 11
Problem with VLOOKUP when i try to look for more than 1 criteria 2
Complicated Formula Problem 1
Nesting excel greater than 7 arguments 9
if function (if more that 7 nesting) 2
More than 7 If Statements 1
Nesting of more than 7 in an IF formula 8

Top