Exponential Moving Average

  • Thread starter Lawrence M. Seldin, CMC, CPC
  • Start date
L

Lawrence M. Seldin, CMC, CPC

I used the Data Analysis pak and found the syntax below for moving average.

=AVERAGE(H22:H31)

Can someone tell me what the syntax for an exponential moving average is?

Thanks much,

Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
 
M

Mark Graesser

Hi Lawrence
By "exponential moving average" do you mean the average of the exponentials of each number? If so then the following aray entered formula will work

=AVERAGE(EXP(H22:H31)

This formula needs to be entered by hitting -ctrl- -alt- -enter- instead of just enter. The formula will be enclosed by a set of brackets { }

If this isn't what your looking for, let me know what the term means

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- Lawrence M. Seldin, CMC, CPC wrote: ----

I used the Data Analysis pak and found the syntax below for moving average

=AVERAGE(H22:H31

Can someone tell me what the syntax for an exponential moving average is

Thanks much

Lawrence M. Seldin, CMC, CP
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CS
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXA


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.ne
 
L

Lawrence M. Seldin, CMC, CPC

Mark,

I tried Cntrl-alt-Enter. Nothing happens until I press Enter.

I get #VALUE!


Not sure what to do.

Could you please help.

Thanks much,

- Larry



----------------------------------------------------------------------------------------
Mark Graesser said:
Hi Lawrence,
By "exponential moving average" do you mean the average of the exponentials of each number? If so then the following aray entered formula will work:

=AVERAGE(EXP(H22:H31))

This formula needs to be entered by hitting -ctrl- -alt- -enter- instead of just enter. The formula will be enclosed by a set of brackets { }.

If this isn't what your looking for, let me know what the term means.

Good Luck,
Mark Graesser
(e-mail address removed)
Boston MA

----- Lawrence M. Seldin, CMC, CPC wrote: -----

I used the Data Analysis pak and found the syntax below for moving average.

=AVERAGE(H22:H31)

Can someone tell me what the syntax for an exponential moving average is?

Thanks much,

Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
Click to expand...

Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
 
L

Lawrence M. Seldin, CMC, CPC

I should have said -ctrl- -shift- -enter-

Mark,

Now I am getting:

#NUM!

Any suggestions.

Thanks much,

- Larry




--------------------------------------------

Mark Graesser said:
Sorry, my mistake.

I should have said -ctrl- -shift- -enter-

Good Luck,
Mark Graesser
(e-mail address removed)
Boston MA

----- Lawrence M. Seldin, CMC, CPC wrote: -----

Mark,

I tried Cntrl-alt-Enter. Nothing happens until I press Enter.

I get #VALUE!


Not sure what to do.

Could you please help.

Thanks much,

- Larry



----------------------------------------------------------------------------------------


Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
Click to expand...

Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
 
M

Mark Graesser

Hi Again Lawrence
The #NUM error means that Excel can't calculate the value. What values are you trying to take the exponential of? For some reason excel can't take an exponential of a number over 709.

Well that's it for me. I'll recheck this thread on Monday if you have any more trouble

Good Luck
Mark Graesse
(e-mail address removed)
Boston M

----- Lawrence M. Seldin, CMC, CPC wrote: ----
I should have said -ctrl- -shift- -enter
Click to expand...

Mark

Now I am getting

#NUM

Any suggestions

Thanks much

- Larr




-------------------------------------------

Mark Graesser said:
Sorry, my mistake
Mark Graesse
(e-mail address removed)
Boston M
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CS
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXA
Click to expand...

Lawrence M. Seldin, CMC, CP
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CS
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXA


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.ne
 
L

Lawrence M. Seldin, CMC, CPC

Mark,


Graesser said:
Hi Again Lawrence,
The #NUM error means that Excel can't calculate the value. What values are you trying to take the exponential of? For some reason excel can't take an exponential of a number over 709.
Click to expand...

Mark,

That was the problem with the syntax. However, that was not what I was looking for.

I am enclosing the "blurb" of Exponential Moving Average from MetaStock, could you take a look at
it, and tell me the formula for what I want.

Much appreciated.


A1 1
A2 2
A3 3
A4 4
A5 5

Average(Exp(A1:A5))

Gave a number way off of 46.64


---------------------- begin

An exponential (or exponentially weighted) moving average is calculated by applying a percentage of
today's closing price to yesterday's moving average value.
For example, to calculate a 9% exponential moving average of IBM: First, we would take today's
closing price and multiply it by 9%. We would then add this product to the value of yesterday's
moving average multiplied by 91% (100% - 9% = 91%).

Because most investors feel more comfortable working with time periods rather than with percentages,
MetaStock converts days into an exponential percentage. For example, if a 21-day exponential moving
average is requested, a 9% moving average is calculated.

The formula for converting days to exponential percentages is as follows:

For example, to calculate a 10-day exponential moving average, you would use 0.18:

To convert an exponential percentage into time periods, you would use the following formula:

Using our previous example, we can check to see that a 0.18 exponential moving average is actually a
10-day average.

The method used to calculate an exponential moving average puts more weight toward recent data and
less weight toward past data than does the simple moving average method. This method is often
called exponentially weighted.

---------------------- end

Thanks for any help you can give.


Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
 
J

Jerry W. Lewis

An exponential (or exponentially weighted) moving average is calculated by applying a percentage of
today's closing price to yesterday's moving average value.
For example, to calculate a 9% exponential moving average of IBM: First, we would take today's
closing price and multiply it by 9%. We would then add this product to the value of yesterday's
moving average multiplied by 91% (100% - 9% = 91%).
Click to expand...

Implementing this is difficult because?

Use a helper column with the weighted averages, and it is
straightforward. I would tend to calculate it using the equvalent formula
a' = a + 9%*(x'-a)
where a is yesterday's exponential moving average, x' is today's closing
price, and a' is today's exponential moving average. The expnential
moving average moves in the direction of today's close, but that change
is discounted.

To calculate without the helper column, you would do something like

=SUMPRODUCT(0.09*A$1:A12*0.91^(ROW(A12)-ROW(A$1:A12)))
+0.91*A$1*0.91^(ROW(A12)-ROW(A$1))

to get the exponetial moving average of A1:A12. With enough data, the
add-on at the end will vanish.

Jerry
 
L

Lawrence M. Seldin, CMC, CPC

Jerry,

Could you please do me a favor and just give me this example. I am a little slow.

Col A1 = 11
ColA2 = 22
ColA3 = 33
ColA4 =44
ColA5 =55
ColA6 =66
ColA7 =77
ColA8 =88
ColA9 =99
ColA10 = 111


I am looking for the Exponential Moving Average for 10 Days with the above Data, each cell
represents 1 day

Using a "helper column" is not a problem. I just need the total calculation spelled out.

Thanks for your help.

- (e-mail address removed)


-----------------------------------------------------------------------------------

Jerry W. Lewis said:
Implementing this is difficult because?

Use a helper column with the weighted averages, and it is
straightforward. I would tend to calculate it using the equvalent formula
a' = a + 9%*(x'-a)
where a is yesterday's exponential moving average, x' is today's closing
price, and a' is today's exponential moving average. The expnential
moving average moves in the direction of today's close, but that change
is discounted.

