Thread: formula or vba code

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

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

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)

"Nader" <nader@infomaniak.ch> wrote in message
news:44e1b9fa$0$7963$5402220f@news.sunrise.ch...
> 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
>
>

I can't break the formula.

"Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de news:
%235iMUoGwGHA.3264@TK2MSFTNGP03.phx.gbl...
> 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)
>
> "Nader" <nader@infomaniak.ch> wrote in message
> news:44e1b9fa$0$7963$5402220f@news.sunrise.ch...
>> 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
>>
>>
>
>

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(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(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!

"Pete_UK" <pashurst@auditel.net> a écrit dans le message de news:
1155644557.353544.299060@i3g2000cwc.googlegroups.com...
> 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
>

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

--
HTH

Bob Phillips

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

"Nader" <nader@infomaniak.ch> wrote in message
news:44e1d524$0$7973$5402220f@news.sunrise.ch...
> I can't break the formula.
>
> "Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de news:
> %235iMUoGwGHA.3264@TK2MSFTNGP03.phx.gbl...
> > 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)
> >
> > "Nader" <nader@infomaniak.ch> wrote in message
> > news:44e1b9fa$0$7963$5402220f@news.sunrise.ch...
> >> 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
> >>
> >>
> >
> >
>
>

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 :
>
> =3DIF($A9<>L$1;IF(OR(NOT(ISNA(MATCH(L$1;LEGACY_CURRENCY;0)));NOT(ISNA(MAT=
CH($A9;LEGACY_CURRENCY;0))));IF(OR(L$1=3D"EUR";$A9=3D"EUR");IF(NOT(ISNA(MAT=
CH(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;FALS=
E);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, th=
is
> formula should not be split.
>
> Thanks for you help Pete!
>
> "Pete_UK" <pashurst@auditel.net> a =E9crit dans le message de news:
> 1155644557.353544.299060@i3g2000cwc.googlegroups.com...
> > 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 t=
hat
> >> 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
> >

What I mean is that it should not be break.

thanks

"Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de news:
%23vC9VkHwGHA.4880@TK2MSFTNGP04.phx.gbl...
> I've seen your formula, and you could easily.
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Nader" <nader@infomaniak.ch> wrote in message
> news:44e1d524$0$7973$5402220f@news.sunrise.ch...
>> I can't break the formula.
>>
>> "Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de news:
>> %235iMUoGwGHA.3264@TK2MSFTNGP03.phx.gbl...
>> > 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)
>> >
>> > "Nader" <nader@infomaniak.ch> wrote in message
>> > news:44e1b9fa$0$7963$5402220f@news.sunrise.ch...
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Thanks a lot Pete, it's very useful !

"Pete_UK" <pashurst@auditel.net> a écrit dans le message de news:
1155653022.817023.98320@i42g2000cwa.googlegroups.com...
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_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(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(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!
>
> "Pete_UK" <pashurst@auditel.net> a écrit dans le message de news:
> 1155644557.353544.299060@i3g2000cwc.googlegroups.com...
> > 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
> >

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)

"Nader" <nader@infomaniak.ch> wrote in message
news:44e1e0bd$0$7965$5402220f@news.sunrise.ch...
> What I mean is that it should not be break.
>
> thanks
>
> "Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de news:
> %23vC9VkHwGHA.4880@TK2MSFTNGP04.phx.gbl...
> > I've seen your formula, and you could easily.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > (replace somewhere in email address with gmail if mailing direct)
> >
> > "Nader" <nader@infomaniak.ch> wrote in message
> > news:44e1d524$0$7973$5402220f@news.sunrise.ch...
> >> I can't break the formula.
> >>
> >> "Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de news:
> >> %235iMUoGwGHA.3264@TK2MSFTNGP03.phx.gbl...
> >> > 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)
> >> >
> >> > "Nader" <nader@infomaniak.ch> wrote in message
> >> > news:44e1b9fa$0$7963$5402220f@news.sunrise.ch...
> >> >> 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
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

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

"Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de news:
%23JxKJpIwGHA.4576@TK2MSFTNGP03.phx.gbl...
> 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)
>
> "Nader" <nader@infomaniak.ch> wrote in message
> news:44e1e0bd$0$7965$5402220f@news.sunrise.ch...
>> What I mean is that it should not be break.
>>
>> thanks
>>
>> "Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de news:
>> %23vC9VkHwGHA.4880@TK2MSFTNGP04.phx.gbl...
>> > I've seen your formula, and you could easily.
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (replace somewhere in email address with gmail if mailing direct)
>> >
>> > "Nader" <nader@infomaniak.ch> wrote in message
>> > news:44e1d524$0$7973$5402220f@news.sunrise.ch...
>> >> I can't break the formula.
>> >>
>> >> "Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de
>> >> news:
>> >> %235iMUoGwGHA.3264@TK2MSFTNGP03.phx.gbl...
>> >> > 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)
>> >> >
>> >> > "Nader" <nader@infomaniak.ch> wrote in message
>> >> > news:44e1b9fa$0$7963$5402220f@news.sunrise.ch...
>> >> >> 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
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

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

"Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de news:
%23JxKJpIwGHA.4576@TK2MSFTNGP03.phx.gbl...
> 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)
>
> "Nader" <nader@infomaniak.ch> wrote in message
> news:44e1e0bd$0$7965$5402220f@news.sunrise.ch...
>> What I mean is that it should not be break.
>>
>> thanks
>>
>> "Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de news:
>> %23vC9VkHwGHA.4880@TK2MSFTNGP04.phx.gbl...
>> > I've seen your formula, and you could easily.
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (replace somewhere in email address with gmail if mailing direct)
>> >
>> > "Nader" <nader@infomaniak.ch> wrote in message
>> > news:44e1d524$0$7973$5402220f@news.sunrise.ch...
>> >> I can't break the formula.
>> >>
>> >> "Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de
>> >> news:
>> >> %235iMUoGwGHA.3264@TK2MSFTNGP03.phx.gbl...
>> >> > 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)
>> >> >
>> >> > "Nader" <nader@infomaniak.ch> wrote in message
>> >> > news:44e1b9fa$0$7963$5402220f@news.sunrise.ch...
>> >> >> 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
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

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

"Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de news:
%23JxKJpIwGHA.4576@TK2MSFTNGP03.phx.gbl...
> 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)
>
> "Nader" <nader@infomaniak.ch> wrote in message
> news:44e1e0bd$0$7965$5402220f@news.sunrise.ch...
>> What I mean is that it should not be break.
>>
>> thanks
>>
>> "Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de news:
>> %23vC9VkHwGHA.4880@TK2MSFTNGP04.phx.gbl...
>> > I've seen your formula, and you could easily.
>> >
>> > --
>> > HTH
>> >
>> > Bob Phillips
>> >
>> > (replace somewhere in email address with gmail if mailing direct)
>> >
>> > "Nader" <nader@infomaniak.ch> wrote in message
>> > news:44e1d524$0$7973$5402220f@news.sunrise.ch...
>> >> I can't break the formula.
>> >>
>> >> "Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de
>> >> news:
>> >> %235iMUoGwGHA.3264@TK2MSFTNGP03.phx.gbl...
>> >> > 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)
>> >> >
>> >> > "Nader" <nader@infomaniak.ch> wrote in message
>> >> > news:44e1b9fa$0$7963$5402220f@news.sunrise.ch...
>> >> >> 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
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>

