# Moving Average Dilemma

Sep 1, 2008
I have a set of data in column A consisting of over 1200 numeric values. The problem is that there are some blank cells in this column:

Colums A data:120, blank, 135, blank, etc

I need to calaculate the average for the first 25 data and then claculate the average for the next 25 data set dropping the first 5 and adding new consecutive 5 data ignoring all the bank cells!, so if the first statemnet is : averagre (a1:a24), the second statmnet should read (a6:a:29) not counting the blank cells. I don't know what array formula can be used to do this job for me.

View 9 Replies
ADVERTISEMENT
Sep 7, 2007

I have a personal.xls macro that basically does a comparison of two different workbooks. One of these workbooks ihas it's own macros (which are there in case the user wants to run them separately)...anyways my main macro calls these other macros one at a time.

It seems that at each call, the screen updates, alternating sheets and/or workbooks....

I know about the Application.Screenupdating=True / False statements....but I am not sure where I should be putting them to get the whole thing to work seamlessly.... I tried them at the start and end of my main macro, but that doesn't do the job.

View 14 Replies
View Related
Dec 8, 2006

this User Defined Function (UDF) would operate on any specified data (ignoring blanks) over a range. Inputs to the UDF are range and period.

View 9 Replies
View Related
Aug 29, 2008

I have obtained a function (from this site at Exponential Moving Average) which is supposed to help calculate simple mathematical values but it's not working on spreadsheet. assist with taking a look at this as I have attached the spreadsheet?

View 6 Replies
View Related
Apr 12, 2008

In the table below I'm trying to work how trend is being produced but I can't figure it out.

I have found how to find the moving average =AVERAGE(C2:C5)

I've tried a few methods using the trend function but I really don't understand what I'm doing. how its being calculated by looking at the table?

View 9 Replies
View Related
Jul 8, 2013

I am trying to do a moving average and running into an issue with the count part of the formula.

Column C (C2 to C54) has the numbers I need to average.

Average is based on last 5 weeks.

My issue is that C23 is blank as we had no data for that week.

Currently the formula is counting 25 entries between C2:C27, there should be 26.

My average should be 31 but I am getting 33.

This is the formula I am using :

=AVERAGE(OFFSET(C2,COUNT($C$2:$C$54)-5,0,5,1))

I have tried using different Counta and ISnumber still not working

I would rather use a formulas then VB code.

View 5 Replies
View Related
Nov 25, 2006

I have done quite a bit of looking on Google and looked over the posts in this forum, however, I can't find an example Excel worksheet for a linear weighted moving average.

The data set I am applying this to has 180 data points and the linear weights should extend back over the last 30 points.

View 9 Replies
View Related
Mar 30, 2007

I am using the built in moving average function to calculate the moving average of a set of numbers. There are a few things that i would like to do.

First i would like to have the last result displayed in a single cell. Then next to that cell i would like to have a cell that would specify the period of the moving average. I would like to be able to change the period in that cell and have that change it in the actual function. And finally i would like to have the moving average in a chart that would also change its period once that is changed in the respective cell. I realize that this might need some VB coding which i am currently learning.

View 14 Replies
View Related
Mar 15, 2014

I would like to calculate the moving average for the first ten rows in a column where each new entry is added to the cell above the last entry.

For example

A5, 2

A6, 3

A7, 4

A8, 5

A9, 6

A10, 7

A11, 8

A12, 9

A13, 10

A14, 11

A15, 12

Therefore in the current list the average is AVERAGE(A5:A14)=6.5.

The next entry added to the list will be in cell A4 making the list look like this

A4, 1

A5, 2

A6, 3

A7, 4

A8, 5

A9, 6

A10, 7

A11, 8

A12, 9

A13, 10

A14, 11

A15, 12

The average of the first 10 cells is now AVERAGE(A4.A13) = 5.5

I would like to enter one formula in say cell A1 that will calculate the moving average in the first ten cells each time a new entry is added. Naturally as needed, new rows are added below cell A1. I hope my problem is clear.

as I have been unable to solve it myself. I have tried using offset but have been unable to resolve the issue.

View 2 Replies
View Related
Aug 15, 2007

I have a column of data that contains various blank cells where no data was measured. In the adjacent column I want to take the moving average of the last 4 data points including the most recent entry. My problem is i do not know how to handle blank cells where there was no data. I need it to average the last four in the column where data acutally exists. I am ok with using helper cells if needed and I am not worried about the first four results at this time.

Example data

A..................B

15

50

25

20................55

Blank............55

30................31.25

35................27.5

blank............27.5

blank............27.5

15................25

10................22.5

15................18.75

40................20

blank.............20

View 10 Replies
View Related
Dec 2, 2012

The following code works fine if the data is sorted from oldest to newest. I need for it to work when the data is sorted from newest to oldest. For this to work, I would need a change that works something like this: "total1 = total1 + close_(a, 1) * a" would be "total1 = total1 + close_(a, 1) * b" with "b" incremented in the opposite direction of "a". I code very infrequently & have tried a lot of different things, searched the Internet, etc. all to no avail.

