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?
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.
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.
Have the formula that does the work daily , need formula on sheet3 , that does the convert of daily into weeks ?
View 7 Replies View RelatedI have daily Open, High, Low, Close stock prices. Separately, I need the same data but in weekly format, so:
1. The weekly open is the open of the first working day.
2. The weekly high is the highest of each of the daily highs.
3. The weekly low is the lowest of each of the daily lows.
4. The weekly close is the close of the last working day.
How can I do this using formulas?
OHLC sample.xlsx
I have two time series which span several years. The first series measures stock levels on every Friday (52 values a year). The second series measures the price level every weekday (260 values a year).
I'd like to condense the daily data in to a weekly average, can I do this easily? For example, I could manually use the Weeknum function to calculate the week number of each daily price data, then find the average daily price for each week, thus giving me 52 values which I can compare to the weekly stock series. Is there an automatic, fast way of doing this? Alternatively, I'd be happy to settle with a monthly average. Is this possible via macro's or does VBA need to be used?
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 RelatedI have a multiple sheet document, on the first sheet I have my daily data:
Name
ID#
Ward
TTO
Transport
Transport 2
Date
Time In
Time Out
Time Stayed
ex1
xxxxx
12
xx
Taxi
01/01/12
xx:xx
xx:xx
xx:xx
[Code] ........
This is updated daily and it shows patients that have been through the department, who, when and how long. This is then converted on sheet 2 and shows as:
Date
Number of Patients
01/01/12
2
02/01/12
1
03/01/12
1
My third sheet has a very similar layout to sheet 2 but is weekly, previously i have used the following to find a weekly total:
=SUM('sheet2'!B563:B567)
I now have well over 4000 rows and altering this sum to accommodate for each week is time consuming as sheet 2+ are updated to cover the following 6 months.
Is there a better formula or a way to use the current formula and drag it down to auto fill? Currently trying to auto fill does the following:
=SUM('DAILY ANALYSIS'!B563:B567)
=SUM('DAILY ANALYSIS'!B564:B568)
=SUM('DAILY ANALYSIS'!B565:B569)
I want something like:
=SUM('DAILY ANALYSIS'!B563:B567)
=SUM('DAILY ANALYSIS'!B570:B574)
=SUM('DAILY ANALYSIS'!B577:B581)
I have one workbook where someone enters information on a daily basis and id like to sum this in another workbook weekly.
The daily sheet "sheet1" has headings in row 1 starting B1 = 11/02/2012 this is then dragged along till the end of the year. In B2 the user will enter a number so B1 - 11/02/2012 and B2 - 5
On the next "sheet2" in cell B1 i have the date 17/02/2012 and in B2 id like to sum the total from "sheet1" B2 - H2 which is in effect the range 11/02/2012 - 17/02/2012 then id like to be able to drag this formula along so it automatically sums the weekly totals based on the first sheet
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)
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
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..................
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 RelatedI 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?
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 RelatedIs it possible to turn monthly data into daily? Repeating the same data for four sets of five working days to turn a CPI monthly inflation index into daily data, to turn example 1 into example 2.
Example 1.
DATEVALUE
2008-01-01 212.174
2008-02-01 212.687
2008-03-01 213.448
2008-04-01 213.942
2008-05-01 215.208
Example 2:
2008-01-01 212.174
2008-01-02 212.174
2008-01-03 212.174
2008-01-04 212.174
I have data in the following format (columns A & B):
Code:
Day Value
1/1/2002 2.1
2/1/2002 4.8
3/1/2002 0.1
" "
" "
" "
31/12/2011 5.2
I'd like to be able to write a formula so that I can get the daily data averaged over each month, e.g so that I have something like:
Code:
Day Value
Jan 2002 3.3
Feb 2002 4.3
Mar 2002 3.1
" "
" "
" "
Dec 2011 4.2
I'd like the data in monthly averages so that I can graph them. I have read about array formulas and averageif statements, but I can't seem to combine them to make them work.
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
While I was working my daily expense I come up with this issue. I do eat outside while I am on work. Sometimes I go to Pizza, sometime I go to mexican etc etc. The common between them is word FOOD. I would like to modify the formula suggested by Ron Coderre
=SUMPRODUCT((TEXT($A$2:$A$14,"mmm")=$E3)*($B$2:$B$14=F$2)*$C$2:$C$14)
See the attached file to get more idea of my question. Then I would Like to Highlight Entire Rows which contains a Specific text.
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
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 RelatedI have to compute monthly standard deviation (volatility) from daily data.
The problem consists on the fact that the days during a month are not constant and change over time.
I attach the excel file : Price_Time_Series.xlsx
In this xls file, you can find dates in column A, price in column B and returns in column C.
I want to compute standard deviation at the end of the month for stock returns (column C), by considering all available days.
As you can note from the file, the days change over time and they are note constant. How can I compute standard deviation of them?
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 RelatedIn 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
I have a problem here in calculating the Daily sales target based on Monthly Targets and Year End Target.
I am attaching the file herewith which has Yearly & Monthly Targets defined. Need calculating Daily targets which should match with Monthly & Year end target .
I have the split of day wise sales for a week as well in another tab. However not able to get the exact monthly target as listed .
I've daily data of a stock indices returns and I would like to calculate the monthly standard deviation. Currently, I'm using the following worksheet functions: =STDEVP(C2:C20)*SQRT(COUNT(C2:C20))
However, the range changes from month to month, which makes the process of calculating the monthly standard deviation to be quite tedious if I've about 10 years worth of data. I assume I could somehow substitute the range with a dynamic range, but I'm struggling to come up with the correct formulation that would do that.
I'm using Excel 2007 and am having an issue with grouping/ungrouping fields in pivot tables.
I have 2 separate pivot tables, both from the same named data source, but summarizing different data selections. Both tables include the date field, I am trying to produce both a daily and a monthly table, but whenever I change the grouping/ungrouping of the date field setting on one table, the other table changes to the same grouping.
Is there anyway to have one table with an ungrouped date field and one table with the grouped to month date field?
I have the following three columns of Data and I'm using this formula to calculate the %age difference cells:
=IF(ISERROR((F14-F13)/F13), " ", IF(ISBLANK(F14)," ",IF(F14=0,"-100%",(F14-F13)/F13)))
A - No. of Hits B - %age difference C - Weekly Average
25
50 100%
25 -50%
0 -100%
20
0 -100%
0
50
I have left blank the "cells" which are giving me trouble, and my troubles are as follow:
-Where the value of the previous month was 0 and the value of the current month is higher my output is Blank, ideally I would like this to show as 100%
-Where the value of two months in a row are 0 I would like the value to show as 0% for the second month
I did add extra into the formula so it would show the 100%, unfortunately it did this for any consecutive months at 0 also which was incorrect.
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 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