hello all,

I am trying to create a worksheet that will reference a size (of bays for my company) and when i use the if function i can get some of it to work but i keep runing into problems with too many nested IF functions. i am trying to reference the number of bays in one cell to return a result to another cell from a named table. i am getting it to work but only halfway.

this is the formula i have so far

=IF(B510,"Too many Longitudinal Bays",IF(B116,"Lateral Too Big",INDEX(LateralSupportNames,IF(B5=10,8,IF(B5=9, 7,IF(B5=8,6,IF(B5=7,5,IF(B5=6,4))))),IF(B11<=4,2,I F(B11<=6,3,IF(B116,"Lateral too big",#VALUE!))))))

if i add another IF funciton it return error "you've entered too many arguments for this function"

I am trying to go from 10 bays in B5 down to 3 bays and only can get to 6 then i have the error.

If anyone can help i would greatly appreciate it. i am so frustrated and have looked into other formulas but dont know too much about them.

Hi,

Am Mon, 30 Dec 2013 07:11:40 -0800 (PST) schrieb
:

=IF(B510,"Too many Longitudinal Bays",IF(B116,"Lateral Too Big",INDEX(LateralSupportNames,IF(B5=10,8,IF(B5=9, 7,IF(B5=8,6,IF(B5=7,5,IF(B5=6,4))))),IF(B11<=4,2,I F(B11<=6,3,IF(B116,"Lateral too big",#VALUE!))))))

if i add another IF funciton it return error "you've entered too many arguments for this function"

I am trying to go from 10 bays in B5 down to 3 bays and only can get to 6 then i have the error.

you can summarize the IF-statements. Here for B5 (for B11 and/or others
you can enlarge the formula):
=IF(B510,"Too many Longitudinal Bays",IF(B116,"Lateral Too Big",IF(AND(B5<=10,B5=3),INDEX(LateralSupportName s,B5-2),"")))


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Mon, 30 Dec 2013 07:11:40 -0800 (PST), wrote:

hello all,

I am trying to create a worksheet that will reference a size (of bays for my company) and when i use the if function i can get some of it to work but i keep runing into problems with too many nested IF functions. i am trying to reference the number of bays in one cell to return a result to another cell from a named table. i am getting it to work but only halfway.

this is the formula i have so far

=IF(B510,"Too many Longitudinal Bays",IF(B116,"Lateral Too Big",INDEX(LateralSupportNames,IF(B5=10,8,IF(B5=9, 7,IF(B5=8,6,IF(B5=7,5,IF(B5=6,4))))),IF(B11<=4,2,I F(B11<=6,3,IF(B116,"Lateral too big",#VALUE!))))))

if i add another IF funciton it return error "you've entered too many arguments for this function"

I am trying to go from 10 bays in B5 down to 3 bays and only can get to 6 then i have the error.

If anyone can help i would greatly appreciate it. i am so frustrated and have looked into other formulas but dont know too much about them.

You've got some redundancies in your formula (e.g. you test for B116 twice), which should be taken care of anyway, but with regard to your nested IF's, to obtain the column and row numbers, I would replace them with a LOOKUP.

For example, your IF's to determine the ROW could be replaced with:

VLOOKUP(B5,{10,8;9,7;8,6;7,5;6,4},2,FALSE)

and you can add other pairs to that array constant as needed.

Same with the columns:

VLOOKUP(B11,{4,2;6,3},2,FALSE)