Flowing Month Data Into A Chart Based On Weeks?
Feb 27, 2009
I have a spreadsheet that I am using to try to track Project Man Hours. What happens is:
I enter in the amount of weeks a Project Phase is in and when the Project Starts. I also enter in how many "Men" we are going to need.
i.e. Project Start - January 2009
Phase 1 - 4 weeks with 1.5 Men
Phase 2 - 20 weeks with 2 Men.
What I want to happen is:
Excel then adds that data to a chart showing EACH MONTH and the amount of men needed for each phase.
Project 1 showing a line chart with January-December and the lines correlating with the amount of "Men" needed each week based on the Start Date and Sequential Phase Week Amounts.....
View 2 Replies
ADVERTISEMENT
Nov 16, 2006
I am working on this "maintenance schedule" that is currently sorted out into tabs that are organized into the 12 months, jan, feb and so on.
I have to write either a command or a program that would be efficient enough to easily convert these monthly data in weekly ones.
So the command would have to convert those "jan, feb, mar... ...dec" tabs into "week 1, week 2, week 3... ...week 52". (prefably by linking them up some how)
View 9 Replies
View Related
Jan 19, 2008
I need a formula that will tell me how many weeks there are in a month.
If cell A2 had the month and cell B2 had the year then I would like to return 4.....
View 9 Replies
View Related
Jan 23, 2007
I have a weekly forecast for what will be sold for the upcoming year and want to sum the quantity by months. For example, 1/6/07 sell 351 units, 1/13/07 sell 315, 1/20/07 sell 1,165, and 1/27/07 sell 328 units so Jan would return 2,159. To avoid future user error, I'd like to sum by month without the basic sum( range) formula and have tried the following SUMIF statement:
=SUMIF(Weeks,"=Jan*",Widget_Qty)
That returned zero. I formated my Weeks to look like "6-Jan-07" so I thought that the month name could be used as a text but this was also wrong. I also tried changing the month value from text, i.e. Jan to a date value displayed as a "mmm" but failed again.
View 6 Replies
View Related
Feb 4, 2013
Here is what I have and am trying to do:
I entered the date and day in this format in multiple sheets in workbook 1 (named 0101, 0102, 0103) from the start of the year to last night (B2:C4):
Date:January 01, 2013
Day:Tuesday
I then entered all the data needed below that (B5:E26):
TimeTransactionsTotal SalesAverage Sales
12:0039 $352.97 $9.05
13:0053 $416.98 $7.87
14:0044 $339.42 $7.71
15:0043 $304.96 $7.09
etc.....
In workbook 2, I have sheets named for each day of the week. I would like to take the information from the previous three weeks for each corresponding day from workbook 1 and put the averaged data into another table with the data all formatted the same in workbook 2. This way, each week when I go into the aggregate file (workbook 2), I can look at the chart I'll create and see the average of the past three weeks transactions, total sales, and average sales for each hour of the business day and be able to staff accordingly (assuming the trend continues).
View 1 Replies
View Related
Feb 18, 2014
I have a workbook that needs to display the relevent weeks in month, the dates would be the start and end dates of each wekk (monday to Friday) but also need to take into consideration, partial weeks i.e. only a wednesday, thursday and friday at the start of the month given where the first available date falss on.
see the attached sheet for reference, I am looking to do this with as few formulas.
It also needs to show a list of monday only dates for the selected month.
View 7 Replies
View Related
Jun 14, 2007
to print last months results each month. I have a cell (range name = LastMonth) with formula =now()-15 to display last months full name since I will always do this in the first half of each current month.
I want it to look at that cell on the "FY" sheet, see what month is displayed
then go to the "Monthly Audit By Cell" sheet & print the correct chart.
Each chart is named "Jan Audit" and so forth
I have this - only first six months done so far. Problem is nothing happens-no printing!
Doing this today on June 14th should print out the May chart.
' Print the Audit for the current month
View 9 Replies
View Related
Jan 8, 2009
I have a spreadsheet with many workbooks. Each workbook contains sale figures for all products for each product range over a period of time. There is one workbook that totals the figures from other workbooks.
This spreadsheet is updated by another department on a weekly basis with new figures added to each workbook (products within each product range are also likely to increase).
Currently, the data in each work book is grouped by months (with a row summarising the month's figures). I can create graphs to visualise monthly performance, but also need to have a weekly performance graph/chart. Thus, the data also needs to be grouped by weeks.
I have tried creating subgroups for the weeks within the month groups, but it seems impossible (?!) to do when a week subgroup comes right in the middle of a month summary (e.g. Dec 2008 Summary comes in between Week 29 Dec 2008 - 04 Jan 2009). I have attached an example spreadsheet in case my description was too convoluted!
View 2 Replies
View Related
Aug 6, 2008
I have to illustrate that the average life of a set of accounts is 6 weeks. I figured that the best way to add significance to this length of time is to show in either a pie, column or bar chart the relevancy of 6 weeks within a 52 week year.
Sounds fine, right? Well, I went blank after that. I couldn't think of how to set up my worksheet in order to generate a chart of this nature.
So, I guess I'm wondering how you guys would set up a table that could then be used as the source for either of the aforementioned charts.
View 8 Replies
View Related
Jan 22, 2008
Is there a way to display only the current month and the past 11 months in a Line Chart in Excel? So if I was to print Jan 2008 Excel graph, it would only display Feb 2007 - Jan 2008 data charting.
The spreadsheet of the data contains data for Jan 2005 - Jan 2008 so far, but only the current month with the past 11 months should display in the line chart depending on what is the current month.
View 13 Replies
View Related
Jul 8, 2013
I am trying to do a moving average and running into an issue with the count part of the formula.
Column C (C2 to C54) has the numbers I need to average.
Average is based on last 5 weeks.
My issue is that C23 is blank as we had no data for that week.
Currently the formula is counting 25 entries between C2:C27, there should be 26.
My average should be 31 but I am getting 33.
This is the formula I am using :
=AVERAGE(OFFSET(C2,COUNT($C$2:$C$54)-5,0,5,1))
I have tried using different Counta and ISnumber still not working
I would rather use a formulas then VB code.
View 5 Replies
View Related
Sep 7, 2009
i currently have a what if statement
=IF(B2>=NOW()-42,"< 6 wks","> 6 wks")
I need it to also give an option of between 3 wks and 6 weeks
Therefore all options are
3-6 WKs
< 3 WKs
> 6 WKs
I have mastered 2 but now need a 3rd.
View 7 Replies
View Related
Jan 11, 2014
I have attached a sample excel sheet.
I want data to appear on sheet 2 from B10 onwards based on month selection and removing duplicate entries and sorting in ascending order
When no data is found to display message box "data not found"
View 9 Replies
View Related
Apr 5, 2014
show/display data based on month criteria, with selected month in cell H2 (as start date) and I2 (as end date) my expected result start from cell H7, i called "blue area"..
see my attached workbook..
View 9 Replies
View Related
Jan 21, 2007
I have a tab set up to display information that has been colletect on other tabs. What I would like to do is change this information depending on what month is selected.
For example, if I select December then data would be pulled from other tabs and displayed. If I Select June the information would change and select other data from the same tabs.
View 9 Replies
View Related
Mar 26, 2014
Have been going round and round on this! I have a set of data that shows sales dollars by weeks. Currently, that data is in a Pivot table. I'd like to have a macro that selects the weeks (1-52) based on my input. For example. If I want to see weeks 5-52, I want to be able to input "5-52" somewhere and have a macro run that will select weeks 5-52 in the pivot table instead of having to go in and manually select each.
View 1 Replies
View Related
Jun 29, 2007
I have created an action plan in Excel, with a column containing the values Yes, No and Partial to show prgress against the actions. I have used CountIF (in cells D72 to D74) to count the number of each Yes, No and Partial. I want to be able to put these figures in a chart each month. I am using columns F to Q for the chart data. the headings of the chart columns are month values, e.g, F1 is "6" (for June).
I have used Month(Now()) to generate the current month value in cell B1. I now need a macro that will check the value in B1 against the chart table headings in F1 to Q1 and if it finds a match, will copy the values D72 to D74 into the chart table below the relevant heading.
View 8 Replies
View Related
May 1, 2006
I would like to create a monthly inventory, based on workdays (Monday - Friday)Myrna Larson has a formula that I would like to use with the workday function, but I don't know how to combine them.
=IF(A1="",A1,IF(MONTH(A1+1)=MONTH(A1),A1+1,""))+ = workday
to fit on the page, I need the dates to be from the 1st to the 15th, and 16th to the 31st. I am not sure how to write this either.
View 11 Replies
View Related
May 14, 2014
I want a table to display data based on which month i select from a drop-down list, the data is of course extracted from a different table. For example, in my final table( highlighted in yellow), i want to display the revenues, cost of goods sold..etc of April in this table when i choose April from the drop-down list, the data of the entire year is located in another table that i plan to hide, as we only need to review one month.
View 1 Replies
View Related
Aug 3, 2006
I have a detailed note inside biweekly.xls, it explains everything in context.
Two excel sheets, one is updated manually (Vikki's Yearly Averages.xls), while the other is for reporting (biweekly.xls)
The coding will all be in biweekly.xls.
First, it needs to take a date to the right of a name from biweekly.xls and use the month and year from that date for the search.
Then it needs to look in VYK.xls under the name and copy every entry starting in the year specified and month specified and ending at the current date.
View 6 Replies
View Related
Nov 6, 2013
I received a workbook, and one of the sheets has a chart based on data from another sheet within the same workbook. Interesting enough that I cannot select any cell within that chart worksheet, all the rows and columns headings are gone, i can only select the chart. Secondly, it doesn't look like anything is locked. I can still protect the worksheet for some reason, so it's not protected.
View 3 Replies
View Related
Jan 5, 2010
I would like to chart (pie or graph) sales based on the hour they occured.
In excel, I have column A - the time stamp, and column B, the sale. Note that each sale has its own unique time stamp.
View 9 Replies
View Related
Sep 5, 2006
I'm facing a charting problem and i can not find any solution with a search here, anyway this is the problem: When I choose a week I would like to see the results of the 5 previous week also.
View 4 Replies
View Related
Sep 16, 2006
I need this chart to show 3 bar colors based upon values from b5:b14 (data column). This chart is based upon the last 10 draws, so since there are 3 positions, the hit frequency expectancy would be (10 draws/3 positions = 3.33 rounded to 3). So now I would like my bar graph colors RED for any values above "3" in b5:b14 (data column), GREEN for any values equal to "3" in b5:b14 (data column) and BLUE for any values below "3" in b5:b14 (data column).
Attached is a sample chart
View 9 Replies
View Related
May 15, 2013
I am using Excel 2010 on windows 7 PC. I work for a railroad and one of my coworkers gets data once a week about how many carloads we ship to a certain company. They save each week in its own worksheet so at the end of the year they will have 52 worksheets worth of carload data. What they would like to do is take these 52 worksheets and then combine this data onto one worksheet so they can see all of it going from week 1 to week 52.
Here is what 1 week's worth of data looks like...
Here is what the 52 week summary page looks like...
And here is what the summary page looks like with all of the data on it....
Although we have compiled all of this data, it was done by copying and pasting data from 52 different worksheets (took some time as you could imagine). So, my coworker asked me if I knew of a faster way to do this. After spending multiple hours on the net researching this and trying a bunch of different options (Data Consolidation, Summary/Array Functions, and varying Macros) I have not been able to produce a worksheet that takes all of the data and displays it like the last picture above. Because I only know the very basics of excel I'm sure I'm probably doing something wrong. Trying to decipher the code for macros hasn't been easy and although I got data consolidation to work it was adding up all of the numbers instead of taking them from one sheet and placing them on another with all of the worksheets' data side by side.
View 6 Replies
View Related
Sep 29, 2011
I have used sumif statement to set up a value by week and then used the drop down list cell reference to display what i want ie week 25 data
What I would like is to display the 8 previous weeks and then use this in a graph, so every time i change the drop down week I see all the data showing the current week and previous weeks.
Now the problem is which formula do i use to to identify previous weeks
ie
WK No 21 22 23 24 25
Visits 100 200 250 300 400
View 1 Replies
View Related
Feb 22, 2014
In my table I want to create a user form where the individual would enter a number which would correspond for the Header Row.
and
The user would also enter a Number that would correspond for the first main column (Column B).
Based on the numbers entered, I want to find the next highest number.
For example if 61 is entered 66 would be chosen.
-Same would be for the numbers entered to find the column.
Based on the numbers entered I would like to find the intersecting cell.
Basic code I can embed in a user form?
View 1 Replies
View Related
Apr 4, 2007
I currently have a simply bar chart that is based on a running 5 days figure so the X-axis is like 01-Mar 02-Mar 03-Mar ...
Y-Axis is the data that I am trying to present - Total $
The source of this data is simply two columns - Column A is the running date, Column B will be data.
Daily I need to only present 5 days historical data based on the latest date i.e. today and the past four days (the source data is updated daily).
Can a macro be used to auto update the bar charts based on today's date?
View 9 Replies
View Related
Dec 27, 2013
I am looking for a simple formula that would look at column A2-A100 and calculate the whole number for the number of the week I am on. So in cell K22, I would like it to add up the A column and spit out a number 4, then on Monday when I add a new record I would like it to automatically update to a 5; indicating the 5th week I have been tracking the data. This number is needed to calculate the average amount in a work week accurately and automatically in cells K20 & K21. New weeks start on Monday and end on Friday. No data will ever have a date of a Saturday or Sunday. I have colored the cells currently to indicate what the number should be. There is 4 alternating colors now. The color coding is just for reference and will not be used going forward. Data will be entered weekly indefinately.
View 3 Replies
View Related
Aug 30, 2012
I am working on automating a report via vba code where the range of the chart needs to be changed every time the macro is run. Basically the chart is a snapshot of performance of 6 months .
For example : if the chart is already displaying Jan - Jun data , when the information for July is added and report updated the chart should be updated to reflect Feb - Jul data .
There are many graphs but have added 2 sample here for ref .
View 5 Replies
View Related
