# Calculating Quarterly Averages From Daily Data

Jan 19, 2007
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/2000-31/03/2000, 2000Q2 will be average of values between 01/04/2000-31/06/2000, 2000Q3 average(01/07/2000-31/09/2000) and 2000Q4 will be the average of (01/10/2000-31/12/2000) etc. for all years afterwards.

I want to have the values in the corresponding cells starting with range ("e2")

View 6 Replies
ADVERTISEMENT
Jan 20, 2014

generating a formula that takes the weekly values in a quarter and uses them to generate a quarterly average. Quarters are split up as follows.

Q#1: 09-11 (First week of September-last week of November)

(Used to generate price for January)

Q#2: 12-02 (First week-Last week)

Q#3: 03-05 (First week-Last week)

Q#4: 06-08 (First week-Last week)

I've attached an excel sheet with some dummy data.

View 3 Replies
View Related
Aug 9, 2006

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 4 Replies
View Related
Apr 15, 2014

I need a formula that will provide monthly and quaterly averages from another worksheet, but only include the months that have occurred. I have the formula to obtain both the averages and quarter totals, but it is counting all months w/in the quarterly range before the months have occurred.

View 5 Replies
View Related
Oct 21, 2009

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 8 Replies
View Related
Jan 5, 2012

I have a column that contains dates for an event. I would like to tally quarterly and yearly totals for these dates. What formula can I use to accomplish this?

View 1 Replies
View Related
Feb 19, 2008

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 9 Replies
View Related
Jun 7, 2013

I have a spreadsheet that we are using as a Skills Matrix for the team. The area that the team member deals with is listed in Row 3. The scores for each person are recorded in Columns D to M. The process steps being scored against are in Rows 8 to 38, and are seperated by the area of responsibility.

I am trying to work out the average scores of each person depending on their area of responsibility. I can work out the totals easily enough using:

=IF(ISNUMBER(FIND("CM",D3)),SUM(D11,D12,D13,D14,D18,D19,D20,D23,D24,D25,D26,D27,D28,D29,D30),0)

+IF(ISNUMBER(FIND("V",D3)),SUM(D33,D34,D35,D38),0)

But if I use the same for the average scores is works fine until a person covers two duties:

=IF(ISNUMBER(FIND("CM",D3)),AVERAGE(D8:D10,D12:D17,D19:D22,D24:D30),0)

+IF(ISNUMBER(FIND("V",D3)),AVERAGE(D31:D32,D34:D37),0)

This is becuase it adds the 2 averages instead of working out the total average score. Is there an easy way around this?

i.e. For team members who have CM in Row 3 I need an average of certain cells, for team members who have V in Row 3 I need a different average and for those team members with both CM and V I need an overall average.

View 1 Replies
View Related
Oct 1, 2013

I have a database with monthly tabs and a summary sheet, on the summary sheet I calculate an average length of stay for each month (tab) but I need to find the yearly average and I don't know if there is a formula that would calculate the true average by using the monthly averages.

View 2 Replies
View Related
May 2, 2014

I need vba macro code or excel function to calculate average for some intervals in my excel sheet which have more than one value... I need all those values to be get averaged and placed in one cell...

I have intervals like 0-2, 2-4, 4-6.... 22-24.

In these intervals, few have got more than one values like

16-1804/11/201416:29:2616503.9902

04/11/201417:52:4917390.0293

18-2004/11/201419:52:5517204.8086

Now I need 16503.9902 and 17390.0293 need to get averaged and placed in one cell like,

16-1804/11/201416:29:2616947.00975

18-2004/11/201419:52:5517204.8086

View 14 Replies
View Related
May 10, 2006

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 3 Replies
View Related
Aug 27, 2007

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 9 Replies
View Related
Apr 28, 2008

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 9 Replies
View Related
Nov 22, 2009

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 7 Replies
View Related
Dec 5, 2011

Here's my problem.

Charter default premium is a calculated as a rate on outstanding charter hire.

Therefore if charter period is 1 year and daily hire is 20,000, outstanding charter hire (policy limit) at inception would be 20,000 x 365 = $7,300,000

However, by day 365 outstanding charter hire and prevailing policy limit would only be 20,000 x 1 day = $20,000

The premium is calculated on a daily pro rata basis on the reducing outstanding amounts.

I've calculated the premium on the attached spreadsheet assuming hire is monthly not daily, but it would be too laborious to try and calculate that for 12 months or more on a daily basis. What I'm hoping is that there's a formula, covering the range of data, presumably using the calculation on the first day and the last day, but which cuts out all the intermediate steps.

View 3 Replies
View Related
May 21, 2013

I create this spreadsheet as a loan schedule using average daily balance method. (1/payment is constant, fortnightly 2/interest is 5.5% per annum)

In the interest column, at the beginning of each month ( when the day is 1) the interest will be added up from calculation of previous month daily balance.

My idea is that at interest column(let start at 1/08/2013) if (day(A49)=1, average the 30 or 31 cells above E49, 0).

I will manually make adjustment for February where 28 or 29 days applicable.

