Calculating Quarterly Averages From Daily Data
I have a sheet with daily data starting from 01/01/2000. I want to calculate daily averages for each quarter (i.e 2000Q1 value will be the average of values between 01/01/200031/03/2000, 2000Q2 will be average of values between 01/04/200031/06/2000, 2000Q3 average(01/07/200031/09/2000) and 2000Q4 will be the average of (01/10/200031/12/2000) etc. for all years afterwards.
I want to have the values in the corresponding cells starting with range ("e2")
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Calculating Daily Averages
The data was taken in 15 min intervals and is organized by date. I have one column with the date and time and another column with the data. I need to find the average for each day. I have almost a years worth of data. Is there any formula I can enter to find the values in a given day and return the average of the values (without having to select the data for each day)? I want to be able to copy the formula down a column with the value per day.
View Replies!
View Related
Quarterly Interest Calculating Formula
I use Excel 2007 , I created Interest calculator , on Daily basis , to caluculate interest , compounded quarterly. But I want to make it compact , as d one I created is long enough. A3 = Principal Amount B3 = Date of Investment C3 = Interest as on Date D3 = Number of Days , amount Invested {comes out of formula set} E3 = Rate of Interest Now in F3 I want the Interest amount , compunded quarterly. Some times NUMBER OF INVESTED CAN BE LESS THAN 90 DAYS TOO...then what ?
View Replies!
View Related
Calculating Averages In Spreadsheet
I'm trying to Calculte the Averages of just the Teachers Salaries in my spreadsheet. If possible i would like to put the average at the end of each of Teachers information. Is there code that would allow me to do so. I attached a sample file to view.
View Replies!
View Related
Calculating Averages In Mark Book
I want to be able to keep an average grade for my students through the course of a year. Work is marked with a grade A  U. I can use VLOOKUP to convert this to a numerical value but I can't work out how to generate an average from these numbers because every week there will be a new unit of work marked. On the sample spreadsheet I have attached, the problem I have had is trying to get an average of the numerical values in row 7 because the cells without numbers have a VLOOKUP formula in them and mess up the calculation. And as the range of numbers will increase each time I mark a piece of work I can't use a fixed formula to find the average from say 10 numbers...
View Replies!
View Related
Formula: Calculating Averages To Reach A Goal
Below is my data point for each month. The goal I need to hit is 99%. So I need to figure out what minimum monthly percentage I need for the rest of the year, I will need to reach a goal of 99%, and if I can't reach it, return an error. Lastly, i want to be able next month to go in and fill in the AUG percentage with an absolute number (i.e. 89%) and then I would like the rest of the percentages to automatically update by figuring out the new minimum monthly average given the new value for August. I thought that I might be able to do that if there is a function that says "If cell is a number, leave it alone, if it's a formula, then include that cell in the calculation of the minimum monthly average. Jan 89% Feb 88% Mar 83% Apr 89% May 90% Jun 86% Jul 82% Aug Sep Oct Nov Dec Goal 99%
View Replies!
View Related
Calculating Averages :: Weekly Demand Of A Product
I am currently using the following formula to calculate the average weekly demand of a product: =SUM($G8:$CF8)/COUNTIF($G8:$CF8,">=0")) It works fine, but sometimes the data I'm analysing doesn't span the full horizon (78 weeks). What I therefore want, is for the COUNTIF part to recognise the last populated Cell in the array, and to use the length of the array up to that point to calculate the average. Put another way, if I only have 52 weeks of data (the last populated Cell is $BF8), I want the COUNTIF to calculate based on 52 weeks and not 78 weeks.
View Replies!
View Related
Calculating Daily Quantities
I have 3 worksheets: Income; Expense; Consolidate. In the first two sheets i am entering, by dates, quantities that are getting in and out of the warehouse. My code copies that information in the consolidated sheet. What I need is to make a code that Calculates the "Daily Quantities" and "Rent", based on quantity in the warehouse, that I am paying each day.
View Replies!
View Related
Automate Quarterly Calculation
how do i get excel to automate quarterly calculation? in column A1 I have Year and column B1 i have months (112) i have data in column D1  H124  I would like to run a macro where it will pick up Jan, Feb, march (1,2,3) for each year in column A and do a quarterly average right now i manually use =(D35*31+D36*28+D37*31)/90 but i would like to avoid human errors can i get excel to read if column B is 1,2, and 3 run quarterly average? and would excel know that in Feb there is only 28days except leap years?
View Replies!
View Related
Store Data Daily
I have a two rows of data one containing names and the other containing corresponding numbers. The names are static and the numbers change on a daily basis. I want to be able to copy the numbers to a static table next to each name on a daily basis (so I can see what the value was a few weeks ago). Is there anything I can write to do this job? My thinking was to set a vlookup to grab the data but i'm not sure how this would work because the vlookup would change daily when the numbers change
View Replies!
View Related
Manipulate Date Ranges For Monthly And Quarterly Reports
I am currently trying to manipulate date ranges for monthly and quarterly reports, and am having trouble doing this. I have attached a file with an example of what I would essentially need. I would need the "Nbr of Projects", "Nbr of Days worked", "Nbr of International Projects" and "Nbr of Local Projects" cells filled in under each respective report, based on the data in the top left. If a project falls in two months, such as "18.12.2008 to 15.01.2009", this would be treated as 1 project for December and 1 project for January. Also, I would need only "NETWORKDAYS" included in the solution, so 11 days in this example.
View Replies!
View Related
Looping Through Daily One Minute Data
I'm trying to create a macro to loop through daily one minute data.I believe the flowchart would be something like: Create Variable For each day in recordset Loop through each minute record Run system rules Copy to Seperate worksheet End Additional Info: Data is in columns BG (Date,Time,Open,High,Low, Close) Sample system could be something like: If Current record close price is > Past 2 records Close Price Then Buy 100 Shares Liquidate if poistion moves against by 10% Take Profit if position increases by 5% Else close by days end
View Replies!
View Related
Move Data Daily Into New Column
I have used the "Import External DataWeb Query" to gather financial data. This data is updated daily by the web site. The data fills up columns A2 to E 6000. The data on Column B is of importance and I need it to be stored daily. I need a code that will store todays Column B data in column F, tomorrows Column B data in Column G, dayafter's column B data in Column H and so on.. In short, I need to create a database automatically..
View Replies!
View Related
Average Daily Data By Month
I have a workbook with two sheets  DATA and SUMMARY. DATA has two columns  date and data_value. Data will be added to this sheet on a regular basis SUMMARY has two columns  month and average In the column for average I would like a formula to calculate the average of data_value for each month without having to manually determine the range for the particular month.
View Replies!
View Related
Sum Daily Data By Month
I have a large set of daily rainfall and evaporation data (see attached sheet) which I would like to sum into monthly data. I have previously been doing this in Access, can anyone show me a quick way to do it in excel?
View Replies!
View Related
Finding Highest Value In A Week From Daily Data
I have two columns. A column = contains dates but does not always have 5 days in a week. Holidays are not entered. B column = price data for each day All I want to do is get the highest price from the previous week. So for example last week highest price was 5000 then column C will display 5000 for this entire week. I tried using WEEKNUM and WEEKDAY but i am clueless on what to do after that. I'm trying to avoid macros or VB since im not that advance with that. But if I have to I will.
View Replies!
View Related
Summarize Daily Data Into Weekly Average
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 Replies!
View Related
Calculate Monthly Standard Deviation From Daily Data
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.
View Replies!
View Related
Pivot Table Report Daily Data & Group Same Days In Year
Last week I posted a question related to formatting a cell to return a Day of the Week versus a numerical representation IE "Wed" instead of 02/20/2008 12:00AM. The solution provided worked for me: 1) Format cell to DDD MM/DD/YYYY HR:MN. Cell range (A1:A500) 2) Format destination cell with DDD. Cell range (B1:B500) 3) Destination cell (B1) = to original cell A1 4) B1 displayed data as "Wed" However, the issue I still have is; I wanted to create a pivot table summarizing a year activity by Day of Week (in other words 7 entries for the year) and the pivot table still recognized all the MM/DD/YYYY. I ended up with a table displaying every day of the year instead of a yearly summary by Day of Week. Is there some way to strip out all the other numerical data from the new column I created to run a pivot table by Day of the Week for a whole years activity?
View Replies!
View Related
Tracking Daily Total Sales And Individual Tender With Data Extracted From .dbf File.
I want to track daily sales of a shop with the tenders (Cash, Master, Visa)seperated. Everyday there will be a file ctp.dbf from a folder YYYYMMDD (previous day date) which contains sales details. I tried to use sumif commands and everything is working fine. everytime i have to open book.xls and from it I do a files>Open to open the ctp.dbf for the calculation to be done. is there a way where by i can open 1 file and everthing i calculated properly? Also this book.xls can only do for 1 day how can i go about having the daily sales detail of the month (look something like sales summary.xls) or even year in 1 excel file? attached is book.xls and sales summary.xls for reference.
View Replies!
View Related
Lookup Invoice Numbers From A Raw Data File With ~5,000 Line Items On A Daily Basis
I have a spreadsheet, in which I need to lookup invoice numbers from a raw data file with ~5,000 line items on a daily basis. The lookup is based on two criteria searches (1) search product type (2) search product make. In this example, I have 4 product types: 1 – car 2 – truck 3 – boat 4 – motorcycle For this example I want to search invoices; (1) first search for cars only (2) search for product make. In my attached example, the first item (cell E2) would return invoice number 7147875FRD from the raw data file. The second item (cell E3) would return invoice number 7147877NSN.
View Replies!
View Related
Calculating Several Data Depending On Date
I have a truck log. Every time some comes in to take a truck they must log the truck out with the millage. After a week, I am supposed to find out how many miles my trucks ran for that week. What would be the code to.... In K column, calculate the milage that is in row F; which is A3  A2 and so on down. But it would have to skip a line every time the truck number changes (in column C) it would skip that line. It would go until a blank cell. Does anyone have any ideas how to do that?
View Replies!
View Related
Prevent Data Tables Calculating
I have a file with numerous data tables that slows down the file significantly due to it's large size. I would like to set the calculation method to Manual, but also be able to manually turn the tables on and off so that they don't slow the file down when I hit F9 to calc. Is there a formula or method to accomplish this? BTW, I can't use VBA.
View Replies!
View Related
Calculating Information From Autofiltered Data.
I have been having alot of trouble with a spreadsheet and have managed to overcome some huge hurdles until now, and I'm really stuck in a rut. If you look at my spreadsheet the main focus is on the Final Results page. I currently have the data in one sheet which I then autofilter to find out the percentage rejection : For example select Grower (Grower9), Product (Lillies Stores), Variety (Orientlstd). This the shortens the list to about 14 items which I then work out by hand. What I want to achieve is on the Grower Summary page, to work out the overall percentage of rejection I.E. = (Sum of data matching critera in Row F / Filtered data of Sum of Row E) * 100. The next problem I have then is I want to be able to do it quickly / automatically based on the content (It doesn't have to autofilter on this sheet, it's just like that to make my life easier at the moment), so it will work out all of the growers / products / varities and put them onto a seperate summary page for each grower. See the attached sheet!
View Replies!
View Related
Formula For Calculating 360 Rowsx180 Columns Data Set
I have a set of data 361 rowsx 180 columns. I need to calculate a volume using these depth measurements and the one row of latitude. The formula i need to use is along the lines of =111120*cos(A1)*B1. Then for the next calculation A and B need to be the next cell down, so =111120*cos(A2)*B2. This continues all the way to the bottom and then the equation starts back at A1 for the cos() value and B changes to column C, so it will be =111120*cos(A2)*C2, then you go down the columns until you reach A180. this goes for 360 colums.
View Replies!
View Related
Dynamically Build Formulas For Calculating A Running Percentage For A Row Of Data
I need to dynamically build a formula for calculating a running percentage for a row of data. The columns that are referenced by the formulas are static (f thru r), but the row number can change depending the number of rows of data. I have a set of data that can have one to many lines in it (the data is placed on the worksheet using vba code). After the data is placed, a total line is built using this 'Find row number for total row (2 rows down from last row of data) endrow = Range("a35").End(xlDown).Row sumrow = endrow + 2 'Set sum formula for the "e" column Range("e" & sumrow).Formula = "=sum(e35:e" & endrow & ")" 'copy total formulas to columns f thru r...............................
View Replies!
View Related
Averages From 2 Columns
We are trying to get an Average between 2 columns that have the same conditions. We have been using an array formula succesfully on data that is on 1 column and I have trying to incorporate an Offset into the formula with no luck. Here is our latetest attempt, WITHOUT THE OFFSET. The data is in Column P and Column N =AVERAGE(IF((('Mill Process Data'!N3:N6650,'Mill Process Data'!P3:P6650>.01)*('Mill Process Data'!N3:N6650,'Mill Process Data'!P3:P6650<.3))*(ISNUMBER('Mill Process Data'!N3:N6650,'Mill Process Data'!P3:P6650)),Mill Process Data'!N3:N6650,'Mill Process Data'!P3:P6650)))
View Replies!
View Related
Averages For Last 12 Entries
I need to calculate average for last 12 valid entries in a column. I say valid because if there are blanks or zeros in the cell they need to be discounted. The solution needs to work on percentages as well. To explain further Col A Col B Jan 2007 1 Feb 2007 5 Mar 2007 5 Apr 2007 0 May 2007 Jun 2007 20 Jul 2007 0 Aug 2007 Sep 2007 10 Oct 2007 0 Nov 2007 10 Dec 2007 18 Jan 2008 8 Feb 2008 6 Mar 2008 0 Apr 2008 May 2008 10 Jun 2008 1 The requirement would be to calculate average from Jun 2008 backwards for 12 months where there aren't zero or blank entries.
View Replies!
View Related
Automatic Averages
I am inputing each day the total amount of sales. I am manually doing the averages for past 12 weeks on Monday sheet. I would like when I enter the amount for that week that the cells B84, C84, E84, F84 are automatically updated for the past 12 weeks. I have tried a few different approaches but I keep getting #ref or circular reference
View Replies!
View Related
Rolling Averages
I am working on a spread sheet and am trying to use rolling averages of the last 3 months. I am using worksheets for each month with a final worksheet containing the 3 mo. rolling average. I tried using a formula with OFFSET and it worked fine if all the data was in 1 worksheet, but did not work for multiple worksheets averaging to another worksheet in the same workbook.
View Replies!
View Related
Averages Formula
In the following cells I have an average: B6,J6,R6,Z6, and AH6 and a total average on cell AP6 How would I go about making a formula for that. The reason why I ask is because all the info I have seen refers to the cells with the averages need to be in sequence or range and nothing about the cells being seperated. I also tried using the countif method, but same prob, the cells need to be within a horizontal or vertical range
View Replies!
View Related
Average From Several Averages
Column 1 contains the number of times an ad has shown Column 2 contains the average position of the ad from column 1 Like so, Ad Impressions.........................Avg Position Google: 14,220,023..........................3.2 Yahoo: 346,656...............................1.2 MSN: 123,234..................................8.9 Total: 14,689,913............................??? Doing the following, = Average(B2,B3,B4) This is not the real average as this is directly influenced by the number of ad impressions... How can i make a real calculation to give me the true total average position of my ads?
View Replies!
View Related
Working Out Averages
I havea list of values that I need to work averages out for. Unfortunately, sometimes the values will be a zero and these should be ignored. I can work out the averages easily enough using the average function, but can't quite figure out a way of ignoring the zeros. Sample data would be : week 1  3.42 week 2  0 (zero) Using the standard average function, I get an average of 1.71 when I actually want it to have an average of 3.42. I would prefer a function for this but would settle for a macro even though it would increase the program runtime.
View Replies!
View Related
Splitting Averages...
If I have a varying number of figures(say between 4 and 10) which I want the average of the first half of the set and an average of the second half, is there a function which I can use to calculate this? ie if there is 8 numbers in total then I need the average of the first four and last four... but if there is ten figures total then I need the average of the first five and last five.
View Replies!
View Related
Averages By Week By Month
I have the following data by week:  29Mar 5Apr 12Apr 19Apr 26Apr 3May 10May # of work days 4 4 4 4 5 5 5 Manpower 106 85 115 115 120 121 87  On a second tab, I want to show the data by month:  Apr May Jun # of workdays 21 (4+4+4+4+5) 21 20 Avg. manpower 108 104 .... 
View Replies!
View Related
Averages  Specific Characters
I need a formula that determines the average of numbers based on specific cells. For instance: COLUMN A COLUMN B John Doe 1.48 Jane X 2.89 John Doe 1.89 Jane X 5.98 Michelle Doe 5 Claire Doe 3 Jane X 0 What formula can I use to give me the average of "Jane X" not taking into consideration the one with the 0 Value?
View Replies!
View Related
Variable Range Averages
I was able to record a macro that did essentially everything I wanted it to, with the important exception of getting the right selection for the average=() calculation. My worksheets are set up with multiple variable columns, and multiple data collection stations as rows. The rows are sorted by date, with a blank row dividing the data by month. There may be 50 rows in January, then a blank row, then 4 rows in February, then a blank, then 15 rows in March, etc. I can easily average the monthly values in, say, column N by typing "average(" in the cell where I want the average to be calculated, then selecting the top cell for the month of interest in column N, holding the shift key down, pressing end, pressing the down arrow, and then typing ")". The macro recorder, however, doesn't seem able to handle this method of selecting the cells to put inside the formula parentheses. If I try to run the created macro on other months, it will average the same number of cells targetted by the initial example (i.e. the month I was working with when recording the macro). Is there any way to code this operation so it will vary the number of cells averaged, based on the number of data rows present in each monthly group? My research into coding averages is turning up only examples that deal with whole columns or fixed cell ranges. Since I have a pretty big stack of worksheets to work though, it would be very nice to automate the process, but I'm still fumbling here. Not getting anywhere.
View Replies!
View Related
Averages With #N/A In Row/column
I am trying to work out the average volume of our daily downloads. I have used Vlookup to automate this. When there is no data it returns #N/A which is no problem. The problem arises when i try to work out the average for the month or peaks (MAX sum). I have tried several variations of sumif/if/isna etc etc and am not having much luck. Below is an example of what i am trying to average out: #N/A620#N/A That is 4 different days download data  I want it to ignore the 0's and #N/A and return the average for the days we actually had a download.
View Replies!
View Related
Getting Averages From Seperate Worksheets
The first one, I do i remove #DIV/0! from cell O7 in the worksheet "My overview"? The second one, I need to get averages of the QA score for the 3 months from January to March in the worksheet "My Overview" Cell E14 for the corresponding person on each sheet. I understand that there need to be values in place to get an average but is there a way to overcome this?
View Replies!
View Related
Top/Bottom X Averages
Have a five column worksheet with the column headings run column A1  E1 and there are currently 112 players filling out the data with many more to come The 5 Column Headers are as follows: Player# ... PlayerName ... M/F ... SessionAve ... GamesPlayed What I need to do I pull out the top 5 male and top 5 female by SessionAve (including ties if possible). Would like the info to look like: Male Shooter ...Session Ave ...GamesPlayed Player1 .843% 30 Player2 .837% 35 Player3 .835% 25 etc.. Then Same for Female Players
View Replies!
View Related
Using Loops For Taking Averages
I have a column of 96 numbers (observations every 15 minutes for a 24 hour period), and I want to take the average of these numbers in groups of four (the hourly average). My data starts in cell A2 and goes to cell A97. My first batch of averages are labeled as follows: Range("A2").Select startCell = ActiveCell.AddressLocal ActiveCell.offset(3,0).select stopCell = ActiveCell.AddressLocal Now, clumsy programming aside, I just want to say something like this averageCell = AVERAGE(startCell:stopCell)
View Replies!
View Related
Averages The Total Of Each Criteria For All The Paygrades
I have a spreadsheet with one "master" sheet and multiple "level" sheets. The workbook is an employee rating system and employees have different pay grades. The individual sheets represent each paygrade while the master sheet averages the total of each criteria for all the paygrades. Look at the image below: http://www.excelforum.com//attachmen...1&d=1165960332 What you see above is a cutout from one of the "level" sheets to give you an idea of how employees are added to the sheets. Sheets I have: Master/E1/E2/E3/E4/E5/E6/ The first catch is that the sheets will not have an equal number of "rated employees", so I would need to tell the program how to find the number of employees being rated (and how to reference the columns to find the total scores in). In the example you see name1 and name2... this represents 2 employees. To add another employee they duplicate the 3 rows and create another employee (i.e. name3). Second is that it has to disregard total scores that are "zeroes", meaning it does not average the total scores with a zero. The master sheet is just a duplicate of the "level sheets" but with 1 set of 3 row columns to average each criteria from the 6 "level" sheets. I appreciate your guys' help in advance. Thanks!  Kareem P.S. Those that are wondering, Total score = Weight(blue column) * points (110)
View Replies!
View Related
Averages From List Of Dates/currencies
I have a list of dates and the exchange rate at that time e.g.: 18/02/20051.449921/02/20051.451322/02/20051.441423/02/20051.444624/02/20051.4445525/02/20051.45228/02/20051.4508 What I need to insert are three formulas, 1 that works out the average for the last year, 1 for the year before that and 1 for between two given dates.
View Replies!
View Related
Capture The Averages (of Cells With Values)
I have a workbook with several sheets , the 1st 31 being named Mar.0107, Mar.0207,etc I'd like to capture the averages(of cells with values) from cells B7:C24 of sheets Mar.0107 thru Mar.3107 and place the values in the sheet called "averages" with the average for B7 going to cell B7 in the "average " sheet, B8's average going to cell B8 in the "average" sheet and so on.
View Replies!
View Related
Userform [Averages Of Textboxes IF Populated]
I am trying to create some VBA code, which will average the contents of up to twelve textboxes on a Userform, and then put the average in the next one. I have sorted this, HOWEVER.. I have a couple of problems. The average isn’t correct Some of the textboxes may be blank, and therefore should not be included in the calculation Here is my code Private Sub CommandButton3_Click() Dim results(12) As Double Dim ave As Double results(1) = CDbl(TextBox5.Value) results(2) = CDbl(TextBox6.Value) results(3) = CDbl(TextBox7.Value) results(4) = CDbl(TextBox8.Value) results(5) = CDbl(TextBox9.Value) results(6) = CDbl(TextBox10.Value) results(7) = CDbl(TextBox11.Value) results(8) = CDbl(TextBox12.Value) results(9) = CDbl(TextBox13.Value) results(10) = CDbl(TextBox14Value) results(11) = CDbl(TextBox15.Value) results(12) = CDbl(TextBox16Value) ave = Application.WorksheetFunction.Average(results) TextBox17.Value = ave End Sub
View Replies!
View Related
Weighted Averages Using SUMIF And COUNTIF
DATA: D1:D11 contains either blank cells or a dollar figure from a payroll check. E1:E11 contains either blank cells or the date of the check. Current Situation: Using the following formula I obtain a weighted average of the pay checks, not including the largest. =(SUM(D1:D11)MAX(D1:D11))/(COUNT(D1:D11)1) Problem: I desire to calculate the weighted average based on the checks issued only prior to a certain date. I have used SUMIF() in other parts of the workbook successfully but needing to withold the largest value has presented me with a problem I can not find a solution to. =SUMIF((E1:E11,">="&TargetDate,D1:D11)MAXIF(E1:E11,">="&TargetDate,D1:D11))/COUNTIF(E1:E11,">="&TargetDate,D1:D11)
View Replies!
View Related
Function To Calculate Averages Of Percentages
I am wondering if there is a function in excel to calculate the average of multiple percentages. Currently I rely on the following formula to achieve this result and I am curious if there is a quicker way: Try this formula. ..... Just today I learned there is a function to calculate the compounded return (FVSCHEDULE). It does part of what I am looking to do by taking away the PRODUCT function and the array, but does not really simplify things. Using FVSCHEDULE my function would look like ....
View Replies!
View Related
