Calculate A 30-day Moving Average Based On The Last X Number Of Entries And Date
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.
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.
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.
I would like to calculate the week number of the month based on a date.
Now my days would only include working weeks (Monday - Friday).
Supposed the date is 12/31/2012:
M 31-Dec T 1-Jan W 2-Jan TH 3-Jan F 4-Jan
Since it only occupies 1 day of the workweek, then it will be considered as Week 1 of January. If the date is 1/28/2012:
M 28-Jan T 29-Jan W 30-Jan TH 31-Jan F 1-Feb
It will be considered as Week 5 of January since it occupies 4 days of the working week. If the date is 4/29/2013:
M 29-Apr T 30-Apr W 1-May TH 2-May F 3-May
It will be considered as Week 1 of May since it occupies only 2 days of the working week.
Basically if the date's month occupies 3 or more of the working days of the workweek then it will be considered as part of that month's working week. Is this possible with formulas? I tried to explain it the best I can.
I have attached a workbook which I am trying to calculate the number of "Booked" entries there are between 0 -36 months, 36 -72 months, 72 - 96 months and 96 months plus, for some reason the 0 - 36 months is calculating correctly but the remainder aren't, the current formula that I am using is;
I have a table that looks like this (its basically a historical data of a stock exchange):
Date Index January 4, 2010
[Code]....
The List continues till the current Date.
I want to calculate Average Index Values of a Date of each month within a Date Range. Example: Calculate Average Index Values for 3rd of Each month from 1st Feb 2010 to 3rd Jan 2011. Formula should calculate Average of the Index Values for 3rd Feb 2010, 3rd March 2010, 3rd April 2010, 3rd May 2010, 3rd June 2010, 3rd July 2010, 3rd Aug 2010, 3rd Sept 2010, 3rd Oct 2010, 3rd Nov 2010, 3rd Dec 2010, 3rd Jan 2011.
Both the Date and the Date Range is variable. Also, the Index Value for selected Date of one or more month may not be available as that being a holiday. In that case, the formula needs to use the last available Index Value before that Date. e.g. If Index Value for 3rd Oct 2010 is not available, system will use the Index Value of 2nd Oct 2010.
I am calculating group averages for the following performers - very good, good, average, low, very low - for a series of factors.
Worksheet 2
Contains the same factors with the values for which Im trying to work out the average. Each factor has a performance rating above it, either very good, good, average, low, very low.
I need a formula which will match the performance rating from worksheet 1 (I3, J3, K3, L3, M3) to worksheet 2 and then calculate the averages of each factors based on those matches.
My attached files contains stock returns for companies. Each sheet contains the returns over a 5 year period for a certain stock, with the ticker symbol of the stock used as the sheet name. I want to write a sub that presents the user with a user form. This user form should have an OK and Cancel buttons, and it should have a list box with a list of all stocks. The user should be allowed to choose only one stock in the list. The sub should then display a message box that reports the average monthly return for the selected stock.
It's probably best if you open up the attached example spreadsheet that contains sheets "Before" and "After" otherwise it might be pretty difficult to understand what I'm talking about.
So I have this spreadsheet where I have part numbers in column B, status of the orders in Column I and lead times in Column M. What I want to achieve is to get of Mode (col N), Median (col O) and Average (Col P) values of each part number and each of its status using the lead times in Column M. If possible I would only like this on the top lines of each of these. I have done the formulas manually on sheet After so if you click on the formula you can see the range's it covers.
I did create a concatenation in column K (conc of column B and I) as helper column because in theory this might make things a little bit easier.
Now going into second part. I would also like to do something very similar but only using the part number and status "Finished" and get the values in columns T,U, and V on the top row of each part number. I made some blue boxes and arrows to kind of point out the idea.
The solution can be either formula based or macro, it really doesn't matter, however I will intent to use it within macro either way.
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.
I'm trying to workout how to take a known initial date a repeating frequency and work out the next due date from today.
Example
Initial Date :- 1st of January 2014
Frequency :- every 5 weeks
Current Date :- 9th of March 2014
Next Due date should be :-12th of March 2014 (if I worked it out correctly from my paper calendar)
I want to use a cell formula to do this for different initial dates and varying frequency periods (the frequency will always be whole weeks i.e. 1,2,3,4,5,6,7,8,8,10)
I need a formula that will calculate the number of days from a date entered into cell A1 to today's date. Whether it's before or after todays date. Example:
I have two columns of dates, leave start and end dates (when people start leave i.e. annual leave). Would need to introduce column(s) to calculate how many days fell within the month including the end date and excludes weekends.
For example, if the staff on leave from 31st March to 6 April, i need to show that the number of leave taken as 1 day in March and 4 days in April.
I need a formula that will calucalte the monthly total based on the following conditions:
Col A = Yearly Cost Col B = The number of the month when the costs are starting (1=Jan etc) Col C = The duration or the number of months for which the costs are to spread Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.
Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx
How to create a spreadsheet with what I think will be a very simple formula?
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2. If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2. If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2. If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2. If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
OR
Another, maybe simpler, way of saying it is:
If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2. If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2. If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2. If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2. If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.
Take a look at the attachment file. Those highlighted in yellow are entered by the user. What is the formula to calculate the End date in (A6) after the user has entered the start date (A2) & the number of weeks (A4)?
I currently have a sheet of 20,000+ items and 2 columns. My first column is an part number and the second is a date. Each part number can be show multiple times but will have a different date each time. What I want to do is that the first occurrence of each part number (and corresponding date) and put it in sheet 1, the second occurrence of each part number and date into cell 2, third in cell 3 and so on... not all part numbers are showing multiple times, only certain ones.
Column B with dates and column C with values. I'd like to make another column with the cell value averages based on the date. In essence, calculating daily averages. In turn I will later be adapting it to go through the all sheets in all workbooks.
Start date: 12/04/2004 End date: 12/04/2006 The formula should give the answer to 24 months
Example 2 Start date: 12/04/2004 End date: 13/04/2006 The formula should give the answer to 25 months
When I use function =(YEAR(A4)-YEAR(A3))*12+MONTH(A4)-MONTH(A3), it does not show 25 months for "Example 2" as it is still within the same month "April"
I was given the formula below to calculate the quarter to date number. The months from 1/1/05 thru 12/31/07 are in cells C3-AL3.
First - can anyone explain the formula so I may understand it and possibly modify it for other uses
Second - wherever the formula has 3:3 and I copy it to the next cell below it, I have to change the 4:4 to a 3:3 - any suggestions on how to do this easily.
By the way, the formulas are in column AO, staring on row 4.
I have the formula for calculating a 13 week rolling average, but I need to be able to set goals in the future and still have the average calculate from current and not from future.
I have my dates set up in row 3 (these dates are all mondays, not every day) and my data in row 4. I need the 13 week rolling average to calculate starting from the prior monday and going back 12 more mondays for the 13 weeks. IE: This monday I'm running reports that the info will be entered into the previous week's monday.
Here is the formula that I'm currently using: =AVERAGE(OFFSET(K4,,COUNT(K4:BV4)-13,,13)) The formula works but will calculate the average for 13 weeks prior based on the last cell with data in it. It's calculating the average of the last 13 weeks of next year.
how to make this work without having to remember my weekly goals every week??
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?
