G
Guest
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?
too long" in Excel? Is there any way to extend this limit?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
B
Bob Phillips
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)
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)
R
Rawxl1
If you can, split up formula in 2 cells and combine the result in
another cell of possible.
Good luck
another cell of possible.
Good luck
G
Guest
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
do. Post your faulty formula and you'll get plenty of help
G
Guest
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:
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:
P
Pete_UK
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
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
B
Bob Phillips
What I mean is say we have this formula
=IF(ISNA(VLOOKUP($A$1,$M$1:$O$20,2,FALSE)),"",VLOOKUP($A$1,$M$1:$O$20,2,FALS
E))
as a fairly trivial example. You could create an Excel name
(Insert>Name>Define...) 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)
=IF(ISNA(VLOOKUP($A$1,$M$1:$O$20,2,FALSE)),"",VLOOKUP($A$1,$M$1:$O$20,2,FALS
E))
as a fairly trivial example. You could create an Excel name
(Insert>Name>Define...) 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)
G
Guest
Thanks Bob for your thorough explanation, this facility is very useful and
was really new for me!
Regards,
Stefi
„Bob Phillips†ezt Ãrta:
was really new for me!
Regards,
Stefi
„Bob Phillips†ezt Ãrta: