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.
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 trying to calculate some averages. What I have is 3 columns of data in A, B, C, also the "tasks" in A are in named ranges ex: "Award Contract" is a named range - "Task_Award" and "Confirm Updates" is a named range - "Task_Updates". I've attached a sample excel sheet.
I'd like to be able to create a macro to evaluate column A, and for every row in range "Task_Award", give me the average of the corresponding cells in column C and put it in the same range of cells in column B , then, for every row in "Task_Confirm" then give me the average of the same range of cells in column C and place the result in the same range of cells in column B. This is my very first post so I hope I am doing this correctly. I have 77 of these task ranges to evaluate and it will take a long time to do it manually. I'm thinking of a loop function.
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??
For those of you that love difficult ones, here's one..
I have a sheet that has a column of names.. Across the top row I have dates.. let's say from the 1st through the 31st.... in the body of the report i have different sales numbers and what I am trying to is the following...
1/1 1/2 1/3 1/4 1/5
Johnny 3 4 2 2 1
Becca 3 4 1 1 1
So let's say in this instance, mind you my real report is about 342 rows of names and stats.. Is look for Johnny between 1/3 and 1/4 and average those numbers between those 2 dates...
I've tried everything from sumproduct to vlookups and can't seem to get it right...
I have a series of employee variances and dates for the variances in two columns.
I have another section on the same sheet where I want to track the amount of variances & occurances for certain months.
attached is an example of what I am looking to do.
I collect unique prices each day. I am trying to find a way to determine the average of the numbers collected from the 21st of the previous month to the 20th of the current month. This formula will need to calculate for multiple months and years. So for example, I need Feb2008 average-which would be the average of numbers found between Jan21-Feb20, I then need Mar 08 average which would be data from Feb21-Mar20 etc. My spreadsheet is setup with the first column having the dates (ex. 01/01/08, 01/02/08 etc) and the second column containing the value for that particular date ($2.85, $3.00 etc).
As the number of days between the 21st and 20th change each month, I just can't seem to find a way to do it without a whole lot of manual effort.
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:
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.
Please find the attached 2010 version excel file <For a given date Time Range average .xlsx>.
I want to calculate the average value (Column C) for the given date (Column A) and given time range (Column B).
Values to be plotted in the color cells yellow and light pink / magenta.
Voting Results.xlsx
I'm working on a workbook that will track staffing patterns.
The workbook has three worksheets: Sheet1 "RCS", Sheet2 "HCT' and Sheet3 "Hidden". I've attached the workbook to this thread. The password for the form is "j".
On Sheet3 "Hidden" I have two tables that are set up to collect the SUM of columns on Sheets1 "RCS" and Sheet2 "HCT". I'm finding the SUM of each range by way of the background color. I've set up the following formulas and when the "data collection tables" are in the same worksheets as the original information, the formula's work perfectly:
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] ........
The following functions are pulling data from Sheet1 "RCS" and placing them into a table in Sheet2 "Hidden"
[Code] .......
I have two more functions that aren't working due to the fact that the source values are percentages and NOT plain numbers. The above functions work great for SUM but not for percentages. EXAMPLE--Let's say, 3 sub percentages it gives me the SUM of the 3 percentages (i.e. 85% + 100% + 100% = 285% instead of giving me 95%.
[Code] ........
How might I use the following functions to find the average of the source fields instead of the SUM?
based on user date ranges entered on sheet1, I'm trying to write code that will write each month of the date range on other sheets across the 2nd row. at this point I'm getting "object required" error at "Set DateStart = Cells(2, 6)"
I also want the date format to be mmm-yy (Mar 14) on the sheets even if sheet1 has a different format. I tried using sourcerange instead of DateStart, but that didnt work either.
Code:
Dim projStartDate As Date
Dim projEndDate As Date
Dim DateStart As Date[code]....
I need to compare three cells of random dates shown in Column E, F,& G with Row's H5:AK5, H7:AK7, H9:AK7 (the Dates to these rows is Static on row H3:AK3.) EX: ROW 5 has Start Date, End Date (1) and End Date (2). Compare Cell H3 between Start Date & End Date (1). If H3 falls between or equal to Start and End Date(1) then highlight cell H5. Proceed till AK3 (higlighting only the cells H5:AK5). Then compare cell H3 between or equal to End Date (1) and End Date (2) (higlighting only the cells H5:AK5). Then do the same for ROW 7 and ROW 9.
To make things a bit more difficult I need to have " WK#" in Row 14 (these WK# is on another tab called "Task" of the workbook) needs to be displayed in Row's H4:AK4, H6:AK6, & H8:AK8. EX: Compare Date in D15 between or equal to Start Date & End Date (1) then display Wk# in D14 in H4. Continue till all dates in
D15:Z15 are compared to Start Date & End Date (1) and WK#'s in Row D14:Z14 are inputted if applicable in Row H4:AK4, H6:AK6, H8:AK8. I hope this is not confusing. I can't seem to use the upload option so here is alink to download a jpg of the sheet
I'm trying to combine the hours that a employee worked on a single date, with one of multiple time periods that exist for that employee.
I have two sets of data.
Set 1 (hours)
Employee number, date, hours
12345, 1-2-2014, 6
12345, 1-3-2014, 8
12345, 1-10-2014, 8
Set 2 (periods)
Employee number, start date, end date
12345, 1-1-2014, 4-1-2014
12345, 6-1-2014, 1-2-2014
What I'd like to do is to add the start and end date of Set 2 to Set 1 for every row in Set 1
In above example the result should be like this.
12345, 1-2-2014, 6, 1-1-2014, 4-1-2014
12345, 1-2-2014, 8, 1-1-2014, 4-1-2014
12345, 1-10-2014, 8, 6-1-2014, 1-2-2014
I created a basic excel weekly budget and would like to know how much money I have as of todays date. on the top row I have a date range from Sunday to Saturday, so it looks like this:
09-15 16-22 23-29
with the month manually put in above it.
then below I have income and expenses with a Overall below that, so basically what I want to is see the Overall value based on todays date, not sure how to do this with the weekly range and automatic current date(which is =TODAY() as far as I know) I have attached a photo as a reference.
Budget Picture.jpg
I have an 'existing results table' as per my attached sample.
I have had help previously from this forum to create lists of 'sold' stock within date ranges (tax year periods) and these are represented as 'sold list' in my attached sample.
I now need to create a list of 'unsold stock' for each annual tax year end date; i.e. populate my table with items that have been created before the end of the date range and that have not been sold by the end of the date range.
Please can someone show me the formula on my attached sample?
Please see my example in red.
I am working with Excel07 however my attached sample is in Excel03 because I couldn't upload an XLSX file.
I like to create "templates" for files that I work with on a reoccuring basis, just to make things simpler. The current template I am working on tracks items processed by day and is used for two reporting purposes; 1 totals the pay ending items processed so I have the dates laid out based on the 2 week period with formulas for that. The 2nd way it is reported is by the month. Since this is a template, only the first day of the first pay period for a calendar year needs to be entered and the rest of the dates populate based on that date. What I would now like to do is add columns for January - December monthly totals using a formula that will read something like "if column A (where the date is) contains 1/1/2014 - 1/31/2014, then sum up column L" and than copy that formula for the remaining months.
View 8 Replies View RelatedWhat i'm trying to do is select a range based on the date which happens to be in the first column of the range.
For example, I have a column (A) that contains dates and column (B) that contains names.
Its easy enough to select the entire range using
Range("A1:B4").Select
I need someting that will analyse column A and conditionally selct the range based on the date in column A (ie. it will only select the rows where column A has todays date).
I am diabetic and track my blood sugar on a spread sheet I created. Each month is a page named for the month and year. ( ie: may07, June07 etc ) The last page is my averages page. I monitor averages from the begining as well as several 'last 3 month' statistics. At this time I have to go in each month and change the months on the '3 month averages' formulas then add a month to the 'over all' formula. I want to use a formula that will simply look at the last 3 months without having to manually change the months as well as the 'overall' formula automatically calculating "all" months without adding a new month every month.
And last but not least, I have some sub-total boxes giving a div by zero error with blank cells. What am I doing wrong? Not all boxes do this even though the code is copied and pasted so they all 'appear' identical. Rather than try to paste my code here you can see my spreadsheet at [url]
I would like to have cell G3 automatically sum the values in Column B based on the start and end date parameters that are typed in the cells (E3 and E4).
View 2 Replies View RelatedI would like to know the function/syntax for determining the due dates given certain date ranges.
See attached sheet for the example : due dates example.xlsx
I have 2 columns, one containing a piece of data and one containing dates. I want to count how many times the data is in the first column, based on a specific date range in the 2nd column. I have attached a sample SSF with a better explanation inside it.
View 3 Replies View RelatedI have data in a spreadsheet similar to the below.
[Code] ....
What I need to do is created a named dynamic range based on the current month which will contain all rows/columns within that range. Ideally I need it to create the range for the current month so I can then use this as the data source for a pivot table.
I believe it is something to do with index and match functions?
I have a bunch of open files with dates in cell A3. I want to copy the range starting in A7 (the same range and size in all the open files) and paste it into my vbafile based on matching the date (in row 11). I have a spreadsheet attach with an example of what the code would do for Jan 1.
View 5 Replies View RelatedI am trying to add a gantt chart feature to a project summary worksheet. Ideally I'd like to search Col C (Start date) and Col D (End date) for all projects, and based on the earliest start date (ESD) in C and latest end date (LED) in D, repeatedly insert columns labeled with the value of the ESD (ie Feb 6, 2011), increment by 7 (1 week), insert the next column with ESD+7 and continue on until reaching LED.
View 4 Replies View RelatedI have one worksheet with 2 columns: A - Dates, B - User IDs. In another worksheet I would like to have two columns: A - User IDs and B - Count. The count in Worksheet2, column B would be a count of how many instances the user ID in Worksheet1 appears before or on a certain date. Example:..................
View 2 Replies View RelatedOn sheet 1 in cell A2 I have a date (2/22/07). On sheet 2 I have 4 columns of data...column A has consecutive dates. I am looking for a code that will select the range that begins with the week beginning date of the date that was inputted on sheet 1 and the week ending date. For example, the code will select the range 2/19/07 to 2/25/07 (A20:D26).
View 6 Replies View RelatedI have a monthly calender, with each month on its own sheet. I have a sheet named Holidays, which list the holidays. I have been able to use conditional format and highlight the dates in the months the holidays fall on, but I would like to highlight a range of cells below the date on monthly sheet. I have attached a sample of what I have and would like to achieve. I am not much good at using VBA, but would not be against using it either.
View 2 Replies View RelatedI have a date in a cell that when it is less than today() (so yesterday's date) would turn red and the 5 cells to its immediate right would also turn red, if the date is still current then it would be green and so would the right hand cells.
View 4 Replies View RelatedI have a VBA or function query.
I have a spread sheet with the following.
Column A contains asset names
Column H to JZ contains cash outflows for each asset
Row 17 contains a Month&Date value (ie. July2013)
What i want to do is, from another sheet, is to calculate the sum of all cash outflows for the asset (determined by a cell value) for a date range entered. In other words, if i say 1/7/2012 to 30/6/2014, it will look the asset up, and sum the value of cashflows, in colums that fall between those dates for that asset.