Automatically Split Weekly Data Into Monthly Totals

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


ADVERTISEMENT

Adding Weekly Totals To Monthly?

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

Summing Daily Weekly & Monthly Totals

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

Automatically Create Monthly Totals From Daily Data

May 20, 2014

I am trying to do an automatic input with my daily total sales.. i did it in total but my problem is i don't know how to make the date change.

View 2 Replies View Related

Automatically Create Monthly Totals From Daily Data

Jan 2, 2009

In column A I have dates, in column B I have data.

What I am trying to do is get the monthly totals from the data so instead of:

01/02/08 - 52
06/02/08 - 87
14/03/08 - 23
12/13/08 - 12

I would get:

Feb 08 - 129
Mar 08 - 33

View 6 Replies View Related

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 View Related

Excel 2007 :: Modify / Transform Daily To Weekly Or Monthly Data

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

Formula To Convert Monthly Data To Weekly Data

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

Daily Data Conversion To Monthly Totals

Sep 9, 2013

I have a daily rainfall data and I want to convert it to monthly totals. I tried the pivotal table but the arrangement of the data seemed to be confusing (i.e the year, month and day are in different columns).

See the attached data: Raindata_excel_forum.xlsx‎

View 9 Replies View Related

Copy Data From Weekly Report To Monthly Report

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

Time Sheet - Calculate Bi-Weekly Totals

Feb 25, 2014

I have a time sheet that I can figure the daily OT and DT on, but how to calc the the Bi-Weekly totals.

I can only have 80 hours per week, the rest needs to be carried to the OT field, but I can only have 40 of OT per week and the rest needs to be carried to the DT.

View 1 Replies View Related

Calculate Weekly Totals From Daily Figures?

Mar 4, 2012

I have a spreadsheet called PRODUCTION, where information is entered daily. I need weekly totals (mon - sun) to be extracted into another worksheet.

View 7 Replies View Related

Creating Monthly Data Table From Weekly Data Table

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

Calculate Rolling Weekly And Monthly Average

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

Consolidated Monthly View Of Weekly Cost And Labour?

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

How To See Percentage Of Items Received Daily / Weekly / Monthly

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

Count Data Monthly Automatically

Dec 3, 2012

I need a formula that i can put in cells that can count datas automatically each month. The data that i have is huge and it updates everyday. As an example i will explain it simply.

Lets say i have a list of product groups in column A. Column B is the date where the samples arrived, Column C is the approval, Column D is the sending date.

I probably need to post a picture of the table: [URL]...

sample workbook: Attachment 197668Attachment 197668

So as you can see, i need to a formula to count the percentage of "approved samples", "not approved samples" and so on (for month december)

The approved sample for month december should have these requirement: If the date of arrival is in December 2012, if the approval says "approved" and NOT EMPTY,if the date of sending is NOT EMPTY.. So from the table, the answer is 2.

The percentage should be the 2, divided by all samples (Telephone) arrived with approval, doesnt matter if the date of sending is empty or not. (which is 4)

I would want a formula that can do this automatically monthly, so i dont need to do the formula time and again.

View 14 Replies View Related

Formula For Monthly Totals

May 20, 2009

I need to create a running monthly total formula for a worksheet. This is something i need in the data, so i can manipulate it without using a pivot table.

I think I would use the SUM, MONTH, and IF formulas, and maybe EOMONTH.

I guess where I'm stuck is figuring out how to make conditions for my SUM formula. So as the dates go down the page, I would like a running total in a column to the right, that will also restart with every new month.

Attached is my example.

View 6 Replies View Related

Calculating Monthly Totals

Feb 24, 2006

best formula for adding figures in a column that correspond to each month of the year?

I have a sales register with the date of sale and commission on each row but want to display the total commissions for each month of the current year on one worksheet and monthly totals for previous year on athother worksheet.

View 10 Replies View Related

How To Get Dynamic Monthly Totals

Nov 26, 2011

Only recently have I really stumbled upon some of the more advanced functionality of excel, and I was literally blown away. I'm now trying to learn more and more about it.

