XL 2K - Is there a limit on how many func. can be used in a formula

[CEN7272]

Lately I've been running in to a problem where seperate parts of a formula work but when I try to put them together I get error messages...WHY!!!

-Clay
claysdays.blogspot.com

 

[dhulbert]

What error message are you getting. there is a limit of 256 characters in a formula string including file apts etc but unless you tell us what error message you get noone can tell you why.

 

[MJPPaba]

As Dhulbert states there is a limit of 256chars, however if you are going to use lengthy formulae I advise the use of user defined functions, i.e. functions written in VB and referenced using a recognisable name.

Also I would like to reiterate the above question, what error message, this will help a great deal.



crazy times call for crazy people


CrazyPabs


sometimes I wonder why I even bova...

 

[Zathras]

My crystal ball is a little dark today, but perhaps you are trying to nest functions more than 7 levels deep?

 

[MJPPaba]

Are you sure that you don't need to install an addin, e.g. Analysis tool pack for any special formulae?

crazy times call for crazy people


CrazyPabs


sometimes I wonder why I even bova...

 

[xlbo]

Please post the formula......

MJPPaba - the limit is actually 1024 characters and I would question the wisdom of using UDFs unless absolutely necessary - they are far slower to calculate than "native" formulae and can also cause problems by not recalculating when you would expect them to...

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[KenWright]

There is no limit other than the character limit wrt how many functions you can include, BUT, there is a limit as to how many functions you can NEST within a formula, and that is seven.

Typing the word 'specifications' into help will give you all the standard limits.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[CEN7272]

I'm glad to know about the 7 levels of nesting functions...I hadn't realized that before. It looks like that's exactly what the problem was.

Here's the function I was using to get the last for characters of the sheet name: (it is in K6)

=RIGHT(MID(CELL("filename"),FIND("]",CELL("filename"))+1,255),4)

Then I wanted to lookup the result in a chart in another book.

=VLOOKUP(K6,'[Frontlift Tonnage.xls]SEPT WK3'!$A$2:$H$69,2,FALSE)

The first formula works fine and the second one only works if I manually type the 4 digits into K6...it won't work if it has to reference to K6. So even though each formula seperately has less than 7 functions it must be that the combined number of functions has to be under 7...is that correct or did I miss something

-Clay
claysdays.blogspot.com

 

[MJPPaba]

Instead of doing Find [ why not do the search for "'!" and minus 4 from the returned value? this will save 1 level of the nested formula...

=MID(CELL("filename"),SEARCH(".xls",CELL("filename"))-4,4)


?

crazy times call for crazy people


CrazyPabs


sometimes I wonder why I even bova...