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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
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
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
Daily Average Formula
I need to count the daily average of a task to a week ending number. I need to see the current average after each day during the week. Example – Mon = 2, Tues = 4 AVERAGE is 3 – Wed = 2 AVERAGE IS NOW 2.6…and so on averaging out after each day is added.
View Replies!
View Related
Average Daily Values
I have a table of data covering the last 9 months based on values automatically collated from 15 minute intevals. The date/time is in column A (01/01/2009 00:00) with the data collected in column D. My wish is to get the average daily data from column D and I am slowly losing my head!!! Is there anyway of getting a formula to autoaverage the daily values bearing in mind there are currently 96 daily entries. I have tried converting the first 5 digits of column A to numeric (i.e. 31894 for 01/01) then trying to write a formula saying =average(D1:D24577,if(range="31894",1)). I can now see a simpler way but am so confused after an hour or so of trying. Each day has 96 readings so I need an auto adding formula. average column cell A would say =average(D1:D96). Is there are way to have the cell below autoupdate itself to look at the next 96 values and so on and so forth?
View Replies!
View Related
Average Formula On Daily Basis
How to go about fixing my spreadsheet so I am not having to manually update it each day..here is my forumla I am currently using...=(AVERAGE($D$2:$AH$2)C5)*AI5...basically i need the cell below in D2 to change as every day a new day rolls off..for example the following day I need this formula to be =(AVERAGE($E$2:$AH$2)C5)*AI5 ....so just that day changes.....do I need to use an If/then statement? if so how?
View Replies!
View Related
Calculate Daily Average Of The Several Variables
I'm working on a time series dataset with a time step of 15 minutes. I need to calculate daily average of the several variables. So let column "A" be the "datetime" column, let column "B" be the "variable column" and column "C" be the "average column", I need a function that calculated in C1 cell the average of B1 to B95 cells, in C2 cell the average of B96 to B190 cells, in C3 cell the average of B191 to B285 etc.
View Replies!
View Related
Formula For Average Daily Balance
Am trying without success to create a formula to calculate average daily balance from a ledger that has a variable amount of entries per month. The variability of # of entries has me stumped. For example: date, amount 1/1/2005, 10 1/5/2005, 1 1/10/2005, 4.65 1/18/2005, 7 1/22/2005, 20 Aver Daily Bal = 23.78. and I can get this easily manually, but I'd like a more automated solution. I'm trying a sumproduct angle, to no avail.
View Replies!
View Related
Calculate An Average Daily Expense Cell
I've set up a simple spreadsheet to keep track of my food expenses every month. The first column is for the date, the second is for the daily total expenses and then the next three columns are where I add the data which is then calculated into the daily total column. I also have a total at the bottom for the entire month. Now what I want to do is I want to also have underneath the grand total, a cell which keeps track of my average daily expenses. Basically I want to divide the total expenses by the number of days which I've entered data. Now normally this would be fine but because I've applied the formula to all the cells in the expense column, it automatically lists every day as "0" rather than leaving it blank. So when it does the average calculation it's dividing my total by 30 days rather than by only the 7 days I have data for.
View Replies!
View Related
Average By Month ...
I am trying to input monthly budgets based on a yearly budget for which I already have a few month's budgets. For example: Jan  ? Feb  ? Mar  ? Apr  ? May  ? June  $1105 July  $1325 Aug  $1470 Sept  $835 Oct  ? Nov  ? Dec  ? TOTAL YEARLY BUDGET  $10,000 I need to find out what the other 8 months are, on average. I'm looking for something along the lines of each month's average IF SUM(A3:L3)=M3
View Replies!
View Related
Average By Month
i have the following formula =SUMPRODUCT((TEXT(DSA!$E$5:$E$10000,"mmmyyyy")=$C$4&$C$3),(DSA!$AW$5:$AW$10000)*(DSA!$F$5:$F$10000="")*(DSA!$AJ$5:$AJ$10000="TLS")) which basically looks at the Month and Year in reference cells then looks through my data on a sheet called DSA.. I have a colum on the DSA sheet AX5:AX10000 where there is a number I want to be able to calculate the average of these numbers but only if the conditions in the above formula are met. For example, if the month was MAR 2009 then i would only have the average of numbers present in Colum AX on the DSA sheet for March 2009.
View Replies!
View Related
Average By Weekday Of Month
I have a sheet that contains a number of documents entered into a system by a user. Each month those number of documents are average by the number of working days in a month. I'm using this formula, =AVERAGE(C4,22) I would have to edit this for each month with the number of working days. Is there a way I can have this formula automatically find the number of working days in a month specificed and average it out?
View Replies!
View Related
Average Times By Month
I am looking for a macro that will allow me to find the mean for all of a specific time during a month. For Example, I want to find the average percentage of capacity for every day at 11:00 am for the month of January. Percentage of Capacity By Hour Hour Month/Day/Year 0.00% 11:00 1/22/2007 0.00% 12:00 1/22/2007 0.00% 13:00 1/22/2007 0.00% 14:00 1/22/2007 0.00% 15:00 1/22/2007 0.00% 16:00 1/22/2007 0.00% 17:00 1/22/2007 0.00% 18:00 1/22/2007 0.00% 19:00 1/22/2007 0.00% 20:00 1/22/2007 0.76% 21:00 1/22/2007 1.59% 22:00 1/22/2007 1.38% 23:00 1/22/2007 2.68% 0:00 1/23/2007 1.75% 1:00 1/23/2007 3.65% 2:00 1/23/2007 3.40% 3:00 1/23/2007 2.41% 4:00 1/23/2007
View Replies!
View Related
How To Create Month To Date Average
I'm having difficulty understanding this concept, more so the formula. So to figure out the MTD for Day 1,2,3,4, how would it be? I want each day to have a MTD etc. Day 1: 33,966,611.42 Day 2: 33,966,611.42 Day 3: 33,966,611.42 Day 4: 34,074,098.36
View Replies!
View Related
Average Based On Criteria :: Within Each Month
I have a list of ages of people who cancelled their account. I'd like to find the average age of people within each month. So column A = age and column B = cancel date Let's so for January 2006 I'd like to find the average age. Logically this is how I see it... Average Column A (If column B >= 1/1/2006 and < 2/1/2006)
View Replies!
View Related
Find Average Of The Specific Month
I have two columns: 1) Last Date of each month from 2005 and 2) Value. Date format: dd.mm.yy For example: 31.01.05 1.7 28.02.05 0.7 31.03.05 2.6 30.04.05 0.4 .... How can I find the average of second column of january months only? I tried countif, sumif and many others, but cannot find the solution.
View Replies!
View Related
Cumulative Sum For Weekly Average Hours Over A Month
I have a column called "Weekly Working Hours" which totals the number of hours worked per week. The cell is filled in every Saturday. In the next column I have "Average Weekly Working Hours per Month" which needs to calculate the average number of weekly hours every four weeks, filled in every Saturday. Please see attached file. I am referring to columns J and K ....
View Replies!
View Related
Finding Average Based On Month And Year
I have 5 years of data in 2 columns: Col A. Col B. 8/2/2004 Value 1 to 7/31/2009 Value n I have a table set up as follows 1 2 3 4 5 6 7 8 9 10 11 12 2004 2005 2006 2007 2008 2009 I was wondering how I could construct a conditional statement to pull the associated values with the given month and year in the table... I tried the following to no avail... I'm just getting a zero value: =AVERAGE(IF(MONTH(J6:J1255)=AC$35,IF(YEAR(J6:J1255)=$AB37,K6:K1255)))
View Replies!
View Related
Average Based On Time & Month Cells
How do I average the data in column G, based on two conditions (time in column H = $H$1, month in column I = month in $I$1) Here's the formula I came up with, but it is including blank cells in the calculation. { =AVERAGE (IF ($H$3:$H$100=H1, IF (MONTH ($I$3:$I$100) = MONTH(I1), $G$3:$G$100, FALSE), FALSE)) }
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
Average With Conditional Month, Year And Date Range
I collect unique prices each day. I am trying to find a way to determine the average of the numbers collected from the 21st of the previous month to the 20th of the current month. This formula will need to calculate for multiple months and years. So for example, I need Feb2008 averagewhich would be the average of numbers found between Jan21Feb20, I then need Mar 08 average which would be data from Feb21Mar20 etc. My spreadsheet is setup with the first column having the dates (ex. 01/01/08, 01/02/08 etc) and the second column containing the value for that particular date ($2.85, $3.00 etc). As the number of days between the 21st and 20th change each month, I just can't seem to find a way to do it without a whole lot of manual effort.
View Replies!
View Related
How Do I Create A Running Average That Will Only Calculate The Averages In % Each Month
I need to do the following and can't figure it out. How do I create a running average that will only calculate the averages in % each month. Example: Opt 1 for Jan, Feb, Mar =1 each= 3 total = 100%; OPt 2 for Jan, Feb, Mar =1,0,1= 2 = 66%; Opt 3 for Jan, Feb, Mar = 0, 0, 1 = 1 total = 33%. My problems is I want monthly running average that shows the yearly percentage up to date but only for the months there is a value 1 or 0. How can this be done because the way I have it now, those % are being divided by 12 and that isn't the correct %
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
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
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 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
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
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
Get Pivot Data: Pull Out The Data From The First Column(A), And Then Average
I have a Pivot Table, with lots of data in, what I want to be able to do is on a separate sheet pull out the data from the first column(A), and then average out the figures from Columns(BE). When this has been done on the sheet (with the average data) in the next column, I then want to use Column(F) (from Pivot Table), and do a simple sum to work out 5% of the difference if it is greater than ZERO between Average Column and Column(F) (from pivot Table) Column(F) must be higher than the average in order to work out the 5%.
View Replies!
View Related
Average Data In Column
I do not use excel very often but know there is a simple formula to take the average of numbers inputted into a column. For example, I would like to sum all the values entered into column “D” and then divide that by the number of entries 4 5 3 1 4 11 6 2 0  36/9 = 4 If I were to add another entry it would automatically change the divisor to 10
View Replies!
View Related
Average Only Columns With Data
I have twelve columns of data nine of which are filled. I'm trying to come up with a formula that averages a column only if it has data in it, I have attached a sample file. As I add data the average should change without changing the formula.
View Replies!
View Related
Compare Data For Month
Each day I have workbooks saved that contain data of alarm files – saved format “PAR Process Alarm Report_MMDDYYYY”. Within each workbook there is a sheet named “E&H” for each day. The goal is to figure out which Tag is causing the most problems. 1 Tag may hit 100 times in a day and then again 40 times a week later. Another tag may hit 2 times a day every day  This is the tag we would want to tackle. I’ve attached 1 of the E&H sheets so you can see what they look like. Comments are in row 1 explaining what they are. I have code provided here that will go into each workbook. I’ve commented out the part that I think needs to be edited to make this all work but I could be wrong. This will be extremely helpful if I can get this all to work.
View Replies!
View Related
Sum And Weight Average Variable Data
Hi there, been using this for a number of years now, but never had to post a question  I guess most things are already answered  but this one is a bit more tricky. I want to calculate a totals and a weighted average based on months from a data set that has duplicate ID's and variable data (attached example). I would like to calculate the total tonnes by month and the average grade of those tonnes. Using the 'month ID table' as a reference to the 'Raw Data table'. Is this possible using if and vlookup functions or will require a higher level of programming knowledge?
View Replies!
View Related
Average Of Last 12 Rows Of Expanding Data
I have a table of data to which new figures are added each month as follows: MonthHeadcount Jan081867 Feb081942 Mar082020 Apr082020 May082020 Jun081980 Jul081941 Aug082000 Sep082000 Oct082060 Nov082060 Dec082143 Jan092101 Feb092101 Mar092186 Apr092208 May092319 Jun092389 Jul092437 Aug092486 Sep092462 "Month" is in A3, "Headcount" is in B3. Figures are on rows 4:24 What I need is a formula that will average the last 12 rows of data so that when a new record is added, the average will update automatically to include the newly added row, but still only look at the 12 rows (i.e. for the data as listed it would average rows 13:24 (Oct08 to Sep09), but when data for October is added, it would average rows 14:25 (Nov08 to Oct09).
View Replies!
View Related
Hourly Average With 15 Minute Data
I have a large sheet with the values of power from a counter in a 15 minute base. A B 01.01.09 00:15 0,25 01.01.09 00:30 1,15 01.01.09 00:45 0,75 01.01.09 01:00 2,01 and son on until the end of the month What I am trying to do is to create a macro which calculates the average per hour and put in another columns like this: 01.01.09 01:00 average from 00:15 until 01:00 01.01.09 02:00 average from 01:15 until 02:00 and so on until the end of the month.
View Replies!
View Related
Average Data With Specific Criteria
I have a data base with 12 columns of data. I need it to look for one criteria in one row, match it, look for another criteria in another row, match that, and then once those criteria are met, average those rows that met the criteria with the statistics in that row that contains a value. Such as: Monica 2 Timed 310 Michele Tom 3 UnTimed 410 TC Art 5 Timed 216 TC Monica 4 Timed 415 Michele Tom 6 Timed 200 TC Tom 4 Untimed 216 TC Art 8 Timed 410 TC What I need is the formula to search in through the data, look for Tom, then find all the ones that say UnTimed, and then average the number in column 2 that match those criteria.
View Replies!
View Related
