Calculate A 30-day Moving Average Based On The Last X Number Of Entries And Date
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Date Function Query (calculate The Last Day Of The Month)
I am using a formula to calculate the last day of the month, using any date of the month in a worksheet in cell A13, this cell is also linked to another worksheet to pick up a date, using the ISBLANK function to prevent a dummy date entry appearing if the field in the linked ASHBY RISE worksheet is blank =IF(ISBLANK('ASHBY RISE'!$C$5),"",'ASHBY RISE'!$C$5) The last day of the month function is shown below =DATE(YEAR(A13),MONTH(A13)+1,0) This works fine if there is a date in A13, but returns a #VALUE! error if cell A13 is blank. I have tried using the ISBLANK function, but I am still getting the #VALUE! error. Of course I may have the sysntax incorrect.
View Replies!
View Related
Formula To Calculate Average Number Of Rebills Per Client
I need help with a formula to calculate average number of rebills per client. I don't know how to get excel to add the number of unique client in a given row. Example Column A Client 1 Client 1 Client 1 Client 2 Client 2 Client 2 Client 3 Client 4 Client 4 Client 4 Client 4 Client 5 Formula needs to calculate number of Unique clients. In this case, the answer is 5, but how can I get excel to calculate for me?
View Replies!
View Related
Calculate Average Based On Matching Values From Two Worksheets
Worksheet 1 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.
View Replies!
View Related
Calculate Average Based On Item Chosen From List
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.
View Replies!
View Related
Round Date Based On Day Of Date
I am trying to make a spreadsheet that will calculate how long someone has been working for us but what i need is for the date started to be rounded to the start of the nearest month. (ie. Start Date: Jan. 1 - 15, = Jan 1. and Start Date: Jan.15 - Feb.15 = Feb. 1....). Is there a way to do this? I figure i may have to use some sort of table but i dont know which one to use or how to use it.
View Replies!
View Related
Serial Number Date Displaying Incorrect Day?
I maintain a class register in Excel to monitor student attendance. The first row shows the date of the class in the form dd-mm. I need to identify all dates which fall on a Monday and thought that if I custom formatted a new row as "dddd" and enter the formula =DAY(cell ref) into the cells of this new row it would achieve this- I could easily spot the Mondays for the period under review. What I'm finding, however, is that the formula seems to incorrectly state that 16th September 2008 is a Monday whereas it's actually a Tuesday- utterly bizarre! I can get a fix simply by modifying the =DAY() formula by adding 1 to my formula [ie =DAY(A1)+1] but am wondering is this a "so called known issue" with Excel or has anyone else come across it? I have never previously come across this and consider myself to be an above average competency level user of the application.
View Replies!
View Related
Update Day Of Week And Date Based On Month
I'm attempting to force excel to auto update the day of the week, and the date in a spreadsheet. The date isn't as important, since it can be hard coded. The only problem there is some months have 31 days, some 30, and another with 28. I've uploaded an image of the spreadsheet, and you can see in field A1 the date/year is input. I'm wanting to find a way to force the days/dates in fields 2E and 3E to update based on the month.
View Replies!
View Related
Change Cell Colors Based On Todays Date/Day
I have a spreadsheet that I enter monthly expenditure on. Column A is expenditure during 24th to 31st Column D is expenditure during 1st to 8th Column G is expenditure during 9th to 16th and Column J is expenditure during 17th to 23rd Ive been trying to colour the columns grey if todays date is outside the above date ranges each time I open the spreadsheet so its obvious which column my expenditure needs to be entered into.
View Replies!
View Related
Average Based On Date Range
Having trouble working out a macro for this... 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.
View Replies!
View Related
Calculate The Quarter To Date Number
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. =SUM(OFFSET(A3,1,MATCH(DATE(YEAR(Date!$C$6),INT((MONTH(Date!$C$6)+2)/3)*3-2,1),3:3,0)-1,1,MATCH(Date!$C$6,3:3,0)-MATCH(DATE(YEAR(Date!$C$6),INT((MONTH(Date!$C$6)+2)/3)*3-2,1),3:3,0)+1))
View Replies!
View Related
Calculate Number Of Months Between Two Given Date
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"
View Replies!
View Related
Calculate Percentage Based On The Number
I am working on a spreadsheet which has lots of data in it. I have a Column i.e. Checked out and on each cell entered an X Mark indicating that a device has been checked out. Since this Checked Out Column goes all the way down to > 1000 cells. Is there a way for us to make a formula and calculate percentage based on the number of X's that are entered and tell as that out of 1000 cells, the X's are 65% and so the blank cells would have to be checked to complete the list?
View Replies!
View Related
Calculate An If Value Based On A Number Of Conditions
I am trying to create on excel order form. I want customers to be able to input the item # (a range from 1 to 12), then I want the to price to be calculated based on the item # they input. For example. If they choose item #2 in A6 then the price in F6 will be recorded as $8.00. (the price would change for each item # they input). the formula I started out with was: =IF((A6=1),"$8.00") this worked for me if A6 did in fact equal 1. So I tried adding this equation to the formula. =IF((A6=1),"$8.00")*OR((A6=2),"$7.00")....this would continue on. I even pressed "command return" after the statement as if I was entering an array formula. I got the error #VALUE!
View Replies!
View Related
Calculate The Average Of A Group Cells In One Column Based On The Condition Of Another Column
I'm trying to figure out if there is a formula I could use that will calculate the average of a group cells in one column based on the condition of another column. It's hard to explain, so I will show an example. All the data is on a one worksheet and I'm trying to show totals and averages on another worksheet. Location, Days 17, 4 17, 3 17, 5 26, 4 26, 8 26, 10 26, 7 On a different worksheet I would want to know what the average days are for each location. So is there a formula that I could use that will look at column A for a specified location number and then average all the days in column B for that location? I'm using Excel 2003 and have tried using the Average(if) but with no success.
View Replies!
View Related
AVERAGE IF Statement Based On Matching Condition And Date Ranges
I have created a spreadsheet which creates an average of feedback for trainers in a training company. The form adds up the feedback score into column L of the summary sheet and I have created a summary sheet which I want you use to calculate the average for each trainer. I have cobbled together an array formula which creates the overallaverage for each trainer based on the named ranges entered via the form. It looks something like this:
View Replies!
View Related
Calculate Workload Based On Number Of Devices
I work in IT Service company and we need to calculate our workload based on a number of devices in our scope. Say, somebody wants to add 300 Windows Servers to our service desk. We know that each Win Server would need 1 Person on duty to manage. So ideally we would just multiply 1*300 and get the amount of people we need. Problem is that because the service is quite similar for each of these servers, we dont really need a dedicated person for each device so I want to make every additional device consume 20% less workload than the previous one. In our case it would be: 1 Server = 1 Person 2 Servers = 1.8 People 3 Servers ~ 2.5 People
View Replies!
View Related
Calculate Number Of Hours Between Two Date/time Combinations
I am trying to find a way to calculate the number of hours between date/times found in separate rows. The attached data set will help to envision what I am talking about. For each couple of rows, I need to find a way to calculate the number of hours elapsed from row 1 to row 2. In the first example, to calculate the number of hours between 12/2/2009 8:56:51 and 12/4/2009 6:35:27.
View Replies!
View Related
Moving Average Dilemma
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 Replies!
View Related
Find The Moving Average
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 Replies!
View Related
UDF For Moving Average
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 Replies!
View Related
Calculate Number Of Dates Within A Column Based On Month
I have a column say column B for example that has a list of dates in the format dd/mm/yyyy. I would like a summary at the top of the columns to state how many dates there are for the current month. But I wondered if this was possible based on the TODAY() function or similar. Thus the user would not have to change anything. So for example at the start of the month it may state 14. Half way through the month down to 6 and at the end of the month 0 for example.
View Replies!
View Related
Calculate X Number Of Rows In Range Based On Cell Value
I can't seem to make SumIF or Vlookup do what I want here. I have a table like that below. I also have a cell on the same sheet called CurrentPeriod in which a user can enter a period number corresponding with one of the values in the first column. If someone enters 3 in "CurrentPeriod" I want to sum the first three values in the "Actual" column and then divide the result by the sum of the first three values in the "Target" column (effectively giving a percentage of target at the end of period 3) Period Target Actual %Target 1 74 68 91.9% 2 81 71 87.7% 3 76 87 114.5% 4 76 68 89.5% 5 71 89 125.4% 6 69 81 117.4%
View Replies!
View Related
Linear Weighted Moving Average
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 Replies!
View Related
Calculate Numer Of Paychecks Based On Date Of Hire
I have a business and i run payroll for my employees twice a month (semimonthly). Date of paycheck will be 16th and 31st. So if employee was hired on say 7/5/2009 then this employee will have 3 paychecks as of today (1st paycheck from 7/1/2009 to 7/15/2009 2nd Paycheck from 7/16/2009 to 7/31/2009 3rd Paycheck from 8/1/2009 to 8/15/2009) i need to know the # of pay checks for each employee computed in Cell C3 to C7.
View Replies!
View Related
Calculate Holidays Remaing Based On Start Date
I want to create a formula that will do the following Each worker is entitled to 21 days holiday per year this will run from 8 Jan 08 to 7 Jan 09. But if a worker starts say 15 Apr 08 he would be entitled to less than 21 days. I would just like to be able to put his start date in a cell and then automatically generate how many days holiday he would be entitled to from 15 Apr to 7 Jan.
View Replies!
View Related
Number Of Days To Add To Date Based On Weekday Of Date
I need to create a formula that states a delivery date when the order date is entered in an adjacent column. Items ordered on Monday, Tuesday and Wednesday will be delivered the Friday of the following week, eg. ordered 23rd April 2008, delivered on the 2nd of May 2008. Items ordered on Thursday or Friday will be delivered on a Friday 2 weeks later, eg. ordered on the 24th April, delivered on the 9th of May 2008
View Replies!
View Related
Moving Average Input Range Display
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 Replies!
View Related
Moving Average Ecluding Blank Cells
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 Replies!
View Related
Average Day Of The Week
I have a spreadsheet where column a = date, b = weekday and c= total calls. I have this array formula to get the true average for the entire month. {=average(if(isnumber(c4:c34),c4:c34))} The question is can I do something similar to get a true average adding in the day of the week? What I need is the average amount of calls per weekday.
View Replies!
View Related
Average For Each Day Of The Week
I require a function that calculates the average amount for each day of the week for a range of dates. I'm a little new at this and I though that something like this would work but it doesn't. The TEXT function doesn't accept a range so I don't know... =AVERAGEIF(,TEXT(,"dd")="01",)
View Replies!
View Related
Conditional Moving Average Omitting Adjacent Blanks
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 Replies!
View Related
Average A Set Of Figures Which Ignores 0 Entries
I need to average the figures in several cells. However some cells have a 0 in them. I therefore want the formula to ignore the cells which have a zero. I have used the AVERAGE & AVERAGEA function, but both count 0 cells. (although AVERAGEA ignores blank cells, I need to keep the 0s in as they are linked to another formula)
View Replies!
View Related
Auto-calculate Last Day For A Month
I'm working on a Calendar. One where all the user does is input the year, and the rest of the Calendar fills itself out, as to the days. Leap year is causing a small problem. There may be an easier way to do this (actually, I'm sure there is, but anyway), is there a way for a cell to automatically figure the last day of the month? IE: I put "2007" in a field. Another cell auto matically reads as "28" (last day of Feb for this year). Subsequently, when I enter "2008", the same field reads "29". The rest I think I got ok, but everytime I get a leap year, it shoves all my formulas down a cell, thanks to the extra day, and they're all off by one (February calendar showing last day as "01" from March, and "29" as the first day for March, from Feb).
View Replies!
View Related
Calculate The Unique Records By Day
I have downloaded data to an excel spreadsheet by day and need to calculate the unique records by day. Then all the daily totals should equal the monthly total if I ran the same date range for the month by removing the duplicates to get the unique records.
View Replies!
View Related
Calculate Day Difference From 2 Dates
is anyone aware of a formula/macro that can calculate (in days, hours and minutes) how much time has elapsed between two date and time stamps? For example [30/07/2008 00:30] and [01/08/2008 01:45], so in days hours and minutes it would look somewhat like this: 02:01:15
View Replies!
View Related
Calculate The Combinations Of Entries
(I am unable to use Colo’s HTML Maker to post this—I have noticed several newer members indicating the same thing). |....| 1 | 2 | 3 |4 | 5 | 6 | 7 | 8 | | A | Y | N | Y | N | Y | Y | N | N | | B | Y | N | N | Y | Y | N | N | N | | C | Y | Y | N | Y | N | Y | Y | N | | D | Y | Y | Y | N | Y | Y | N | Y | The answer to the table above is: 144 combinations (using "Y" entries only). The answer is not to do just a simple count of the entries in each row and multiply as this produces 360 combinations rather than the desired 144 combinations. In this example, combinations such as 1-1-1-1 or 5-4-4-3 would not need to be calculated, which is the reason the answer is not 360 combinations. The table always allows for 8 x 4 cell entries. It is possible that every cell can be selected (the answer would be 8!/4! in this instance); At least one entry must occur in each of the first 3 rows. In this example, if row 4 was excluded the answer would be 38 combinations (3 rows); I am looking for 2 formulae: The first one to calculate the combinations of entries in 4 rows and the second to calculate for 3 rows only.
View Replies!
View Related
|
TRACKER