sorry for posting many times .. I had a little problem with group client ...

"Nader" <nader@infomaniak.ch> a écrit dans le message de news:
44e2e573$0$7956$5402220f@news.sunrise.ch...
> 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
>
> "Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de news:
> %23JxKJpIwGHA.4576@TK2MSFTNGP03.phx.gbl...
>> 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)
>>
>> "Nader" <nader@infomaniak.ch> wrote in message
>> news:44e1e0bd$0$7965$5402220f@news.sunrise.ch...
>>> What I mean is that it should not be break.
>>>
>>> thanks
>>>
>>> "Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de news:
>>> %23vC9VkHwGHA.4880@TK2MSFTNGP04.phx.gbl...
>>> > I've seen your formula, and you could easily.
>>> >
>>> > --
>>> > HTH
>>> >
>>> > Bob Phillips
>>> >
>>> > (replace somewhere in email address with gmail if mailing direct)
>>> >
>>> > "Nader" <nader@infomaniak.ch> wrote in message
>>> > news:44e1d524$0$7973$5402220f@news.sunrise.ch...
>>> >> I can't break the formula.
>>> >>
>>> >> "Bob Phillips" <bob.NGs@somewhere.com> a écrit dans le message de
>>> >> news:
>>> >> %235iMUoGwGHA.3264@TK2MSFTNGP03.phx.gbl...
>>> >> > 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)
>>> >> >
>>> >> > "Nader" <nader@infomaniak.ch> wrote in message
>>> >> > news:44e1b9fa$0$7963$5402220f@news.sunrise.ch...
>>> >> >> 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
>>> >> >>
>>> >> >>
>>> >> >
>>> >> >
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
>
>

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

