Given Weekly Data Coming Up With Monthly Amounts
Sep 5, 2009
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.
A: DateB: MonthC: YearD: Weekly data
12/11/200612200619
12/18/200612200644
12/25/200612200650
1/1/20071200741
1/8/20071200737
1/15/20071200741
1/22/20071200741
1/29/20071200741
2/5/20072200732
2/12/20072200736
2/19/20072200740
2/26/20072200735
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..................
View 9 Replies
ADVERTISEMENT
Mar 13, 2012
I have the following setup:
52 weeks of 2010 cost with the following structure:
week number | wk1 | wk2 | wk3
week ending | 1/10/2010 | 1/17/2010 | 1/24/2010
weekly cost | 57,000 | 35,000 | 42,000
I want to be able to use the above data to present a summary, but instead of weekly, list the data on a monthly basis:
month | Jan | Feb | Mar
monthly cost | wk1-4 | wk5-8 | wk9-12+3days wk13
Any way to set this up automatically instead of having to split the weekly data as many months share the same weeks?
View 2 Replies
View Related
Nov 14, 2013
I have an excel 2007 script that downloads daily stock data and prices. I do analysis and graph the data.
I would like to keep the download the same, but modify my analysis so as to obtain weekly and/or monthly data.
I am sure this is commonly done, and is not rocket science, but so far I am baffled as to the procedure / algorithm to do this.
View 1 Replies
View Related
May 8, 2014
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.
View 11 Replies
View Related
Apr 7, 2014
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.
View 14 Replies
View Related
Jul 2, 2008
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.
View 4 Replies
View Related
Jan 2, 2010
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.
View 2 Replies
View Related
May 14, 2012
I've got data in E4:I4, Monday - Friday.
What I need to do, is add the total of those 5 figures in a total, but then clear the data in those 5 days.
Then when I input the data for the next week in to those days, I want it to add to the previos week.
So basically;
In week 1 the 'Total' is just week 1
In week 2, the 'Total' is week 1 & week 2'
I will only be going up to week 5. (only week 5 once in a 3 month period, so 2 4 weeks and a 5 week)
View 1 Replies
View Related
Jan 25, 2014
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.
View 2 Replies
View Related
Jun 1, 2009
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.
View 14 Replies
View Related
Feb 26, 2009
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.
See attached worksheet.
View 8 Replies
View Related
Feb 19, 2012
I am wanting to calculate a rolling monthly average and a rolling weekly average.
The following cells have the headers k2 has Allan, Cell L2 has Bill, Cell M2 has Charlie, Cell N2 has Don, cell o2 has Ellen and Cell P2 has Flora
Column J3 to J14 respectivley has Jan to Dec
The balance of the cells will have the data.
I then need to plot the rolling averages for each person on a gaph as teh months data is filled.
Below is the table:
Monthly Totals 2012AllanBillCharlieDonEllenFloraJan0.0000.0000.0000.0000.0000.000
Feb0.0000.0000.0000.0000.0000.000Mar0.0000.0000.0000.0000.0000.000
Apr0.0000.0000.0000.0000.0000.000May0.0000.0000.0000.0000.0000.000
Jun0.0000.0000.0000.0000.0000.000Jul0.0000.0000.0000.0000.0000.000
Aug0.0000.0000.0000.0000.0000.000Sep0.0000.0000.0000.0000.0000.000
Oct0.0000.0000.0000.0000.0000.000Nov0.0000.0000.0000.0000.0000.000Dec0.0000.0000.0000.0000.0000.000
View 1 Replies
View Related
Jan 24, 2013
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?
View 3 Replies
View Related
Apr 20, 2013
I'll use following as an example.
I work for a supermarket, I receive many fruits per day and I want to see what percentage of them are bananas, apples, oranges, watermelons etc.
Are there any templates of pie charts and bar graphs that I can use for this that will show the percentage of said fruits daily, weekly and monthly?
View 3 Replies
View Related
Aug 27, 2013
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
View 5 Replies
View Related
Mar 3, 2014
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.
View 3 Replies
View Related
Jun 26, 2014
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.
Monthly.png
File attached for reference.
330014857 DSNY Budet Detail.xlsx
View 3 Replies
View Related
Jan 23, 2012
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
View 4 Replies
View Related
Aug 3, 2009
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:....
View 9 Replies
View Related
Oct 14, 2009
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.
View 10 Replies
View Related
Dec 24, 2009
I have one excel sheet where I write a macro to create pivot table.
It was successfully ran and created the pivot table but there is no data in that table. Only headers are coming.
View 7 Replies
View Related
Jul 29, 2014
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.
View 3 Replies
View Related
Jun 19, 2007
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?
View 5 Replies
View Related
Feb 22, 2013
Column A: List of document numbers
Column B: List of amounts
A B
111 $5.00
222 $10.00
111 $5.00
222 $15.00
I need to merge all equal document numbers so that it shows the full amount on that document number. It needs look like this in the end:
A B
111 $10.00
222 $25.00
I realize a pivot table will do this but the formatting of a pivot table will not work with what I am ultimately trying to do.
View 7 Replies
View Related
Jul 10, 2014
I have a payment Excel sheet with thousands of lines Every line (one cell) has row payments data from mainframes like the following
Lhfskldhflshlshdflsd 234 234 sdljflsdjfkl 345.000,56 EUR lshflhshlfsld 2342 sdffgsd
Fsdlflsdf -384823 jsdlfsdlfjsdlfjldfjsldjf 43 50.000,56 EUR fsdgdfgdsfdg 23432
Jlflsdjflsjdfld 569 USD fjsldjflslfhsdlhdlshg 3-448 jlfsdkljfklsd
Etc.
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!)
View 4 Replies
View Related
Jan 1, 2009
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.
View 11 Replies
View Related
Mar 28, 2012
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.
View 1 Replies
View Related
Apr 7, 2007
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.
View 7 Replies
View Related
Feb 4, 2014
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.
Excel Problem 2.xlsx‎
View 1 Replies
View Related
Mar 8, 2014
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.
DCC foler.jpg
View 10 Replies
View Related
