Thanks a lot Pete, it's very useful !

"Pete_UK" a écrit dans le message de news:
...
An obvious way of shortening the formula is to get rid of all the
CONCATENATE( ) functions and replace them with the operator &. For
example, your last use of this:

CONCATENATE($A9;L$1;" Curncy")

can be written as:

$A9&L$1&" Curncy"

which saves about 13 characters each time you have used it. Do you want
to just try this throughout your formula to see if that makes it short
enough?

You do have some long names for the lookup tables which could also be
shortened, eg LEGACY_CURRENCY and EU_CURRENCY. Also, you have "PX_LAST"
appearing many times, and you could replace this with a named cell with
a shorter name - the same applies to the string " Curncy".

Hope this helps.

Pete

Nader wrote:
that's my formula :

=IF($A9<L$1;IF(OR(NOT(ISNA(MATCH(L$1;LEGACY_CURRE NCY;0)));NOT(ISNA(MATCH($A9;LEGACY_CURRENCY;0)))); IF(OR(L$1="EUR";$A9="EUR");IF(NOT(ISNA(MATCH(L$1;L EGACY_CURRENCY;0)));
VLOOKUP(L$1;EU_CURRENCY;2;FALSE); 1/VLOOKUP(L$1;EU_CURRENCY;2;FALSE));
IF(NOT(ISNA(MATCH(L$1;LEGACY_CURRENCY;0)));VLOOKUP (L$1;EU_CURRENCY;2;FALSE);IF(ISNUMBER(BLP(CONCATEN ATE("EUR";L$1;"
Curncy");"PX_LAST"));BLP(CONCATENATE("EUR";L$1;" Curncy");"PX_LAST");
BLP(CONCATENATE("USD";L$1;" Curncy");"PX_LAST") ))
/IF(NOT(ISNA(MATCH($A9;LEGACY_CURRENCY;0)));IF(ISNU MBER(BLP(CONCATENATE("EUR";L$1;"
Curncy");"PX_LAST"));VLOOKUP($A9;EU_CURRENCY;2;FAL SE);
VLOOKUP($A9;EU_CURRENCY;2;FALSE) / BLP("EURUSD Curncy";"PX_LAST") );
IF(ISNUMBER(BLP(CONCATENATE("EUR";$A9;"
Curncy");"PX_LAST"));BLP(CONCATENATE("EUR";$A9;"
Curncy");"PX_LAST");BLP(CONCATENATE("USD";$A9;" Curncy");"PX_LAST")))
);IF(NOT(ISNUMBER(BLP(CONCATENATE($A9;L$1;"
Curncy");"PX_LAST")));BLP(CONCATENATE(L$1;"
Curncy");"PX_LAST")/BLP(CONCATENATE($A9;"
Curncy");"PX_LAST");BLP(CONCATENATE($A9;L$1;" Curncy");"PX_LAST")));1)

PS : BLP is a bloomberg function which return an exchange rate ! Also,
this
formula should not be split.

Thanks for you help Pete!

"Pete_UK" a écrit dans le message de news:
...
Post a copy of your formula here, so that we can see if it can be
shortened - if you use long sheet names these can always be shortened.

Pete

Nader wrote:
Hello,

I'm having a little problem, recently I wrote a formula for excel which
is
too long so I have to decide between trying to find away too shorten
that
formula (i'm not sure if it's possible) or written some of the formula
code
in vba ?

However, I read in different website that If I wrote some code in vba
it
will not be as efficient as a formula.

What should I do ? Shorten the formula or Vba code ?

PS : My data are consentenly updated because they are exchange rates
(almost
every second)

Thank you all in advance.

Nader