View 1 Replies
View Related
Apr 20, 2012

I have a very large spreadsheet with daily sales for over 140 different items, over the past 2 years.

This is how my data looks like:

ITEM NAME 4/1/12 4/2/12 4/3/12 etc

item1 10 15 500

item2 5 0 3

item3 0 0 5

etc

I'm trying to build a formula that allows me to see which item is the highest trending over a period of time I input.

I figured out the way how to select data between specific Start and End dates, but don't know how to interpret "trending" in a formula.

I was thinking maybe highest jump from beginning to end of interval, but what if it's a new item that was launched halfway through the interval and does poorly throughout, that would still be "trending" under that rationale....

View 1 Replies
View Related
Jan 27, 2014

I am trying to convert quarterly data to monthly

The quarterly data columns go: Mar-14, Jun-14 etc

The monthly data columns goes Jan-14, Feb-14 etc

I am trying to use a vlookup and match formula but as you will see in the attached file it is only working for those months that are labelled in both data e.g. March 14, June 14

Is there a formula that will pick up for example that January and February numbers should be drawn from the March 14 quarter, April and May from June quarter etc?

View 2 Replies
View Related
Aug 7, 2014

summarize quarterly data in the summary table taking into consideration the months and the district. see the attachment for a more clear picture.

View 4 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
Jul 15, 2014

I have a fairly large set of data on one sheet containing numbers for 60 different people. The data is set up similarly to the set below:

John Smith 75

John Smith 80

John Smith 62

Jane Doe 49

Jane Doe 89

Jane Doe 66

So on and so forth until the 60th person. My question is: Is there a way to average the numbers for each different person at one time? What about median and mode as well? Do I need to set up a different sheet for that?

View 11 Replies
View Related
May 8, 2014

I have a problem converting hourly data into daily data for electricity import capacity. My file has the following form:

| Connection | Date | Period | Available (capacity) | Where 'Connection' identifies the physical location of the capacity (4 possibilities, Germany -> Netherlands; Norway -> Nehterlands; Belgium -> NL; UK -> NL), 'Date' refers to the date, Period refers to hour of the day (e.g. 18:00-19:00) and 'Available' refers to the import capacity of electricity on that conncetion during that hour. I need daily aggregates and tried several things such as DSUM but I cannot manage I've shortened the attached file quite a bit because of the upload size restriction. I am using Excel 2010. I've tried some suggestions in similar topics but none worked for me.

View 3 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
Oct 28, 2009

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 3 Replies
View Related
Dec 31, 2008

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 9 Replies
View Related
Feb 4, 2014

I have attached a tiny part of a massive data set I am working on. As you can see in column 2, the data is roughly every 15min for 5 days. The data I am interested in averaging is color coordinated in column 3 (if you scroll down you can see a different color for each day's data set.)

In column 5 the dates are summarized into days as opposed to the 15min breakdown. In column 6 is the problem. How do I get the averages of the relevant data in column 6 in such a way that I can drag the formula down and the next cell will automatically calculate the average for the NEXT day, REGARDLESS of how many temp readings there are, as this data fluctuates from day to day.

Excel Problem 2.xlsx

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
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
Apr 20, 2009

Column A = Date

Column B = Numbers

Column C = results

As you can see Column C, I manually did what I want the formula to do, which is get last number from the previous week. (weekdays only, but can be either 1,2,3,4 or 5 days due to holidays).

View 6 Replies
View Related
Jun 24, 2014

I am trying to create a macro that would copy three cell values - Date, Amount, Rate (from Sheet1) and paste them into Sheet2 as values.

In addition, this macro will be run for each business day's file, which only has the 3 values only as of that date; I want Sheet2 to be updated on a daily basis with historical data from past days (when the macro was run) and pasted as values.

For example, if i run the macro today, it should copy Date, Amount & Rate from Sheet1 ('Data' tab in sample.xls) and paste them as values in the next row after yesterday's data in Sheet2 ('Historic data' tab in sample.xls).

Sample attached; what is the best way to do this?

View 3 Replies
View Related
May 21, 2008

I have used the "Import External Data-Web 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 14 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
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
View Related
Dec 20, 2012

I have a spreadsheet that I am using for budgeting and I want to be able to track my savings by logging data from one cell.

If cell A1 represented my savings I want that copied to B1 then the next day when A1 changes the new value would be copied to B2 and so on.

Any way that I have tried so far B1 always gets overwritten when the new value comes along.

View 2 Replies
View Related
Mar 2, 2008

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 B-G (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 9 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
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
Apr 20, 2009

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 5 Replies
View Related
Apr 24, 2013

I have daily production sheets (Excel sheets) that I fill out every day indicating the day's production totals. The files are named by the day's date. For example today's sheet (4/24/13) would be 042413.xls. However, I would like to be able to pull certain pieces of information into another separate spreadsheet. For example, my boss asked me how many of a certain product we made over the last month. So, I'd like to pull the product info, the date made, and quantity made into another sheet. And I'd like to be able to do this automatically for a date range that I specify.

View 8 Replies
View Related