For instance, with "close" data in column "I", the data sorted as follows works with the formula

"=WMA(OFFSET(I2,0,0,9,1) )" to correctly return "791.50", but the opposite sort returns the wrong number.

The correct calculation is: (807.19*9+798.38*8+793.81*7+793.06*6+776.28*5+769.48*4+773.2*3+789.01*2+793.87*1)/(9+8+7+6+5+4+3+2+1).

DateClose

11/12/2012793.87

11/13/2012789.01

11/14/2012773.2

11/15/2012769.48

11/16/2012776.28

11/19/2012793.06

11/20/2012793.81

11/21/2012798.38

11/23/2012807.18

Code:

Public Function WMA(close_)

total1 = 0

n = WorksheetFunction.Count(close_)

divisor = (n * (n + 1)) / 2

[Code] ..........

View 1 Replies
View Related
May 4, 2008

how do I perform calculations on the last x non-blank instances in a data range?

for example, let's say I have a spreadsheet of 5 baseball players' batting averages (rows are team game number played, columns are at bats and hits for each player). I want to see how each player has performed in their last 10 games played, but some players have not played in every game. If I just use the sum function for the last 10 cells, I won't get the correct information for any player that has missed one or more of the last 10 games.

View 8 Replies
View Related
May 19, 2008

Currently i have a spreadsheet with realtime data feeds from Bloomberg (or reuters). What i would like to do is:

a. Fill a vector/column of data values every minute until we have 30 observations i.e. from 9.00am till 9.30pm

b. This will then allow me to calculate a moving average of the last 30 (1 minute) observations.

c. At 9.31am, the 9.00am value drops out of the column and is replaced by the observation at 9.31am. This results in a constantly updating column of the last 30 minute observations and will allow me to have a realtime moving average.

View 2 Replies
View Related
Jul 5, 2006

I have a worksheet that has all weekday dates in column 1 and values in column 2. I want to create a 30-day moving average based on the last (non-zero) value in the column 2.

