Sum Weekly Range Based On Daily Information?
Feb 7, 2013
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
View 3 Replies
ADVERTISEMENT
Apr 13, 2014
Have the formula that does the work daily , need formula on sheet3 , that does the convert of daily into weeks ?
View 7 Replies
View Related
Feb 15, 2014
I 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
View 4 Replies
View Related
Jan 17, 2009
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?
View 5 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
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
Jul 19, 2012
I 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)
View 2 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
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
Oct 4, 2008
I'm working on a sreadsheet for inserting and collating information for a fleet of vehicles. I have userforms for inserting various pieces of information when the vehicle is new or leaving the fleet.
I would like to use a userform for inserting weekly information. I know how to insert the basic information in columns but I'm stumped for working in rows and with inserting information at different times.
View 4 Replies
View Related
Jan 4, 2013
a daily report I have to create. On a daily basis I will be provided with dispatch information from a warehouse. What I need to do is create some sort of report that will add the data and then calculate totals on a daily basis. There are five columns on the daily report that I will be provided with but I am only interested in two colums: that is the product code and the quantites. I will then save under a week number or month.
View 9 Replies
View Related
Apr 19, 2008
I have a nice case to prepare and need weekly riskfree interest returns on the UK treasury bond. Right now I have daily returns and want to convert them into weekly. The problem is, weeks do not always contain five trading days. How can I turn my data into weekly average riskfree interest returns?
******** ******************** src="*********>*********>Microsoft Excel - UK interest daily.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA1=ABCD1DateInterest rateweekday 24-Jan-889.871 35-Jan-889.992 46-Jan-8810.053 57-Jan-8810.094 68-Jan-8810.135 711-Jan-8810.051 812-Jan-8810.072 913-Jan-8810.283 1014-Jan-8810.114 1115-Jan-889.985 1218-Jan-889.871 1319-Jan-889.962 1420-Jan-889.913 1521-Jan-889.864 1622-Jan-889.715 1725-Jan-889.71 1826-Jan-889.762 1927-Jan-889.693 2028-Jan-889.564 2129-Jan-889.555 221-Feb-889.741 232-Feb-889.622 243-Feb-889.643 254-Feb-889.684 265-Feb-889.755 278-Feb-889.931 289-Feb-889.852 UK interest daily [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name boxPLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.
View 9 Replies
View Related
Jul 25, 2008
I have set up a datasheet with information to be used in generating several pivot tables:
Column A - Our Invoice Number
Comunn B - Vendor's Invoice Date
Column C - Type of Vendor (Labor, PM, Subcontractor, Material, or Equipment)
Column D - Vendor Name
Column E - Vendor Invoice Number/Time Classification (Column C is Labor, PM, this is either Regular Time or Overtime. If Column C is Subcontractor, Material, or Equipment, the Vendor's Invoice Number is enetered.)
Column F - Labor Hours Worked (Column C is Labor or PM, a value is entered, if not, formula enters "N/A")
Column G - Labor Rate ((Column C is Labor or PM, vlookup value is entered, if not, formula enters "N/A")
Column H - Amount Billed ((Column C is Labor or PM, formula multiplies rate by hours, if not, enter the amount of the Vendor's invoice.)
Column I - Mark Up Percentage
Column J - Mark Up Amount
Column K - Total Amount Billed (Column H + Column J)
I need to set up a daily job sheet (like and invoice) for each date listed under Column B. For each day, I need to generate a daily job sheet showing all of that day's information. The location of the information is based on the value in Column C - Subcontractors's data go in one spot on the sheet and Labor costs go in another place.
View 9 Replies
View Related
Jul 28, 2009
I have attached an example file.. Basically I want the text in a range to change to red if one cell="Description".
Also, I want a cell's text to turn blue if it's corresponding data in the row says "Click on Title to Follow Link"
The example file explains it a lot better..
Thanks for the help.. Sorry if this may seem like a duplicate post but I was not able to get any of the examples I searched for to work that others have posted.. Also, maybe conditional formatting would be an option here but I could not get it to work as it will only meet the first condition that comes across thats true...
View 14 Replies
View Related
Feb 7, 2008
I would like to automatically update a 'yearly' database file with info from a file that is changed on a daily basis.
The daily file that i use has info like date, truck number, delivery stops, weight.
the database file has the similar headings.
at the end of each day this daily file is saved. I would like to have the info that is entered into the daily file automatically plugged into the yearly database file into the next available group of cells with respect to the salesperson.
This is kind of a generalization but i'm hoping to just get pointed in the right direction. If something like this involves vba then it will be beyond my ability and i'll have to do it manually, which is fine
View 9 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
Aug 5, 2013
I'm trying to create a line graph to show the trends of usage. I have a list of dates that I pulled from a website. I am trying to create a function that will count the number of dates between, for example, 8/5/2013 and 8/11/2013.
I had tried using this function to create a set of weekly ranges. =TEXT(DATE(2013, 1, 7)+(ROW(2:2)-1)*7, "m/d/yyyy h:mm AM/PM")&"-"&TEXT(DATE(2013, 1, 7)+(ROW(2:2)-1)*7+6,"m/d/yyyy h:mm AM/PM")
It correctly displays the date ranges, however when using this function:
=COUNTIF($E$2:$E$305, A2) Nothing gets pulled.
Here is one of the dates that has been pulled for example: 8/5/2013 11:10:00 AM
View 5 Replies
View Related
Mar 20, 2014
I have the following scenario:
Cell A1 shows a specific value (pivot table value), but same A1 cell value might change if pivot table is refreshed.
So I am trying to automatically copy A1 value to another cell but I need to keep track of each value when pivot table is refreshed.
I have been researching about =Value formula, but it does not work properly since A1 cell reference will change each time pivot table is refreshed.
View 4 Replies
View Related
Mar 2, 2014
I am performing a given exercise every day in the month of march. I have the dates all lined up in my spreadsheet, with the sum at the bottom. What I want to do is have another cell that will give me my daily average of performance. For example today is the 2nd and I have performed this exercise 360 times, making my daily average 130 per day. If I performed 200 of the exercise tomorrow I would have a grand total of 560 repetitions making my daily average roughly 186 per day (the day, march 3 divided by the number of repetitions, 560) and so on and forth.
I need a formula that will automatically calculate that daily average as I progress with the month. But I want the calculation to stop at the end (i.e. I don't want it to continue as the year progresses, meaning the divisor keeps going throughout the rest of the year). Basically, I guess, I am looking to fill in the daily repetition and the divisor to increase each day as I do so. My average is to be displayed in cell B36, so today my formula should read (=2/B36) and on March 31st the formula would be (=31/B36).
View 1 Replies
View Related
Dec 21, 2013
I have a daily data dump that starts at the beginning of 2012 up to the current date. I have an executive view that allows the user to see metrics(in a data graph) from the current date back to a date selected from a drop down menu. I would like to have it so they can choose what the end date will be as well as the start date. I have tired using offset with very little luck. Is there a way I can accomplish this task with VBA? Everyday a new row of data (for the previous date) is added to the front sheet. So row 1 is a header row and then row 2 is 01/01/2012, row three 01/02/2013, etc.
View 7 Replies
View Related
May 29, 2014
What I am trying to do is to create a formula for the attached spreadsheet - that calculates the daily compounding interest based on the higher rate of the two rates for the first five years then after 5 years the calculation should only be based solely on the blocked rate.
View 5 Replies
View Related
Jan 7, 2014
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 .
View 2 Replies
View Related
Aug 18, 2006
I need to monitor the average daily usage of a liquid tank for a customer. We fill this tank every few weeks. The formula I am looking for would ignore the fills and just count the daily usage.
View 9 Replies
View Related
Jun 29, 2009
I have around 250 Employees Camp Meals Statements. Each day we prepare a Excell Sheet and enter the details file attached for easy reference Im manually calculating the Totals in each sheet if emp takes meals we marked as Y otherwise N based on that i want the total meals daily. One more thing Base on employeed code i want the monthly statement in another sheet same file attached..
View 9 Replies
View Related
Jan 27, 2014
Imagine I have 2 columns of information that look like this:
Column A
Column B
AS
Dog
AS
Cat
AS
Hamster
FT
Fish
These are my key columns. The letters are initials of people and the animals are the pets they're responsible for.
Now, I have 3 more columns that look like this:
Column D
Column E
Column F
These columns can go on for hundreds of rows.
What I want to do is pull out the information from columns D, E and F where the initials and pet match those in the key list, then paste that elsewhere (say to columns J, K and L).
So, for instance, the first entry would be copied across because, according to the key list, AS is responsible for a Dog, but the bottom entry for AS wouldn't because he was looking after a fish, and that pet isn't listed as one of his animals in the key list (Fish is listed alongside FT). Likewise, the entry for AH wouldn't come across because AH doesn't appear on the key list at all.
View 1 Replies
View Related
May 25, 2014
I've got this spread sheet with an overview of my companies leased assets and i would like a formula to break the information into monthly periods so we know what we require to return to the leasing company before getting charged excess rent.
The overview sheet is from A - J, and would like for when i make changes to the overview sheet it also affects the sheet for the month, similar to a vlookup, but unsure how to do the that depended on the date (date starts from 01/09/2013).
View 1 Replies
View Related
Feb 1, 2008
I have information in range A1 through z100, how do i put all the information in one column?
View 9 Replies
View Related
Apr 29, 2014
I have been struggling hard to pull information out of a table using vlookup and match & Index functions. It does not seem to be working. Below is my criteria:
I have a data of call centre agents with names , dates and calls offered answered details. On another sheet i have given a list of names of agents in a drop down list and all the dates of the week. Now i want that when a user selects a particular agent name from the drop down list, the data fetched under '07042014' date column is against the number of calls answered by the selected agent on this date. I have 5 columns of title '07042014' , '08042014', '09042014', '10042014' and '11042014' Capture.PNG. Attach is the file how it looks like.
How to use match and index function or any other function to fetch information against this date as per the agent selected.
View 3 Replies
View Related
Jan 9, 2009
I'm still having trouble with this formula. I have a sheet, sample attached, that has a list of transactions, with customer name, date, cash-in & cash-out information that is listed down a set of rows. I'll have a tab for each year & the information will be seperated by months.
My other sheet is a monthly report that I will be printing. I have a seperate "section" for each customer & I want excel to fill the information from that customer based on the month. I tried using a index match formula & a vlookup formula in a helper column, but it doesn't bring out the info that I want.
View 6 Replies
View Related
Oct 8, 2007
If you look at the attached file you will understand what I am trying to get at more. I have a download which comes from a time management system. What I want to do is have a macro which runs everytime the name dropdown box on the summary page is changed. The macro needs to lookup the correct times for that person and the day and place them in the correct order (i.e 1st clock in, 1st clock out, 2nd clock in, 2nd clock out, etc.) I am trying to find the best way to do this.
View 3 Replies
View Related
