ExcelBanter - View Single Post

Ron Rosenfeld[_2_]

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)