Since every month has a different amount of days, I want it to search the date that has the last value (since I don't get a chance to update it daily) and go back thirsty days from that date and give an average of all the column 2 values skipping and values that are null or zero.

View 13 Replies
View Related
May 3, 2013

I have a pivot that includes customer data and I need to create an average for each row based on the first populated column for each customer. After one month of units appears for the customer, I need to calculate the average going forward, and if there are blanks after that they should be treated as 0 in the average. Below is an example of my description of the pivot.

Jan Feb Mar Apr

Customer #1 1 1 3

Customer #2 1 3

Customer #3 2 2

So, the average for customer #1 would be calculated from Column B to Column E, and factor column D as a 0 in that calculation. Customer 2's average would start in Column C and go to Column E, and factor column E as a 0 in that calculation. Customer 3's average would start in column D to column E.

View 5 Replies
View Related
Sep 23, 2013

I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.

View 5 Replies
View Related
Jun 14, 2013

E11 through E24 contains numbers and a few errors (#N/A) that need to persist (the errors need to show).

E10 needs to show the average of the numbers that are in E11 through E24, and just ignore the errors.

I have many columns like that - where the errors need to show and I need to show an average of the number/values that do appear, ignoring the errors.

View 14 Replies
View Related
Dec 9, 2008

i have two columns...a and b (a w/ names, and b w/grades). then i have the table lookup with names and grades all mixed up for many rows. i want to be able to average the grades with appropriate names.

=average(vlookup(name, table, column, false))?? i don't get it to work and how can i specify the grade to average?

View 5 Replies
View Related
Jan 23, 2007

TotHCInv.Value = WorksheetFunction. Sum(KRInv, PBLInv, CRInv, PVInv)

If i >= 34 Then CPSCtphRMA.Value = WorksheetFunction.Average("G" & (i - 30) & ":G" & i)

The first line runs properly, but the second line bugs out with the error message "Unable to get the Average property of the WorksheetFunction class". I can simply do the math, but I thought that using the worksheet function would be easier than summing and dividing. I'm curious, though, as to why I can't seem to use the Average function.

View 3 Replies
View Related
May 23, 2014

Can I get a minimum average and a maximum average, I have a worksheet with days of supply for 100 stores with about 100-200 products each, the dos resides in column D.

I was going to create a summary page and reference column d.

The following just gives me the min, I want the min average if possible:

View 5 Replies
View Related
Sep 1, 2006

I am attempting to calculate Grade point averages for my students for all classes. There are 5 columns of grades to be considered. I frist assign values of 0-5 to the grades then average the points. My problem is that I want the AVERAGE to ignore the zero but it calculates it as well. (I enter a 0 if I have no grade for that class.) I have tried the{ } to make it an array formula but this also did not work. Here is what I have, can anyone help?

=SUM(AVERAGE(IF(AND(L2>89,L2<100),5,IF(AND(L2<90,L2>79),4,IF(AND(L2>69,L2<80),3,IF(AND(L2<70,L2>59), 2,IF(AND(L2>0,L2<60),1,0))))),IF(AND(T2>89,T2<100),5,IF(AND(T2<90,T2>79),4,IF(AND(T2>69,T2<80),3,IF( AND(T2<70,T2>59),2,IF(AND(T2>0,T2<60),1,0))))),IF(AND(AB2>89,AB2<100),5,IF(AND(AB2<90,AB2>79),4,IF(A ND(AB2>69,AB2<80),3,IF(AND(AB2<70,AB2>59),2,IF(AND(AB2>0,AB2<60),1,0))))),IF(AND(AJ2>89,AJ2<100),5,I F(AND(AJ2<90,AJ2>79),4,IF(AND(AJ2>69,AJ2<80),3,IF(AND(AJ2<70,AJ2>59),2,IF(AND(AJ2>0,AJ2<60),1,0))))) ,IF(AND(AR2>89,AR2<100),5,IF(AND(AR2<90,AR2>79),4,IF(AND(AR2>69,AR2<80),3,IF(AND(AR2<70,AR2>59),2,IF (AND(AR2>0,AR2<60),1,0)))))),-1)

I know it is huge. The syntax is correct. It calculates the average, but always for all 5 columns. It will not ignore a 0 in a column.

View 10 Replies
View Related
Jan 7, 2008

I'm using the formula =AVERAGE(B16:L16) to give me the average.

However I have a couple of problems with this. Firstly I would like to exclude the value zero from the average. Secondly to also ignore the lowest and highest values.

Example, if the values in the cells are 0,1,2,3,4,5,6,7,8,9,10 then the current result shows 5, by ignoring the 0 and lowest value 1 and highest value 10 the average should be 4.5.

View 9 Replies
View Related
Oct 11, 2006

Ok, I for some reason just cant wrap my head around this. I need to to get the average time per call of two rows, but they are based on how many calls taken.

so in one column i have 50168 calls taken at 4:21 seconds per call. and in the next row i have 597 calls taken at 5:20 per call. I need to see what the new average will be with them combined, and I need it to display in m:ss form.

View 9 Replies
View Related
Aug 13, 2008

to formulate Excel formulas to obtain the average buy price and average sell price for me to do this futures trading. Thanks a lot. I downloaded the Htmlmaker to post the spreadsheet here to show the manual way to calcualte the average buy price and average sell price but when it is on html form, i clicked on the 'Please click this button to send the source into clipboard' button & then i paste into this thread. Is the way to make my spreadsheet appear here correct cause it cannot work.

View 9 Replies
View Related
Jul 9, 2014

Let's say there's a couple row:

[Code].....

Here's a column I will be pasting:

[Code] ....

If I want to "Insert Copied Cells" in place of Yellow on Row1 and shift the cells down, the rows would look like this:

[Code] .....

How can I make it so the rows would be the following during such a paste:

[Code] .....

This is because I have hundreds of cells that I need to insert as columns all the while pushing complete rows down. If you're trying to understand why, it will be for some online marketing purpose as demonstrated here in this sheet: [URL] ...

View 7 Replies
View Related
Feb 9, 2006

I am unaware how to set up a combo box to allow users to move from the combobox to the next combo box . tab or enter? The way we do it know is we click with the mouse from combobox.

View 3 Replies
View Related
Mar 5, 2007

I have a workbook with 2 work sheets, when a row has "yes" typed into the "accepted bid" column I would like that row to be placed into the second sheet in the order in which it was entered "yes"

so, if on the first sheet a row, lets say row 10 had "yes" typed into it but row 10-15 on the second sheet had something in it already, the info would have to be placed in the next available row.

I have approximatley 20 rows on the first sheet and the ones that transfer to the second can just be added to it as needed.

View 14 Replies
View Related
Mar 24, 2014

I want to move the vales from F8 to F9, F9 to F10, F10 to F12 (there is a space)and so one. I would like it to be triggered by changing the value in F8 if possible. I have tried a good few ways, sometimes I get a stack 28 error.Zones.xlsm

View 6 Replies
View Related
Apr 30, 2009

Is there a way to write VBA code that can reference a range of data that may be moved on the spreadsheet by the User? For example, if I write VBA code to reference a range of data such as:

View 2 Replies
View Related
Jul 15, 2009

I need to know the function to move a named sheet to the end of the list so its the last sheet in the workbook. Worksheets("xxxx").Copy After:=Worksheets(?)

Im copying a worksheet and want to move the new sheet to the end but I dont know how to finnish it. I tried to do.

name3 = Worksheet.count + 2

Worksheets("xxxx").Copy After:=Worksheets(name3)

but I get an error and cant debug it.

View 2 Replies
View Related