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")
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.
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.
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:
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.
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.
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...
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
I am currently using the following formula to calculate the average weekly demand of a product:
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.
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.
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....
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?
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.
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.
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?
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
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.
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
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).