MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Argument Limits


Posted by SChase on December 27, 2001 1:18 PM

I know Excel 97 limits to what, 7 or 9 arguments in a formula. Does anybody know a patch or method around that?


Posted by Juan Pablo G. on December 27, 2001 1:20 PM

There's almost always a better, more efficient, way of calculating the formula, i never go pass 5 levels.

What are you trying to do ?

And no, there's no patch. 7 is the limit.

Juan Pablo G.

Posted by SChase on December 27, 2001 1:33 PM

a bunch of IF statements. If such a cell contains like 1 or 2 or 3 or 4 or 5 do this, if it contains 12345 do this, or 1 & 3, or 1 & 4, 2&5, and so on.

basically an argument for any possible combination of 1,2,3,4,5


Posted by Juan Pablo G. on December 28, 2001 6:34 AM

Usually the Nested If problem can be solved with VLOOKUP. Try this, create a new table where in first column you have the combinations of 12345, and in the second column the "answer", something like:

{Comb,Ans;12345,"Ans1";1234,"Ans2";123,"Ans3"}

and so on.

Then, in another formula put a VLOOKUP

=VLOOKUP(cell_tested,this_table,2,0)

Juan Pablo G.


Posted by SChase on December 28, 2001 4:59 PM

Hmmm, not sure if it will work or not. Little layout information on how it is set up.

Sheet one (Info) is where our person inputs any data. The week 1,2,3,4,5 is for the specific week. Sheets 2,3,4,5,6 are the individual weeks. It then matches say week 2, and pulls any week 2 information from the info sheet. The last sheet pulls all info.

I'm wondering if the VLookup will suffice for more than one column......