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?