Excel Tips - Moving Averages

is it possible to create a moving average on the excel graph?

I've been at this for some time, both trying and searching..

What I need to do is make a sumproduct of some chemical analysis I have.

Like this:
=SUMPRODUCT($L$1:$AN$1,L$7:AN$7)/SUM($L$1:$AN$1)

The problem is that what I actually need to do is calculate the L$7:AN$7 part as a moving average of the last 3 months. so the value in e.g. L$7 should be replaced by average(j$7:L$7). (I've tried various array formulas without succes).

Thanks for any help!

I have daily weather data (high, low, and average temperatures) for the past 37 years (from 1/1/1965 through 12/31/2002) in an Excel spreadsheet in 4 columns: the first, the date; the second, the high; the third, the low; and the 4th, the average of the high and low. What I am interested in doing is calculating historical averages of each day for the following periods of time: 2 years, 3 years, 4, years, 5 years, 10 years, 15 years, 20 years, 25 years, and 30 years. For example, the historical 2 year average high temperature for February 23, 2003 would be the average of the high temperatures for 2/23/2002 and 2/23/2001. For leap years, I will have data for only every 4th year. Therefore, the "samples" (or temperature readings) over which the averages will be calculated will be smaller, obviously.
Anyone lend a hand?

Hi,
I had build a program in VBA which is calculate 2 moving averages (20, 25)MA. How can I create a msgbox which will top up when the 20MA cut the 25MA?

I want to show in msgbox the situantion where the 20MA is lower the 25MA and suddenly pass above the 25MA (20MA is higher than the 25MA)

How can I do this?? with the if then statment?

Thanks

Hi,

I am trying to create a formula that will dynamically calculate a short-run and long-run moving average. The number of periods for each moving average can change depending on what is typed into cell C2 and cell C3.

For example, if $C$2=2, then the SRMA in cell E8 would be the average for Periods 1-2, or 594,443 (average of cells D7:D8). If $C$2=3, then cell E8 would be empty and cell E9 would be 602,394.

Thanks so much!
John

I am trying to build an excel formula for doing some basic stock analysis. I found a way for excel to automatically link with MSN money and import LIVE basic stock quotes.

What I am now looking for is a way to calculate the current 200 and 50 day moving averages of the stock price without having to manually input new information every day (since i am setting this up for many individual stocks).

Is there anyway to automatically import the 200 & 50 day averages directly from a web site or something of that nature? If not is there a way to have a up to date list of the last 200 day closing prices of a stock automatically updated in excel? Any guidance someone can give me would be much appreciated!

I am sure someone here can tell me whether the following can be done or not.

I have a column of values in Column A.

I would like column B to contain a moving average of these values. However, I would like to be able to specify the length of the Moving Average to calculate.

For instance, by typing 4 in cell D1, Excel would calculate a 4 period moving average. Cell B4 would then contain the average of cells A1:A4, B5 an average of A2:A5, etc..

If I typed a 7 in cell D1, Cell B7 would then contain the average of cells A1:A7, B8 an average of A2:A8, etc..

I can't figure out how to do it with a formula, and am not sure about a user defined function.

I do think I could use code to write the correct formula when a moving average length was changed, then copy that new formula down the column. I have never tried writing a formula in VBA and placing it into a cell but it seems it would work.

Am I missing something obvious?

Matt

OK I may be asking Excel to do something it's really not designed for, but here goes anyway...

I have a workbook consisting of 5 worksheets:

A) Advertising Expenses
B) Revenue tied to specific ad campaigns
C) Pivot tables summarizing a & b
D) Line item detail (table calculating information about each ad campaign)
E) Daily breakdown (pivot tables) with chart

I have a lovely chart on sheet E that shows me the current month's trend, and I can easily plot a 5 day moving average to see how we're trending over the near term.

However, I'd also like to be able to show what the CURRENT 5-day MA is, next to each of the items on D, so that I can cancel or extend a specific ad campaign based on its current trend.

In other words, if I see a campaign has been running for several weeks but today its revenue has fallen below the 5-day MA, I want to end that ad campaign.

I can already see this visually on the chart, but I have to fiddle with the pivot tables too much on sheet E. I'd like to just see at a glance and maybe use conditional formatting to show me red & green next to each line item on sheet D.

This is probably better suited to a database application, but I'm working in Excel so... it is what it is.

Suggestions on how to accomplish this?

I can get a 5-day MA directly on sheets A & B using an array formula, but I think I need to somehow marry that to a VLOOKUP from sheet D in order to reference the calculation on an entire column of data from sheet A, to the single item on sheet D.

Does this make sense to anyone but me?

Thanks all!

Jonathan

Hello. I have a chart that is a time series of observed birds. I have set the y-axis time scale (years) to 4 years past the current data point. This was done to save time in the future since I cannot globally change the 150 or so charts.

I use a Moving Average Trendline set at a period of 5. I want to end the trendline at the current year, rather then let it run into the future (in this case 2010). I do not care to project future population changes.

Please advise. Thanks in advance.

Hi all, new to the forum and have a problem i hope you can fix.

I run a 5-a-side league and am trying to put together a s/s that when i put in the number the number of points a team wins over a date range will produce the average points scored over the last 3, 4 & 5 games.

I have my s/s laid out as follows, column B1 is team, B2 is average points scored over last 3 games, B3 is average points scored over last 4 games, B4 is average points scored over last 5 games, B5 onwards are points scored per game, i.e. 0 for loss, 1 for draw, 3 for win). Column A5 onwards is date played. Where the team hasn't played i want the formula in B2, B3 & B4 to ignore this and not count as 0 for average purposes, however where a team has played and not scored any points i want to calculate the average using the 0.

I have the following formulas in B2, B3 & B4 respectively:

=AVERAGE(OFFSET(B5,COUNTA(B5:B17)+COUNTBLANK(B5:B17)-3,0,5,1))
=AVERAGE(OFFSET(B5,COUNTA(B5:B17)+COUNTBLANK(B5:B17)-4,0,5,1))
=AVERAGE(OFFSET(B5,COUNTA(B5:B17)+COUNTBLANK(B5:B17)-5,0,5,1))

The above formula calculates the averages correctly unless there is a blank space (i.e. where a team has not played they have not accrued any points, which i want to ignore).

Example: if over the last 6 games Team A has gained the following points:

3
No game played
3
3
3
1

I would expect the averages to be as follows:

Over 3 games = 2.33 [(3+3+1)/3)]
Over 4 games = 2.5 [(3+3+3+1)/4]
Over 5 games = 2.6 [(3+3+3+3+1)/5] - the key being the blank where no game was played being ignored and the previous score being taken.

However for the average over 5 games, using the above formula i am getting 2.5.

Please help

Part of my s/s attached: http://download.yousendit.com/Z01OOU1lcTJHa1BIRGc9PQ