Rank: Newbie
Groups: Registered, Registered Users
Joined: 4/15/2009(UTC)
Posts: 5
|
I am trying to recreate this formula in Excel and am having difficulty in interpreting what the Excel equivalent would be - any ideas?
MS Formula:
if(C-Ref(C,-1) AND Mov(C,2, E)<Ref(C,-2) = 0), 1, 0);
************************************************
Does this mean if C-Ref(C,-1) <Ref(C,-2) AND Mov(C,2, E)<Ref(C,-2) THEN 1 ELSE 0?
Any help would be much appreciated.
|
|
|
|
|
|
Rank: Advanced Member
Groups: Registered, Registered Users, Subscribers, Unverified Users
Joined: 10/28/2004(UTC)
Posts: 3,111
Location: Perth, Western Australia
Was thanked: 16 time(s) in 16 post(s)
|
Boy, this formula raises some great learning points!
Firstly, look at the way MS computes binary logical values; there is no need for the If(expression, {true}1, {false}0) as MS already computes this without the If() part.
Next, have a look in the MS Users Manual for operator precedence as it will explain in what order the expression will be evaluated.
Whilst in the MS Users Manual have a look at the syntax of each of the functions used in your expression. This should give you a pretty good idea how you're going to need to reference other cells in the Excel spreadsheet. There is no single Excel function to replicate the exponential function without some additional add-ins for Excel, so you're going to have write own worksheet functions for this; the algorithm for EMA can be found in the MS Users Manual too.
As an alternative to using Excel to do the computations in lieu of using MS, why not use MS to do the number crunching and use a DDE link to send the data to Excel for further processing? DDE links don't necessarily suit all needs but some people would be surprised at what can be achieved using "cut-and-paste-special".
Hope this helps.
wabbit [:D]
P.S. Please try to get the formula right when asking for help. Your expression has mismatched brackets, which may affect the logic and order or evaluation.
|
|
|
|
|
|
Rank: Newbie
Groups: Registered, Registered Users
Joined: 4/15/2009(UTC)
Posts: 5
|
Many thanks.
Apologies re: formula syntax - it is a subset of a larger formula so I was a little careless in stripping it out.
I have replicated EMA and c. 20 other functions in Excel VBA - owing to the time it takes on MS to run the analysis I want. The formulae were created by a third party, so am just trying to understand the operands and built-in functions of MS as they seem to differ from programming logic I have encountered in the past.
I'll consult the MS Users Manual.
Thanks again for your insight.
Ronan
|
|
|
|
|
|
| Users browsing this topic |
|
Guest (Hidden)
|
Forum Jump
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.