Hope you were able to shorten your formula sufficiently.

Pete

Nader wrote:
> sorry for posting many times .. I had a little problem with group client =
..=2E.
>
> "Nader" <nader@infomaniak.ch> a =E9crit dans le message de news:
> 44e2e573$0$7956$5402220f@news.sunrise.ch...
> > 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 1=
80.
> >
> > Something like that ... and I almost have not much space left.
> >
> > =A6 CHF =A6 GBP =A6 USD | SEK =A6 LIR | EUR =A6 GIP |
> > CHF
> > -----
> > GBP
> > -----
> > USD
> > -----
> > SEK
> > -----
> > LIR
> > -----
> > EUR
> > -----
> > GIP
> >
> >
> >
> > Thanks Bob
> >
> > "Bob Phillips" <bob.NGs@somewhere.com> a =E9crit dans le message de new=
s:
> > %23JxKJpIwGHA.4576@TK2MSFTNGP03.phx.gbl...
> >> 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)
> >>
> >> "Nader" <nader@infomaniak.ch> wrote in message
> >> news:44e1e0bd$0$7965$5402220f@news.sunrise.ch...
> >>> What I mean is that it should not be break.
> >>>
> >>> thanks
> >>>
> >>> "Bob Phillips" <bob.NGs@somewhere.com> a =E9crit dans le message de n=
ews:
> >>> %23vC9VkHwGHA.4880@TK2MSFTNGP04.phx.gbl...
> >>> > I've seen your formula, and you could easily.
> >>> >
> >>> > --
> >>> > HTH
> >>> >
> >>> > Bob Phillips
> >>> >
> >>> > (replace somewhere in email address with gmail if mailing direct)
> >>> >
> >>> > "Nader" <nader@infomaniak.ch> wrote in message
> >>> > news:44e1d524$0$7973$5402220f@news.sunrise.ch...
> >>> >> I can't break the formula.
> >>> >>
> >>> >> "Bob Phillips" <bob.NGs@somewhere.com> a =E9crit dans le message de
> >>> >> news:
> >>> >> %235iMUoGwGHA.3264@TK2MSFTNGP03.phx.gbl...
> >>> >> > 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 inte=
rim
> >>> > result
> >>> >> > within the next part. This can be done very effectively to get to
> >>> >> > the
> >>> >> > final
> >>> >> > result.
> >>> >> >
> >>> >> > For instance,
> >>> >> >
> >>> >> > B1:
> >> =3DIF(ISNA(VLOOKUP(A1,M1:P10,2,False)),"",VLOOKUP(A1,M1:P10,2,False))
> >>> >> >
> >>> >> > Change this to
> >>> >> >
> >>> >> > B1: =3DIF(ISNA(C1),"",C1)
> >>> >> > C1: =3DVLOOKUP(A1,M1:P10,2,False)
> >>> >> >
> >>> >> > --
> >>> >> > HTH
> >>> >> >
> >>> >> > Bob Phillips
> >>> >> >
> >>> >> > (replace somewhere in email address with gmail if mailing direct)
> >>> >> >
> >>> >> > "Nader" <nader@infomaniak.ch> wrote in message
> >>> >> > news:44e1b9fa$0$7963$5402220f@news.sunrise.ch...
> >>> >> >> Hello,
> >>> >> >>
> >>> >> >> I'm having a little problem, recently I wrote a formula for exc=
el
> >>> >> >> 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
> >>> >> >>
> >>> >> >>
> >>> >> >
> >>> >> >
> >>> >>
> >>> >>
> >>> >
> >>> >
> >>>
> >>>
> >>
> >>
> >
> >
> >