To calculate without the helper column, you would do something like

=SUMPRODUCT(0.09*A$1:A12*0.91^(ROW(A12)-ROW(A$1:A12)))
+0.91*A$1*0.91^(ROW(A12)-ROW(A$1))

to get the exponetial moving average of A1:A12. With enough data, the
add-on at the end will vanish.

Jerry
Click to expand...

Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
 
J

Jerry W. Lewis

Put =A1 in B1
Put =B1+0.09*(A2-B1) in B2
Copy cell B2, and paste into B3:B10

Column B contains the weighted averages following each day. After Day
10, the weighted average is 46.46308

Jerry
 
L

Lawrence M. Seldin, CMC, CPC

Jerry,

Thanks for the help.

Much appreciated.


- Larry


Lawrence M. Seldin, CMC, CPC
Author of POWER TIPS FOR THE APPLE NEWTON and INTRODUCTION TO CSP
Author of RECRUITSOURCE PEOPLESOFT EXAM and RECRUITSOURCE SAP/R3 EXAM


NOTE: To send me an email, remove TAKEOUT from my email address: (e-mail address removed)

NOTE: My web home page: www.seldin.net
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I reference a cell in another worksheet 4
How can I automatically run a macro every 1 minute or every 5 minuts 2
How to enter a range plus an additional cell in a function. 2
Paste Link in Excel greyed out 0
Last updated time for cell 19
Help with VB Excel 2
Help with VB Call function from Excel 0
Paste Link is greyed out during Paste Special 0

Top