[URL]

I need the Monthly totals of Apples, Oranges, Apples & Oranges.

I know how to select it by hand with the:

=SUM(, hold shift, mouse select the apples for March, and do the same for the other columns.

But how do you do it via a formula? Also, is it possible to make it dynamic? i.e. you add more transaction dates later, and the whole spread sheet gets updated automatically.

The only way I see how to do that is to write a loop that goes through all of the records. How would such a loop look like? Or is there a better way?

View 4 Replies View Related

Horizontal Weekly Date Values Aggregated Into Single Vertical Monthly Value

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

Monthly Totals To Display Simultaneously

Apr 10, 2007

Spreadsheet is made up of Col A - Col AK:

Col A = Purchase Date

The info is continuous:

Row1 12/1/06
Row2 1/2/06
Row3 3/5/07

I need monthly totals to display simultaneously. Do not want to use Subtotals option.

View 9 Replies View Related

Delivery Report Summary - Monthly Total Sheet On 4 Individual Weekly Sheets?

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

Totals Based On Monthly Figures From Last Year

Jun 3, 2006

I need to make a sheet that give totals based on monthly figures from last year.
Then based on difference between the 2 it will show no increase and no bonus, or it will show an increase and bonus based on increments $75.

One month Last year the store made 31.82% on it’s money.
The bonus for anything over is $75 per 10% increments.
Ie
0.00% $400
0.10% $475
0.20% $550
0.30% $625
0.40% $700
So this month was over last months, 37.18% so the bonus was $3,975.

What formula do I use to make this calculation shown under % is nothing but anything over adds up to a relation with $75. added to a base of $400 every 10%.
This hurt my head trying to get it right and im new to this more complicated formulas.

View 14 Replies View Related

Average Of Monthly Totals In Pivot Table

Sep 22, 2011

How can I get a pivot table to calculate the average of the monthly totals and not the average of all the raw data.

Below is a pivot table of the data on the left. When the pivot table averages the months it calculates the average of every single record, 48.83. How can I get it to average the monthly totals:average(apr,may,Jun)= 179.05

Date cost Sum of cost Years Date
4/1/2011 -75.00 2011

2011 Average Monthly Average
4/11/2011 -58.88
Apr May Jun

[Code] ........

View 1 Replies View Related

Monthly Totals *without* Pivot-table Grouping

May 20, 2008

I have an expense ledger that looks essentially like this but spans seven months and counting:

HTML 18-Apr-08pizza$10.00Food
14-Apr-08book$12.50School
13-Apr-08milk $3.99Food
13-Apr-08soap $3.99Grooming
I have been using pivot tables with great success to summarize my data according to categories, payment methods, etc. But now that I want to track monthly spending for each category, it is not working out. Grouping the date fields doesn't work for me for the following reasons:
1. After grouping, the pivot table treats Jan as the oldest month for sorting purposes even though it isn't so in my data set.
2. Based on #1, I believe that the pivot table’s monthly grouping works like the month() function, which returns the month without the year attached. This will not work for my data, which will likely span more than a year eventually.
3. When I choose to group the date fields in one pivot table, they also get grouped in other tables where such grouping is undesirable.
Can I use sumif() or sumproduct() in conjunction with a pivot table or some other way to tabulate my monthly totals?

View 9 Replies View Related

Date And Time Differences With Blank Cells And Monthly Totals?

Jan 30, 2014

see the attached workbook with two different sheets (same data) using different formulas. Each has problems (red text) preventing me from moving forward.

I need to calculate time in level 1, time in level 2 and total time for each row and then be able to sum for the month. Unfortunately, sometimes there is missing data, but I still need to calculate everything possible using a consistent formula that can be applied to the column/row universally.

View 3 Replies View Related

Weekly Numbers Rolled Up To Monthly Numbers

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

Converting Weekly Rankings Into Monthly Rankings

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

Adding Weekly Dates Automatically

Mar 25, 2009

how i can automatically add a week to a start date i have in a rotas for the whole year?.

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved