How can I create large formulas without getting the error message "formula is
too long" in Excel? Is there any way to extend this limit?

You have to break it down, either put part of the formula in another cell
and reference that in the final formula, or create a name for the partial
formula, and use that.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jakob" wrote in message
...
How can I create large formulas without getting the error message "formula
is
too long" in Excel? Is there any way to extend this limit?

Hi Bob,
Do you mean creating a name for the cell containing the partial formula and
use this name in the final formula, or is there another way which is new for
me?

Regards,
Stefi


€žBob Phillips€� ezt Ã*rta:

You have to break it down, either put part of the formula in another cell
and reference that in the final formula, or create a name for the partial
formula, and use that.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jakob" wrote in message
...
How can I create large formulas without getting the error message "formula
is
too long" in Excel? Is there any way to extend this limit?

You could try this link:

http://www.cpearson.com/excel/nested.htm

which explains how you can overcome the limit of nested functions by
making use of named formulae.

Hope this helps.

Pete

What I mean is say we have this formula

=IF(ISNA(VLOOKUP($A$1,$M$1:$O$20,2,FALSE)),"",VLOO KUP($A$1,$M$1:$O$20,2,FALS
E))

as a fairly trivial example. You could create an Excel name
(InsertNameDefine...) with a name of say lookup_rate and a Refersto value
of =VLOOKUP($A$1,$M$1:$O$20,2,FALSE), and then you can use the in-cell
formula of

=IF(ISNA(lookup_rate),"",lookup_rate)

which helps with 7 nested functions, and makes it more readable.

As a real-world example, I created this formula to extract leading or
trailing numbers from a mixed number/text cell value

=IF(ISNUMBER(1*LEFT(A1,1)),MID(A1,MIN(IF(ISERROR(1 *(MID(A1,ROW(INDIRECT("A1:
A"&LEN(A1))),1))),
255,ROW(INDIRECT("A1:A"&LEN(A1))))),99),"wrong")

and it threw a wobbly on the LEN. I created an Excel name of pos_array with
a value of =ROW(INDIRECT("A1:A"&LEN(A1))) and then used a formula of

=IF(ISNUMBER(1*LEFT(A1,1)),MID(A1,MIN(IF(ISERROR(1 *(MID(A1,pos_array,1))),25
5,pos_array)),99),"wrong")

which worked fine.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Stefi" wrote in message
...
Hi Bob,
Do you mean creating a name for the cell containing the partial formula
and
use this name in the final formula, or is there another way which is new
for
me?

Regards,
Stefi


"Bob Phillips" ezt írta:

You have to break it down, either put part of the formula in another
cell
and reference that in the final formula, or create a name for the
partial
formula, and use that.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jakob" wrote in message
...
How can I create large formulas without getting the error message
"formula
is
too long" in Excel? Is there any way to extend this limit?

Thanks Bob for your thorough explanation, this facility is very useful and
was really new for me!

Regards,
Stefi


€žBob Phillips€� ezt Ã*rta:

What I mean is say we have this formula

=IF(ISNA(VLOOKUP($A$1,$M$1:$O$20,2,FALSE)),"",VLOO KUP($A$1,$M$1:$O$20,2,FALS
E))

as a fairly trivial example. You could create an Excel name
(InsertNameDefine...) with a name of say lookup_rate and a Refersto value
of =VLOOKUP($A$1,$M$1:$O$20,2,FALSE), and then you can use the in-cell
formula of

=IF(ISNA(lookup_rate),"",lookup_rate)

which helps with 7 nested functions, and makes it more readable.

As a real-world example, I created this formula to extract leading or
trailing numbers from a mixed number/text cell value

=IF(ISNUMBER(1*LEFT(A1,1)),MID(A1,MIN(IF(ISERROR(1 *(MID(A1,ROW(INDIRECT("A1:
A"&LEN(A1))),1))),
255,ROW(INDIRECT("A1:A"&LEN(A1))))),99),"wrong")

and it threw a wobbly on the LEN. I created an Excel name of pos_array with
a value of =ROW(INDIRECT("A1:A"&LEN(A1))) and then used a formula of

=IF(ISNUMBER(1*LEFT(A1,1)),MID(A1,MIN(IF(ISERROR(1 *(MID(A1,pos_array,1))),25
5,pos_array)),99),"wrong")

which worked fine.


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Stefi" wrote in message
...
Hi Bob,
Do you mean creating a name for the cell containing the partial formula
and
use this name in the final formula, or is there another way which is new
for
me?

Regards,
Stefi


"Bob Phillips" ezt Ã*rta:

You have to break it down, either put part of the formula in another
cell
and reference that in the final formula, or create a name for the
partial
formula, and use that.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Jakob" wrote in message
...
How can I create large formulas without getting the error message
"formula
is
too long" in Excel? Is there any way to extend this limit?

No, you can't extend the limit, but there are several clever things you can
do. Post your faulty formula and you'll get plenty of help

"Jakob" wrote:

How can I create large formulas without getting the error message "formula is
too long" in Excel? Is there any way to extend this limit?

If you can, split up formula in 2 cells and combine the result in

another cell of possible.

Good luck


--
Rawxl1
------------------------------------------------------------------------
Rawxl1's Profile: http://www.excelforum.com/member.php...o&userid=30673
View this thread: http://www.excelforum.com/showthread...hreadid=520071