EWMA and charts in excel

**Nukehed** · 07-21-2012, 05:03 PM

Hello to all!

I think I am in need of some help. I wish to make a chart with an EWMA (Expotentially Weighted Moving Average) from some data. While I understand the concept behind a EWMA, I'm not sure how to do this in Excel and then chart the EWMA.

Is there some formula in an add-in I can use or do I have to calculate the numbers.

I am using Excel:mac 2011.

**Richard Buttrey** · 07-21-2012, 05:43 PM

Hi,

I don't know of any standard Excel function.
I think you'll either have to write your own UDF in the Visual Basic Environment, or create a table of numbers on the sheet.

**shg** · 07-21-2012, 06:07 PM

I believe this is correct for a 10-day exponential average. You start with a regular average and go from there:

       --A--- --B--- --C--- -------------------------D-------------------------
   1    Date  Price   Avg                                                      
   2   23-Jan 100.00                                                           
   3   24-Jan  98.89                                                           
   4   25-Jan  99.50                                                           
   5   26-Jan  99.78                                                           
   6   27-Jan 100.37                                                           
   7   28-Jan 101.91                                                           
   8   29-Jan 102.31                                                           
   9   30-Jan 102.50                                                           
  10   31-Jan 101.35                                                           
  11   01-Feb 101.70 100.83 B11: =AVERAGE(A2:A11)                              
  12   02-Feb 100.97 100.86 B12 and down: =2/(10+1) * A12 + (10-1)/(10+1) * B11
  13   03-Feb 100.58 100.81                                                    
  14   04-Feb  99.73 100.61                                                    
  15   05-Feb  99.42 100.39                                                    
  16   06-Feb  99.64 100.26                                                    
  17   07-Feb  97.63  99.78                                                    
  18   08-Feb  97.07  99.29                                                    
  19   09-Feb  99.42  99.31                                                    
  20   10-Feb 101.86  99.77                                                    
  21   11-Feb 101.52 100.09                                                    
  22   12-Feb 101.22 100.30                                                    
  23   13-Feb 101.58 100.53                                                    
  24   14-Feb 100.06 100.44                                                    
  25   15-Feb 101.60 100.65                                                    
  26   16-Feb 101.87 100.88                                                    
  27   17-Feb  98.68 100.48                                                    
  28   18-Feb  98.27 100.08                                                    
  29   19-Feb  99.75 100.02                                                    
  30   20-Feb 100.84 100.17                                                    
  31   21-Feb 100.76 100.27                                                    
  32   22-Feb 102.81 100.74                                                    
  33   23-Feb 104.94 101.50                                                    
  34   24-Feb 104.16 101.98                                                    
  35   25-Feb 104.34 102.41                                                    
  36   26-Feb 104.31 102.76