Moving Average - Based On Last 5 Weeks
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
ADVERTISEMENT
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
Dec 3, 2012
In field A2 I use now() to get the actual date.In colom C8:C1000 I have all monday dates.In colom E8:E1000 I have the weekly sales figures.
How can I calculate the average sales of the last 10 weeks?
View 2 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
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
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
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
Sep 7, 2009
i currently have a what if statement
=IF(B2>=NOW()-42,"< 6 wks","> 6 wks")
I need it to also give an option of between 3 wks and 6 weeks
Therefore all options are
3-6 WKs
< 3 WKs
> 6 WKs
I have mastered 2 but now need a 3rd.
View 7 Replies
View Related
Feb 27, 2009
I have a spreadsheet that I am using to try to track Project Man Hours. What happens is:
I enter in the amount of weeks a Project Phase is in and when the Project Starts. I also enter in how many "Men" we are going to need.
i.e. Project Start - January 2009
Phase 1 - 4 weeks with 1.5 Men
Phase 2 - 20 weeks with 2 Men.
What I want to happen is:
Excel then adds that data to a chart showing EACH MONTH and the amount of men needed for each phase.
Project 1 showing a line chart with January-December and the lines correlating with the amount of "Men" needed each week based on the Start Date and Sequential Phase Week Amounts.....
View 2 Replies
View Related
Feb 4, 2013
Here is what I have and am trying to do:
I entered the date and day in this format in multiple sheets in workbook 1 (named 0101, 0102, 0103) from the start of the year to last night (B2:C4):
Date:January 01, 2013
Day:Tuesday
I then entered all the data needed below that (B5:E26):
TimeTransactionsTotal SalesAverage Sales
12:0039 $352.97 $9.05
13:0053 $416.98 $7.87
14:0044 $339.42 $7.71
15:0043 $304.96 $7.09
etc.....
In workbook 2, I have sheets named for each day of the week. I would like to take the information from the previous three weeks for each corresponding day from workbook 1 and put the averaged data into another table with the data all formatted the same in workbook 2. This way, each week when I go into the aggregate file (workbook 2), I can look at the chart I'll create and see the average of the past three weeks transactions, total sales, and average sales for each hour of the business day and be able to staff accordingly (assuming the trend continues).
View 1 Replies
View Related
Mar 26, 2014
Have been going round and round on this! I have a set of data that shows sales dollars by weeks. Currently, that data is in a Pivot table. I'd like to have a macro that selects the weeks (1-52) based on my input. For example. If I want to see weeks 5-52, I want to be able to input "5-52" somewhere and have a macro run that will select weeks 5-52 in the pivot table instead of having to go in and manually select each.
View 1 Replies
View Related
Nov 20, 2008
I have data as follows:
id | name
0 | axe
1 | sword
3 | pencil
4 | fist
5 | hammer
7 | gun
9 | rifle
Is it possible to move the rows to the row# shown in the ID?
- e.g. pencil gets moved from row#2 to row#3
View 9 Replies
View Related
Jun 27, 2014
following code is supposed to check whether cell contains a value and based on that either display or hide a shape. The last couple of lines are moving it. What I would need is to check all cells in a row (not only the first one) and if lets say first 1,2,3...10... cells contain a value move the shape all the way to the end of that cell and so on.
VB:
Sub Worksheet()
If Sheets("Text").Range("z6") <> 0 Then
With ActiveSheet.Shapes("Rectangle 8").Fill
[Code].....
View 4 Replies
View Related
Oct 8, 2011
I have a list of data which which has repeated supplier numbers in column A. For instance
A123
A123
A123
X456
x456
x456
x456
The information in other 13 columns is variable and I need to be able to move all the rows which start with the first supplier number to another sheet which I will rename to that name, then move the second set of supplier numbers to a second sheet etc. So in my above mini example I would have one sheet named A123 with 3 rows of data and another sheet named X456 with 4 rows of data.
The number of supplier codes will be different every day and the number of rows within each supplier number will also vary. Each new datafile that comes in daily creates a new file do I do not have to worry about existing values only what the current file contains. I have used the following code to move data based on a known constant before but dont know how to tell the macro "move all the data while the value stays the same"
Sub DiffOrderNo()
Dim totalrows As Long, row As Long
Cells.Sort Key1:=Range("A1")
totalrows = ActiveSheet.UsedRange.Rows.Count
[Code] ...........
View 3 Replies
View Related
Jun 17, 2014
I am a production manager for a homebuilder and I am trying to create an Excel spreadsheet that will allow me to input a date in a column and have a second column dependant on the date and move as I change the date.
Since no work is done on weekends, I'd like the output column to skip weekends and move the input to the next available weekday. I have conditionally formatted the date to be a red fill as a visual cue but I'm not sure where to go from here.
View 7 Replies
View Related
Jan 30, 2013
I was wondering if i can cut & paste rows to a different worksheet ("sheet2") based on a criteria my current loop is as follow need filling in the code below for if then ..
Sub moving()
Dim x As Integer
x = 0
Do While x < 12
x = x + 1
Cells(x, 4) = ""
Do While Cells(x, 4) = "absent"
If Cells(x, 4) = "absent" Then .....
View 9 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
Feb 11, 2009
I have an excel workbook with 5 sheets, the first called 'data drop sheet' & the others called 'Louise', 'Thor', 'Peter' & 'Steve'. In the data drop sheet, the first row is header data and underneath (A2 onwards) are rows of data that I have cut & pasted in. The first column (A) of this data will be the person's name, e.g. Louise Wilson.
I want to be able to move the rows for each person to their respective sheets, starting from row 2 as the first is a header. So, for example, if 'data drop sheet' cell A2 says Steve Brady, I want it to copy the whole line to sheet 'Steve' row 2 etc.
View 5 Replies
View Related
May 30, 2012
I'm currently creating a formula to look up the date based on the alphanumeric codes, my problem is if it crosses the year, say 2011 & 2012, the lookup will be distorted because codes for jan. 1 2011 & jan. 1 2012 is the same. Our alpha numeric code is A01= Jan. 1.
to elaborate further:
wanted to get the ageing days between todays date and item manufactured date:
today: May 30 2012
when the administrator enters the manufacturing date code of "A01" I have a look up of the code that equates A01=Jan 1. which will compute the difference of May 30 2012-Jan. 1 2012 and give me the Age date of 150. Now, if I enter H1, I wanted to have it look up at the 2011 not 2012 of Aug. 1; is there a look up formula that looks up the date already past (current date below), not the future date of under the same database?
View 5 Replies
View Related
Nov 30, 2012
I would like to make a macro that will move rows from one worksheet to another worksheet.
I have an excel spreadsheet with multiple columns and rows, within one of the columns there is some text that refers to SOS or County, I need to move everything that has SOS to another worksheet title UCC SOS and everything that has county to be moved to another worksheet title UCC County. Here is a sampling of the worksheet
Account
Primary Borrower
Corp
Status
[Code]....
View 7 Replies
View Related
Jan 14, 2013
I am having difficulty expressing a formula so that if a cell is greater than zero, it adds the value of that cell to a String figure. I have got as far as the following, the 'problem' area is in red.
Sub Share_Sales3()
Dim Prompt As String
Dim Caption As String
[Code].....
View 3 Replies
View Related
Sep 4, 2009
I have a column of data. Some are "--" and others have a number of 1-100.
I need to average the top ranked 90% of that data without including the "--" in the average. Is there a function I can use to do this?
View 9 Replies
View Related
