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")

ADVERTISEMENT

Calculating Quarterly Averages From Weekly Data?

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

Calculating Daily Averages

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

Monthly And Quarterly Averages From Another Sheet

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

Quarterly Interest Calculating Formula

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

Calculating Quarterly And Yearly Totals From Dates?

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

Calculating Averages In Spreadsheet

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

Calculating Average Of 2 Averages

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

Calculating Average From Averages

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

VBA Macro Code For Calculating Averages Those Which Have More Than One Value?

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

Calculating Averages In Mark Book

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

Formula: Calculating Averages To Reach A Goal

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

Calculating Averages :: Weekly Demand Of A Product

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

Calculating Daily Quantities

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

Calculating Reducing Daily Pro Rata Amounts

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

Average Daily Balance - Recursive Equation For Calculating Interest

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

Finding And Calculating Highest Trending Item By Daily Sales?

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

Auto Copying Cells Value That Changes Daily Without Affecting Daily Value?

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

Quarterly Data To Monthly

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

Automate Quarterly Calculation

Jul 29, 2009

how do i get excel to automate quarterly calculation? in column A1 I have Year and column B1 i have months (1-12) 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 9 Replies View Related

Combine Quarterly Bars Get Messed Up?

Feb 5, 2014

Each quarter should have the bars standing next to each other, i.e. Q1 2013 should stand right beside Q1 2012. Between these pairs of quarter there should be a small space and the the next pair, Q2 2013 and Q2 2012, should be. So how do I do this?

When I use data without stacking several info in the same bar, this isnt a problem but when I have this "aaa, bbb,ccc,ddd" in every bar, this gets messed up.

View 2 Replies View Related

Monthly To Quarterly Cash Flows

May 13, 2014

I am building a Discounted Cash Flow.

I have monthly cash flows rolling for 10 years, which I need to sum up into quarterly cash flows.

View 1 Replies View Related

Quarterly Goal Pacing Calculation

Dec 8, 2012

I am very new to excel and my formulas dont add up.

I need to calculate based on my quarterly goal and my quarter to date actual sales what my percentage pacing is.

Now the way i did it is =B5/A5

Do i need to add the Quarter date calculations in there because its not giving me the accurate percentage.

Goal Actual Pacing

View 3 Replies View Related

Summarize Quarterly Data In The Summary Table?

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

Lookup Quarterly Periods Against Multiple Dates?

Feb 26, 2014

My lookup formula is not hitting the correct period. How can this be adjusted without changing any of the "GRAY" data.

View 2 Replies View Related

Linking Input Dates To Quarterly Reports In Different Tabs

Jan 27, 2014

I'm lookng for a way to add functionality across tabs in a spreadsheet where if you put a date and $ amount in different cells on an expense sheet, it recognizes the date of the amount input and it shows up in another tab with a list of monthly expenses.

View 4 Replies View Related

Manipulate Date Ranges For Monthly And Quarterly Reports

Feb 4, 2009

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 7 Replies View Related

SUMPRODUCT Quarterly Average -> For Multiple Product Categories

Dec 31, 2013

I have data table with sales numbers by product, its wheel base, and over platform for months ranging from Aug 2013 - Nov 2013. This data table will populate with the complete month sales numbers after the month has finished going forward.

I have a table to the right of the listing the sum totals for each product (by wheel base and platform) by each quarter (rolling).

Now I need building a sumproduct average to calculate the quarterly average. This is not that simple because not all products (platform/wheel base) were available the entire quarter.

Also PLATFORM 3 in the tables are grouped - instead of listing platform 3a and platform 3b - using this in the formula:

(LEFT($D$2:$D$100,LEN($J9))=$J9)

Because not all products were available for the entire quarter - and the formula will need to count how many months that product, platform, wheel base was available during that quarter I'm guessing "=MOD(MONTH(A1)-1,3)+1" this may have to be used to count the number of months into the quarter the product was available.

Attached file. [URL] ........

View 9 Replies View Related

Form Sub-totals Quarterly In Typical List Of Transactions?

Feb 21, 2014

How to form sub-totals quarterly in a typical list of transactions. Twist being it is not calendar year quarters but quarters from from 5 April to 5 July, 6July to 5 October etc. Typical table columns looks like:

Date Amount Transcation
5/4/14 200.50 bolts
7/5/14 50.10 screws
6/6/14 10.00 bolts and nuts
--------------------------------------------
SubTotal 260.60

12/7/14 10.00 bolts and nuts
10/8/14 40.00 rivets
4/10/15 10.00 screws
--------------------------------------------
Sub Total 60.00

View 1 Replies View Related

Gantt-type Chart With Quarterly Marks On The Date Axis?

May 22, 2014

I'm using a stacked bar chart (in Excel 2010, running on Windows 7) to create a simple Gantt-type chart - with just four or five bars. I've got my chart looking most of the way I want it to, but one thing still eludes me: I'd like to set up the major axis ticks to be quarters of the year (from 1/1/2011 to 4/1/2013). Since quarters are not regular intervals (they are not exactly every 90 days), I can't do this using Excel's standard functionality for choosing axis tick marks.

View 3 Replies View Related

Rolling Averages

Jun 5, 2007

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 12 Replies View Related

Averages With #N/A In Row/column

Jan 13, 2009

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 2 Replies View Related

Splitting Averages...

Feb 2, 2009

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 2 Replies View Related

Getting Averages To Round Up?

Mar 8, 2013

I have a spread sheet with col's that I want to average their totals, but would like them to be rounded up or down.

View 3 Replies View Related

Averages When Using A Filter?

Apr 7, 2013

I am using the formula =AVERAGE('Year 13'!BV:BV) to work out the average value in column BV.

However, I would like the value to change if I filter one of the other columns, is there any way to do this?

View 2 Replies View Related

Automatic Averages

Jul 13, 2007

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 10 Replies View Related

Averages From 2 Columns

Jan 6, 2006

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 10 Replies View Related

Average Of Row Averages

Jan 31, 2012

I want a cell that takes each row in column B and subtracts column A from it... (10-5 for row 1). I want it to do that for all rows and then average that number. This would be easy if I were not using a pivot table with a lot of data.

For example:
Column A Column B
5 10
2 9
6 8
1 4
6 7
1 1
6 2
6 8

View 2 Replies View Related

Working Out Averages

Oct 2, 2006

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 run-time.

View 9 Replies View Related

Averages Formula

Mar 1, 2007

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

Averages For Last 12 Entries

Oct 20, 2008

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

ADVERTISEMENT