I am to the end of my wits - or maybe it's impossible to do the following with formulas?
I have the data like this:
Column A: Date (which is basically the date for the beginning of weeks)
Column B: Month of the date in Column A
Column C: Year of the data in Column A
Column D: Weekly data.
Maybe it's because it's Friday night, but I just can't invent how to do the following:
Create a new column E that would contain the monthly sum of ColumnD across all weeks of this month - but entered only against the first week of that month (that is currently in Column A)
I.e., in my example it should be:
113
empty
empty
201
empty
empty
empty
empty..................
I have 12 observations per variable over 2013. At the end of each month I gathered the average value of X of said month. What I want to do is convert these observations to a list of weekly observations, where each week in January gets the value of the January observation
Example:
Month123456789101112 Value 2345678910111213
And get it like:
Week Value 1 2 2 2 3 2 4 2 5 2 6 3 7 3
and so on.
Since I've got a lot of variables I'd like to use some kind of formula.
How to write a macro that will sum daily figures into monthly figures? On the attached spreadsheet I would like to take the daily figures on the amounts on the VRU DAILY worksheet and sum them in the appropriate month on the VRU # sheet.
How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.
I need to gather total dollar amounts from a column based on their date/month entered.
I have a sample attached, in cell L2 for example I would need to gather all the dollar amounts in Column G that were entered for that month in column B that correspond to the month in column K.
I have a table of data which represents data in different categories by week.
My ultimate goal is to have another table representing the data for each month - for instance - for each of the categories the data for :
06/04/2009 13/04/2009 20/04/2009 27/04/2009
is summed to make the data for the month of April.
The way I'm doing this at the moment is very long-winded
I'm using a whole new table - the size of the original weekly one - for each month. A calculation decides whether to effectively leave a cell blank or insert the appropriate data based on a date being within a particular range.
So in each "month table" there is the same list of week values:
06/04/2009 13/04/2009 20/04/2009 27/04/2009 04/05/2009 11/05/2009 etc.
but for each "month table" only the cells adjacent to the dates within the relevant month will return actual numerical values within them
This is an example of the forumula I am using in these tables: =IF(AND(($AD7>=MIN($AE$5,$AE$6)),$AD7<=MAX($AE$5,$AE$6)),$C7,"NA()")
Then a master table sums the totals for each month.
I want to be able to keep this table but get rid of the ones for every month as the sheet is getting unwieldy!
I have tried several times to attach the sheet for clarity but each time upon trying to "Submit New Thread " I am getting page not found errors - the sheet is only 133KB and I have tried zipping and sending also - I can't make it any smaller.
I assume there's a database (or pivot table?) solution for my task, which is to detect the days, weeks and months within a very long table, and obtain the totals of the data therein.
I am trying to build a Project Labour tracking sheet and would like to create a consolidated (monthly) view of labour spent over the life of the project. I have created attached sample sheet to explain it more clearly. Is it possible to have month by month view of individual teams for actual and forecast tracking in Proj Summary tab?
I can't seem to figure out to easily aggregate & automate a series of weekly values (ie. forecast volumes by a specific part number) into vertical monthly summaries, by product.
Here's the before picture:
column A column B column C column D column E column F Part num 1/1/2013 1/8/2013 1/15/2013 1/22/2013 1/29/2013A 100 100 100 100 100B 150 150 150 150 150C 200 200 200 200 200D 250 250 250 250 250
The output after manually doing this would look like this:
column A column B column CPart num Date QuantityA Jan-2013 500B Jan-2013 750C Jan-2013 1000D Jan-2013 1250
We have a weekly "On-Time" report that shows early, late, on time, or no data. Each week has a total percentage of early, late, on time, or no data deliveries. I now need a monthly total on a seperate worksheet, but for some reason I can not get the percentages to total correctly. I am attaching the spreadsheet.
I maintain two tables for forecasting hours. One is by week and the other is by month. I'd like to 'automate' the monthly roll up, by summing the weekly totals that fall within a particular month, but not sure how to do this.
This is the weekly table that has a column per week where i update actual hours worked.
weekly.png
This is the monthly table that has a column per month. I'd like to sum up the hours for a month here, and not have to hand count and enter for a given month.
I have been struggling to convert an array of weekly data rankings into monthly rankings using the closest date to month end and cant seem to find a way. see below
spx indexspgsci Indexief us equityspx indexspgsci Indexief us equity 24/01/201212431/12/2011???20/01/201213430/11/201113/01/201213431/10/2011 6/01/201213430/12/201114323/12/201114316/12/20111429/12/20111322/12/201113225/11/2011132
How do I convert them to a monthly rank18/11/2011132using the closest to month end date 11/11/20111324/11/201113228/10/201113221/10/201113214/10/2011132
I’m currently pulling data into two columns labeled “Monthly” & “Non-Monthly” respectively. They indicate work orders with a frequency of “Monthly” or “Non-Monthly”
The Monthly data is obtained using the following formula:....
I need to copy the values of a range on the weekly sales worksheet to the monthly sales worksheet. The last column is the total on the weekly sales. Part of the heading of the total column is the week ending date (e.g. 10/17/2009. On the Monthly Sales I have the months in columns by week ending (e.g. 10/17/2009).
Range I4:I28 to the monthly sales worksheet by date.
I'm using Excel 2008 for Mac & cannot figure out how to add a data label to an XY scatter plot that comes from a 3rd, separate column.
I have 3 columns of data: (A,B,C) Labels, X values, Y values
When I select the Data Source for the Chart, there is a greyed out box for Category X axis labels, which is where I remember such information going in PC versions of Excel I used to use.
From the formatting palette, the only options to select for labels add the values of column B, but I need the reference from column A.
I'm not familiar with macros or visual basic. How to add these labels? This is Mac, Excel 2008.
In the attached workbook, the CASTINGS worksheet has a SOH ( stock on hand) column. It looks up the SOH from the SOH worksheet (column 3) for each product on order for that week.
But how do I make week 2 use the SOH figure minus the previous weeks order, IF there was an order?
And so on until week 52.
The problem I have found is what if that product wasnt ordered for the last 4 weeks? Maybe I need a temp worksheet with all the products listed for all the weeks and a running total of SOH weather it is ordered or not?
Is there a way (UDF or Macro) to extract from every line(cell) (and put them in the cell to the right) these raw data only the amounts with the Currencies.
PS. (most of the times the amounts mentioned before are the biggest number in every line!)
want to be able to take a large quantity of data, sort all the like data together, and then quantify the number of each like data. I need the equations to do that.
I am looking for a functional way to break up very large amounts of data. I am looking to break them up by an ID number and then by date. The date function needs to break up data from a hire date to the closest date to a year without going over and proceed to do that for multiple years. I am hoping that the function can just add a blank row between the split data. The file that I have not comatins three years.
if there was a way I can put it in to some kind of pivot table, because the hardest part for people when they read his list is it's so large it's hard to find data easily.
So this is how he formatted his data... I was wondering what would be the best way to get this list in to a possible pivot table. This is a consilidated example, there are plenty more columns, but this will give you an idea of my problem.
A2007 A2006 A2006 B2006 B2006 B2007 Feb Jan Feb Jan Feb Jan 630 Labor Cost 1000 7500 3000 4500 800 5000 624 Equipment Cost 900 50 40 300 20 1400
Now, the only thing I can think of is to make columns, but then I'd have to recopy all the task names (which there is about 700) for each of the different years (A= Actual, B= Budget, F= Forecast). Is there any other way that you can think of to do this with out making it so complicated? Any help or suggestions would be great. I really want some format that allows you to click the total and it goes to what makes up that total.
I have attached a tiny part of a massive data set I am working on. As you can see in column 2, the data is roughly every 15min for 5 days. The data I am interested in averaging is color coordinated in column 3 (if you scroll down you can see a different color for each day's data set.)
In column 5 the dates are summarized into days as opposed to the 15min breakdown. In column 6 is the problem. How do I get the averages of the relevant data in column 6 in such a way that I can drag the formula down and the next cell will automatically calculate the average for the NEXT day, REGARDLESS of how many temp readings there are, as this data fluctuates from day to day.
We have folders of daily cash collections stored in such a manner, yearmonth. In every month, we will have worksheets sent by the end user to the finance dept, naming it using mmdd. (The folder in the drive will reads: C:Daily Cash Collection2013), (C:Daily Cash Collection2013 0104.xlxs), (C:Daily Cash Collection2013 0115.xlxs). I intend to put the master list outside the year folder, meaning, in the Daily Cash Collection folder (C:Daily Cash CollectionDCC_2013.xlsm). When I have a new folder for year 2014, my master list will be here (C:Daily Cash CollectionDCC_2014.xlsm)
I am looking to automate this opening of all the daily worksheets, select all data except the header row, and copy it into a master list (which will be data for the whole year, with 3 months of the previous year data).
The data in the daily sheets, it will have collections of the same Debit Note number from the file sent earlier. Meaning, if the file was sent on 0104, there is a DN0114-0002, collection of $50. In another daily sheets 0115, it will also have a collection of DN0114-0002 of $20. This 2nd information of $20 will also need to be captured as the payment in 0301 is partial and incomplete.
I will need to copy the daily sheets into the master list every now and then. Is there a way to check and copy the daily sheets and not repeating it and missed out one?
In another words, if I had already copied Jan sheets into the master list, will it look for the next worksheet that I had not copy and copy according the DN number? (it will be in running number but sometimes will have DN of the previous month due to the partial payment ealier).
Alternatively, if this is too complicated, how to insert a macro to copy all cells except the header (will be in fixed column and the first row will be fixed) from an open daily sheet, find the last row in the master list and copy it to the master list, and after copying, unclear the selection of the daily sheet and close the daily? Where can I put this macro as the daily sheet is from the end user. I can only put it into my master list, but my problem is, the file name of the daily sheet is not fixed, it depends on the day the end user saved and email the data to Finance Dept.
