formula or vba code

N

Nader

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
 
P

Pete_UK

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
 
B

Bob Phillips

Normally with long formulae, you can break them down by putting one part in
a separate cell and getting an interim result, and use that interim result
within the next part. This can be done very effectively to get to the final
result.

For instance,

B1: =IF(ISNA(VLOOKUP(A1,M1:p10,2,False)),"",VLOOKUP(A1,M1:p10,2,False))

Change this to

B1: =IF(ISNA(C1),"",C1)
C1: =VLOOKUP(A1,M1:p10,2,False)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
N

Nader

I can't break the formula.

Bob Phillips said:
Normally with long formulae, you can break them down by putting one part
in
a separate cell and getting an interim result, and use that interim result
within the next part. This can be done very effectively to get to the
final
result.

For instance,

B1: =IF(ISNA(VLOOKUP(A1,M1:p10,2,False)),"",VLOOKUP(A1,M1:p10,2,False))

Change this to

B1: =IF(ISNA(C1),"",C1)
C1: =VLOOKUP(A1,M1:p10,2,False)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
Click to expand...
 
N

Nader

that's my formula :

=IF($A9<>L$1;IF(OR(NOT(ISNA(MATCH(L$1;LEGACY_CURRENCY;0)));NOT(ISNA(MATCH($A9;LEGACY_CURRENCY;0))));IF(OR(L$1="EUR";$A9="EUR");IF(NOT(ISNA(MATCH(L$1;LEGACY_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(CONCATENATE("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(ISNUMBER(BLP(CONCATENATE("EUR";L$1;"
Curncy");"PX_LAST"));VLOOKUP($A9;EU_CURRENCY;2;FALSE);
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!
 
B

Bob Phillips

I've seen your formula, and you could easily.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
P

Pete_UK

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
 
N

Nader

Thanks a lot Pete, it's very useful !

"Pete_UK" <[email protected]> a écrit dans le message de [email protected]...
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
 
B

Bob Phillips

Can you humour me and tell me why not? By doing so, your formula would be
much more manageable.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)
 
N

Nader

Because this formula calculate an exchange rate between two currency by
getting data from bloomberg and I use that formula in a matrix 180 by 180.


CHF ¦ GBP ¦ USD | SEK ¦ LIR | EUR ¦ GIP | HKD | ATS |

x

Thanks Bob
 
N

Nader

Because this formula calculate an exchange rate between two currency by
getting data from bloomberg and I use that formula in a matrix 180 by 180.

Something like that ... and I almost have not much space left.

¦ CHF ¦ GBP ¦ USD | SEK ¦ LIR | EUR ¦ GIP |
CHF
-----
GBP
-----
USD
-----
SEK
-----
LIR
-----
EUR
-----
GIP



Thanks Bob
 
N

Nader

Because this formula calculate an exchange rate between two currency by
getting data from bloomberg and I use that formula in a matrix 180 by 180.

Something like that ... and I almost have not much space left.

¦ CHF ¦ GBP ¦ USD | SEK ¦ LIR | EUR ¦ GIP |
CHF
-----
GBP
-----
USD
-----
SEK
-----
LIR
-----
EUR
-----
GIP



Thanks Bob
 
P

Pete_UK

Well, that's telling him anyway ! <bg>

Hope you were able to shorten your formula sufficiently.

Pete
 
Top