# Monthly Average From Daily Data

Oct 28, 2011
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.

View 7 Replies
ADVERTISEMENT
Jul 26, 2014

Is 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

View 3 Replies
View Related
Sep 9, 2013

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â€Ž

View 9 Replies
View Related
Apr 7, 2014

I 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?

View 9 Replies
View Related
May 20, 2014

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 Related
Jan 2, 2009

In 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

View 6 Replies
View Related
May 11, 2008

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 5 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
Jan 27, 2014

Basically i have month end data ranging from 31/01/2000-31/01/2009 with a value attached to each. I need to convert this data into a daily series with the month end value being the same throughout the whole month. I've been playing around with excel for a couple hours tonight

View 9 Replies
View Related
Mar 27, 2013

I cannot solve with Excel 2010 and I have searched all over for the answer.

I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.

For example, I would like to turn this:

3/2/2005 $xxxx

3/5/2005 $xxxx

3/20/2005 $xxxx

4/2/2005 $xxxx

4/10/2005 $xxxx

Into this:

March 2005- $xxxx (monthly average)

April 2005- $xxxx (monthly average)

I have a feeling some 'countif' formula would work but I am not sure how to do this.

View 4 Replies
View Related
Jan 15, 2009

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 2 Replies
View Related
May 22, 2014

I have a spreadsheet that contains developer related data. I want to be able to do a weekly analysis of average construction time and costs. The problem I am running into is properties enter rehab phase and exit rehab phase throughout the week.

For example:

I want to know the average $ spent and total rehab time week 1 of april. I may have 4 homes that entered rehab that week and 3 that move out of rehab that week. Moreover, I need to ensure those homes that entered and are still in rehab for that week are counted. How do I accomplish this feat?

View 2 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, 2014

I want it to average based on month and year. I have daily data and want to average everything for say January 2000 into one cell and February of 2000 and so on. Column A has date (mm/dd/yy) and column B has data.

View 13 Replies
View Related
Apr 7, 2014

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 Related
Jan 8, 2010

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.

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
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
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
Oct 17, 2011

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?

View 1 Replies
View Related
Sep 14, 2009

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 auto-average 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 auto-update itself to look at the next 96 values and so on and so forth?

View 11 Replies
View Related
Nov 13, 2009

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 9 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
Jun 3, 2009

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 "date-time" 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 3 Replies
View Related
Feb 5, 2010

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 13 Replies
View Related
Mar 30, 2005

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 9 Replies
View Related
Aug 3, 2009

I’m currently pulling data into two columns labeled “Monthly” & “Non-Monthly” respectively. They indicate work orders with a frequency of “Monthly” or “Non-Monthly”

The Monthly data is obtained using the following formula:....

View 9 Replies
View Related
Jun 13, 2014

I am trying to create a very basic workbook that has 2 worksheets. one is a daily input for tonnes, that then just gets cut and pasted to a different program, and the other worksheet is the running total. i.e., it adds up every time you update it.

been trying to figure out a macro so when you press the update button it then just updates the monthly total.

Colac Production.xls

View 7 Replies
View Related
Feb 16, 2014

I have a set of 7 years daily rainfall data. I need to calculate the daily average of rainfall from this 7 years data set. I don't wanted to calculate it by for example copying and pasting the first day rainfall of each year rainfall to a new sheet and average it for all the days. I need to calculate it all at a time.

View 1 Replies
View Related
May 27, 2014

HOW TO MAKE DAILY AVERAGE IF I HAVE DATA SET WITH 15 MIN SETS

View 3 Replies
View Related