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


ADVERTISEMENT

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

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

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

How To Create Table Depicting Trailing Monthly Data / Statistics

Dec 6, 2011

I am improving an old spreadsheet that contains a tab for inputting the following data, all using data validation:

date
employee (who did the work)
team (for whom work was done)
department (for whom work was done)
type (of work done)
Project (which property worked on)
new/revision (was this a new project or revision of old)
time (spent working on this)

I have a sheet with a drop down in which you can sort by employee and the associated chart and graphs adjust the information accordingly.

I need to create a chart and graph in which I can track the trailing month as well as year to date. Ideally, I would like to sort by the following:

- This month (i.e., the 1st of the month to present)
- previous month (etc., be able to select all previous months for which data was entered, i.e. Jan., Feb. March. etc.)
- Year to date

I know there is a way to do this - I thought maybe it would be through some formula incorporating =today() and subtracting back to what you need using numeric dates?

View 1 Replies View Related

Excel 2010 :: Sales Daily Sales Data To Be Aggregated Into Monthly Data (not Using Pivot Table)

Mar 27, 2013

I cannot solve with Excel 2010 and I have searched all over for the answer.

I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.

For example, I would like to turn this:

3/2/2005 $xxxx
3/5/2005 $xxxx
3/20/2005 $xxxx
4/2/2005 $xxxx
4/10/2005 $xxxx

Into this:
March 2005- $xxxx (monthly average)
April 2005- $xxxx (monthly average)

I have a feeling some 'countif' formula would work but I am not sure how to do this.

View 4 Replies View Related

Creating Dynamic List Of Data From Expanding Table Of Data?

Feb 13, 2013

I need to create a dynamic list from a table of data.

I have performance data for 110 different pumps. Data points are generated every hour, and the table is updated with new data periodically. I want to automate the population of a list of 6 different pumps, and specify the date range populated. To put it another way: I want to place data from Pumps 1 through 6 for all of November into Columns A through F. Then I want to clear that data, and show data for Pumps 105-110 for last week in those same Columns.

One of the main goals of doing this -- other than quickly narrowing a field of data -- is being able to quickly chart this data on a scatter plot with a custom format. The pumps are grouped together by region, and individual pumps have specific purposes. So I need to quickly generate graphs with a series' color scheme or formatting that is consistent and logical between different pumping regions.

I haven't been able to make pivot table work because of the graphing issue, and also because of the way it handles data points and presents data. I would like to make this work with excel functions and maybe some filtering, but I'm not opposed to figuring out a VB script if you think that's what I should use.

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

Creating Table From Data From Several Other Tables

Jan 10, 2014

My family owns a Fast Casual Middle Eastern/ Mediterranean Restaurant. I'm taking charge of making it as profitable as can be! Our plates are different combinations of kebabs, rice, Falafels, Gyros, salad, platters. So theirs tons of combinations.

I am Dead serious about knowing REAL plate costs:

I spent days entering price data, weighing meat, produce, calculating usable yields. I then built "component builders" using data validation drop-downs, and Index + Match functions to construct the costs of the recipe items,standard base items and side items. So I now know How much a skewer of Marinated Kebab Costs me, and all the Components that go into building a plate.

I've now built a "Plate Builder" (See Pic) to create the EVERY combination available at our restaurant. So when I'm done filling in all the builders, I'll have a whole lot of tables. I'd like to pull the name and plate cost from each table and create a big table organized by whether its eat in or take out. How do I do it.

TL;DR: Need to create a table that pulls data from several other tables....

View 2 Replies View Related

Creating A Drop Down List From Another Table Of Data

May 6, 2009

I want to put a drop down list in Cell D3 with the list of names from column B. There are 14 different names, but when i try and create the list it gives everyname from Column B.

Also once the drop down list works i would like the table to be populated with the managers supplier, so if Dan was chosen in D5, Suppliers 1 to 9 would appear below D3.

View 7 Replies View Related

Creating A Table From Various Results Of Data Validation

Nov 17, 2006

See the attached:

