|
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 |
formula or vba code
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 |
formula or vba code
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" wrote in message ... 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 |
formula or vba code
I can't break the formula.
"Bob Phillips" a écrit dans le message de news: ... 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" wrote in message ... 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 |
formula or vba code
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 |
formula or vba code
I've seen your formula, and you could easily.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nader" wrote in message ... I can't break the formula. "Bob Phillips" a écrit dans le message de news: ... 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" wrote in message ... 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 |
formula or vba code
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 |
formula or vba code
What I mean is that it should not be break.
thanks "Bob Phillips" a écrit dans le message de news: ... I've seen your formula, and you could easily. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nader" wrote in message ... I can't break the formula. "Bob Phillips" a écrit dans le message de news: ... 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" wrote in message ... 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 |
formula or vba code
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" wrote in message ... What I mean is that it should not be break. thanks "Bob Phillips" a écrit dans le message de news: ... I've seen your formula, and you could easily. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nader" wrote in message ... I can't break the formula. "Bob Phillips" a écrit dans le message de news: ... 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" wrote in message ... 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 |
formula or vba code
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" a écrit dans le message de news: ... 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" wrote in message ... What I mean is that it should not be break. thanks "Bob Phillips" a écrit dans le message de news: ... I've seen your formula, and you could easily. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nader" wrote in message ... I can't break the formula. "Bob Phillips" a écrit dans le message de news: ... 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" wrote in message ... 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 |
formula or vba code
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" a écrit dans le message de news: ... 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" wrote in message ... What I mean is that it should not be break. thanks "Bob Phillips" a écrit dans le message de news: ... I've seen your formula, and you could easily. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nader" wrote in message ... I can't break the formula. "Bob Phillips" a écrit dans le message de news: ... 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" wrote in message ... 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 |
formula or vba code
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" a écrit dans le message de news: ... 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" wrote in message ... What I mean is that it should not be break. thanks "Bob Phillips" a écrit dans le message de news: ... I've seen your formula, and you could easily. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nader" wrote in message ... I can't break the formula. "Bob Phillips" a écrit dans le message de news: ... 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" wrote in message ... 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 ...
sorry for posting many times .. I had a little problem with group client ...
"Nader" a écrit dans le message de news: ... 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" a écrit dans le message de news: ... 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" wrote in message ... What I mean is that it should not be break. thanks "Bob Phillips" a écrit dans le message de news: ... I've seen your formula, and you could easily. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nader" wrote in message ... I can't break the formula. "Bob Phillips" a écrit dans le message de news: ... 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" wrote in message ... 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 ...
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 .... "Nader" a écrit dans le message de news: ... 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" a écrit dans le message de news: ... 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" wrote in message ... What I mean is that it should not be break. thanks "Bob Phillips" a écrit dans le message de news: ... I've seen your formula, and you could easily. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Nader" wrote in message ... I can't break the formula. "Bob Phillips" a écrit dans le message de news: ... 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 03:11 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com