1/ Need to be able to populate a table with the following data (or as illustrated in ' Sheet 1'.

Company Name, Year, Manufacturing, Non-Manufacturing

This data will need to come from the lists for each of the variables as opposed to from the table, as this will be hidden from the user. Also, more company details will be added over time.

View 4 Replies View Related

Creating Correct Pivot Table Data Fields In VBA?

Aug 1, 2012

Basically I've jumped straight in and have tried to recreate a report I need to produce which requires a pivot table. I have managed to create the code I need to produce a pivot table in a new sheet with all of the headers available for analysis. Where I'm struggling is in getting the pivot to show the datafields correctly.

The list that I am creating the pivot from has about 30 headers. 20 or so are analysis fields with the remainder being various figures; monthly plan, monthly actual, monthly variance etc..

The pivot table I need to create needs all analysis fields available to the end user for manipulation, which I have managed to do. I have created rows for sales areas, also with no problem. It also needs the above plan, actual and variance fields in columns and as datafields. This is where I'm having trouble. When I create the pivot manually it ends up with 1 row and 3 separate columns with summed data in each. I have checked the VBA code, copied it and used it again. The pivot table comes out with 1 row but with the data fields stacked one on top of each other. Any minor alterations that I can think of, (for example adding a position = 1, position = 2 etc.), either creates a chaotic looking sheet or an error.

I've checked various posts on pivot tables but am having trouble understanding the way in which I can correct this. Is there a straightforward command that I'm missing? Do I need to start declaring variables for pivot items?

View 5 Replies View Related

Creating Table On Sheet2 From Copied Data On Sheet1

Mar 17, 2014

Below is a snip-it of VBA code that I am using to try and create a table on Sheet2 which I have named "Summary Unsorted". Sheet1's name is "Diagnostic Measures". The range name, RawDataTable", is what I am copying from Sheet1 and pasting the values onto Sheet2 and want to create a simple table. The error I am getting is "The worksheet range for the table data must be on the same sheet as the table being created."

'Copy/Paste data from Dianostic Measures worksheet (Sheet1)
'to the Summary Unsorted worksheet (Sheet2).
Range("RawDataTable").Copy

[Code]....

View 9 Replies View Related

Creating Horizontal Summary Table From Consecutive Vertical Data Lists?

Jul 17, 2014

I have consecutive vertical lists that each have a different number of instances and I'm trying to create a horizontal summary. Here is an example:

Vertical lists:
Mary
red 2
blue 11
John
yellow 5
red 7
blue 8
Susan
red 9
green 3

Desired Summary table:
Mary John Susan
yellow 5
red 2 7
blue 11 8
green 3

Is there a way to do this using a pivot table or formulas, instead of manually?

View 3 Replies View Related

Producing Table Of Monthly Values Based On Monthly Growth Rate And Yearly Total

Mar 6, 2013

I have a table of yearly totals for the amount spent by x. I also have a growth rate for each month so for example in 2001 in jan the growth rate might have been 0.3% and feb 0.5% What I want to do is for each month based on the growth rate and the total produce a value for each month which sum to the total amount. It's also important to note that it restarts each year.

Link for excel file is here: [URL] ...........

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

Excel 2010 :: How To Auto Update PowerPoint Table When New Data Entered Into Table

Jun 12, 2013

I have created a table in Excel 2010 (pls see attached table named post.xlsx).

Then copied the above table into PowerPoint 2010, using "paste link" (I tried to attach the PowerPoint file but the system says "invalid file type" and I cannot attach it).

Question:

I have received income data for another month - the new month is 13 and the corresponding new income is 100.
I typed 13 and 100 into the Excel table post.xlsx and thus extended the table by another column.

Then I went back to PowerPoint slide, then right clicked on the table there, then clicked "update link".

Specific Question:

The newly-typed column in Excel table is not get updated in PowerPoint table.

View 2 Replies View Related

Import Data From Access Table To Pivot Table - Enable Auto Refresh

Feb 1, 2010

I have enable Refresh on Open for my excel pivot table, but user need to click "Enable Automatic Refresh" , only solution i came across is to change the registry setting. Which i dont have access to edit registry(admin disable the access).

Alternate solution i try to use Access macro to automate the process and use Outputto save it as a excel file A. Then use excel file B to update pivot table from excel file A.(as excel A data is always latest)
The problem is i will get "....A file name already exist...do you want to overwrite.." prompt.
Which defeat the automate process.

Any other solution to enable the automatic refresh on open the excel workbook?

Or Access can overwrite the exist file or save it as another file name with timestamp ?

View 14 Replies View Related

Code To Move Data From Entry Table To Historical Table By Date

Mar 14, 2014

In the attachment you will see an example of what I am trying to accomplish. What I am trying to do is find VBA code that is either specific to this worksheet or in a macro. When the sheet is opened I enter a date in B2. I then enter data into A7, B7, and C7. What I would like to happen is when the data is entered into A7, B7, and C7 the sheet goes and finds the same date that I entered in B2 and copies that data from A7, B7, and C7 into F7, G7, and H7.

View 2 Replies View Related

Macro To Create A Statistics Table From Another Data Table (containing Merged Cells)

Apr 14, 2009

I would like to have a macro to automatically generate a statistics table (on the "statistics" tab) with the 5 following fields:
Fragment names / # samples / # of failed samples / % of success / # of variations in the fragment (SNP). At the bottom of this table, I would like to have a cell with the average % of success for all fragments. The data to generate these statistics are on the "gene name" tab (please note that this name will change every time I will work on a new gene). To make things easier, I think the macro should be run from this tab.

1. The Fragment names are displayed in row #5. I use one column per variation per fragment. If one fragment has 3 variations, there will be three columns and I will merge together the fragment name cells. The fact that some cells are merged can be a problem when copy-paste to the stats table (as I would like to get rid of the merging).

2. # of samples corresponds to the number of cells in blue in column A. The number of samples can change from one report to another but is always constant in the same report.

3. # of failed sequences. In the table, I type "Failed Sequence" (if the analysis has failed) and "Missing Sequence" (if the analysis has not been done). When a sample is failed or missing, it is for the who fragment, no matter how many variation there is in the fragment, so I usually merge the cells of all variations for this failed sample.

4. % of success: this is quite easy #sample/#of failed+missing sequence for this fragment

5. # of variation is equal to the number of variations for this fragment (can be 0, 1, 2, etc.). When there is no variation in a fragment, I put '-- in all cells of the corresponding fragment on the "gene name" tab. Fragment 3 on my file is an example of 0 variation.

View 3 Replies View Related

Convert The Data Shown In Table 1 To Table 2 Without Rearranging The Columns And Rows

Sep 11, 2009

Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.

Table 1

Team 1Team 2Team 3Team 4Team 4Team 5Team 5ABABCity 12531642City 231173705City 367891125City 436251348

Table 2

City 4City 2City 1City 3Team 4BTeam 2Team 5ATeam 4ATeam 1Team 3Team 5B

View 2 Replies View Related

Transfer Data Table 1 To Table 2 Change Rows To Column?

Mar 13, 2013

Table 1
January-12
February-12
March-12

Table 2

Sr. No
Name
Dep
Lates
CL / SL
AL
Lates
CL / SL
AL
Lates
CL / SL
AL

[Code].....

View 1 Replies View Related

Data Table Is Pasted In For VLookUp - Not Have To Redo Table Name Each Time

Jun 2, 2013

I created a lookup table that works quite well. It even has if statements in the LookUp Formula. However, I have to update the table it pulls the information from each day. I wind up recreating the range each time because the table always has more rows each time. Is there a way I could just paste the table in each day and not have to change the range? The columns never change.

View 9 Replies View Related

Reconstruct Data Table So That Column Headers Become Values In Table

Jul 15, 2014

I have a large table that I want to reconstruct. For simplicity sake, let's just says it's 3 rows (excluding headers) by 3 columns.

Item Description
1/1/2014
1/2/2014
1/3/2014

Cheese Burgers
2
3
4

Hot Dogs
5
12
6

Beverages
2
5
3

I want to reconstruct it so that the column headers become values in the table. The table headers are dates, in this case, if that gives clearer picture. So the new table would have 9 rows, (3 rows of data, excluding the header times four columns).

Item Description
Date
Quantity

Cheese Burgers
1/1/2014
2

Hot Dogs
1/1/2014
5

[Code] ....

The above example is sorted by date but I would be indifferent if it's sorted by the Item Description.

Is there an easy way to do this? Pivot possibly? Again, my data table is large: 36 rows x 181 columns. Using the copy/paste/transpose feature is pretty impractical.

View 3 Replies View Related

Creating Validation List From Table Column Based On Data In Another Column

Dec 11, 2013

If I have a table as noted below with the following assumptions:

- this table will likely grow
- the 'Include' column data will change based on external criteria/formulas, so the 'Include' column will not be sorted.
- Macros aren't an option as this sheet needs to be macro free.

A
B
C
1
Item
Calories
Include

[Code]...

How do I build a formula that I can place in a data validation drop down to only include 'Item's that have Yes indicated in the 'Include' column?

I've been researching this and found answers if the 'Include' column was sorted via offset, but I haven't found any to sift through when unsorted. I feel like there is a simple answer to this that I am missing. Here is the sheet --> ExampleSheet.xlsx

View 1 Replies View Related

Creating A Mat Table Based On A Price Table

Dec 7, 2008

Part 1: Pricelist
The user presses 1 of 3 macro buttons to access the price list.
The buttons represent 1 of 3 parts of a construction project.
The buttons are labeled PHASE 1, PHASE 2 & PHASE 3. The macro,
depending on which button is pressed, hides all but the relevent quantity
col & total col.

Each job phase needs its own total & having it setup this way makes it
easier to enter new items into the table (only have to enter
the item once, rather than 3 times).

The first html shows a simplied example of the "price list" described above
(before & after the quantitiy & price cols are hidden).
The user adjust the quantity & the sheet calculates the price total.
Part 1: Material listThe material list is a list of items & quantities the user selected in the
quantity col of Price list. This list is to be printed & given to the workers.
It does not show price, only item & quantity.

This list, unlike pricelist, must be visiablly broken down by job phase.

I dont want the material list to be the price list repeated vertically
on a sheet 3 times. This would take up too much space & require new
items to be entered once in the pricelist & then 3 times in the mat list.

I would rather the mat list be on a seperate sheet from price list & have
a formula in cell matlistA1 that searches the phase 1 quantity col
(then phase2 & 3) for the first #>0.

When a #>0 is found, the formula
would return an item name into the cell on matlist. Then the formula
would run in matlistA2, & search for the next #>0.
(& so on until a complete mat list table, broken up into phases is created)

Sheet2

ABCDEFGHIJK2 3 4 PRICE CALCULATOR (USED TO ESTIMATE ANY OR ALL 3 PHASES OF CONSTRUCTION) 5 6 7 *this is how table appears before macro button named job PHASE 1-3 is pressed to access. Macro hides 2 of 3 quantity cols & 2 of 3 total cols. 8 9 10 11MATERIALSQUANTITY PRICE TOTAL12MISCELLANEOUS PHASE 1PHASE 2PHASE 3 PHASE 1PHASE 2PHASE 31399871009'x12' PLASTIC DROP CLOTH1 X4.73=4.73
..............................

View 9 Replies View Related

Linking Pivot Table To Data Source Table?

Jul 14, 2014

I was wondering if there is any way possible to link your pivot table filters to filter the data the same way in the table that it comes from? So if i had date as one of my headers and i filtered the date to a specfifc date, is there a way to also filter the date in the data source sheet?

View 2 Replies View Related

Formula To Auto-populate Data In A Table From Another Table?

May 13, 2013

I have a table below that looks like this:

LOC
# of days

DTX
3

RTC
3

PHP
12

IOP
12

The # of days column will be a manual input. Then I have a larger table that will take those values and convert them to look like this: (Obviously the dollar values are pulled from somewhere else)

1
DTX
$ 1,292.00

2
DTX
$ 1,292.00

3
DTX
$ 1,292.00

[code]....

I've been thinking of trying to use a macro but not sure that is the best way. Using a button or something doesn't seem very elegant either.

View 7 Replies View Related

Pivot Table An Extract Of Each Data Contained In This Table

Dec 14, 2006

i have a pivot table an extract of each data contained in this table.

[img]Count of NAMdate
SERVICENAM12-oct10-déc11-décGrand Total
Commercial-lauralaura11
Commercial-laura Totalgh11

custody-jonathanjonathan112
k11
custody-jonathan Totalgh1113

settlement-ludovicludovic11
settlement-ludovic Totalgh11

SPQC-elodieelodie112
SPQC-elodie Totalgh112

Grand Total1337

View 9 Replies View Related

Data Table: Incorrect Results Inside Table

Feb 22, 2007

I did a data table yesterday and it worked. I tried again today and the results are incorrect. They are coming out as a constant (the same result as the original formula). Has anyone had this happen before and figured out how to fix? My spreadsheet is fairly complex. Does the data table formula need to refer to the "base" cells? For example, if the formula refers to cell F15, but in F15 the formula is +C15, does my data table formula need to use C15?

View 3 Replies View Related







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