Free Microsoft Excel 2013 Quick Reference

Calculate Average Using Date Range

Can anyone possibly help with the following please:

I have a spreadsheet that contains the month in column A. Column B contains a list of numbers, but there are blanks in some of the fields in Column B. Basically I want to calculate the average of the numbers in Column B for each month shown in Column A.

See example data below. Can anyone help with a formula that can calculate average but exclude/ignore any blank fields in the number Field.

Month Number
May 2
June 3
May
June 4
June 7
June 9
June
June 12

Can anyone help?

Any help would be greatly appreciated.

Many Thanks


Post your answer or comment

comments powered by Disqus
I am trying to calculate averages for a range of temperatures associated with start and end dates. The data are arranged like so:

Date Temp
1/1/07 0.4
1/2/07 0.3
...and so on

And on the other worksheet,
Start date End date
4/22/07 11/18/07
8/17/07 10/25/07
(plus 1000 more entries...)

What I'm trying to do is get excel to find the temperature associtated with the start date, the end date, and then return an average of those temperatures. I tried combining the daverage function, but that didn't seem feasible for the list of start and end dates that I have, and I ran into formating problems. I tried to use the average and the lookup functions together, but that didn't work.

I'd rather not figure out the averages by brute force (i.e., one at a time) since there are so many, but I can't seem to figure out how to get a function to solve this. If anybody can help, I'd be grateful.

Thanks

Hi
is there any way i can calculate 4 & 12 week sales as per highlighted columns on the attached doc for each product code listed (use the date column to lookup the date range re 4& 12 weeks...)

thanks

I have two worksheets. One has my list of data and the other is to make it look decent for a report. The list of data has dates (MM/DD/YYYY) and sales price. Some of the sales prices are supposed to be zero.

I want to find sales within a certain date range (e.g.- everything in 2006) and average the sales amounts while removing outliers and zeros and display them on the other worksheet.

Any help is greatly appreciated.

I'm not sure if this is the proper way to ask an Excel qeustion, but I'm looking to determine how to search for the number of items in a date range using the COUNTIF command. Is there an easier way to do this?

I need to sum data from a colum using a specific date range from another
excel sheet. Sheet 1 contains my summary page extracting the sum from sheet
2 by listing date ranges on sheet 1 to find corresponding numerical data to
be summed that fall in the date ranges. Any ideas where to start?

I have three rows as follows
Column A Column B
11-Jul-2006 $10
12-Jul-2006 $20
03-Aug-2006 $65

Now I want to calculate the monthly total in different rows.
For July, Its result would be $30
For Aug, Its result would be $65.

When I try the following formula, it is not working when I give the date range
SUM(IF(H41:H43>DATE(2006,7,1)*(H41:H43

I have three rows as follows
Column A Column B
11-Jul-2006 $10
12-Jul-2006 $20
03-Aug-2006 $65

Now I want to calculate the monthly total in different rows.
For July, Its result would be $30
For Aug, Its result would be $65.

When I try the following formula, it is not working when I give the date range
SUM(IF(H41:H43>DATE(2006,7,1)*(H41:H43<DATE(2006,7,31),I41:I43))

But if I give only one condition, either above the date or below the date it
is working fine. What is the way to enter the date ranges in the formula?

Thanks in Advance

Hi, I am not an expert in excel and I am having trouble calculating averages using date constraints. In the worksheet I have a column that contains the date and time and another column that contains the temperature. The amount of measurments per day vary from 24 or less. I need to calculate the average daily temperature for each day of the year for 5 years.

Example
Column A Column B Column C
5/13/10 23:44 30
5/13/10 22:44 27
5/13/10 21:44 29
5/13/10 20:44 30
.
.
.
.
1/8/10 5:44 10

Any help would be greatly appreciated, Thanks!

Hello everyone,

after searching through this great forum I found part of the answer. But it is unfortunately not enough to solve my problem. My sheet is very large: 10200 rows and many columns. I need to calculate the average of many ranges within the sheet.

What I would like Excel to do:

I have two cells in which I would like to input the range; The first cell is the start of the range e.g. 3 and in the second cell is the end of the range e.g. 9.
I then want excel to calculate the AVERAGE of that range for different columns. The different columns is making my head hurt...I would like to get the AVERAGE for B3:B9 in one cell; in the next cell I would like AVERAGE of C3:C9 and so on...
I made a samplesheet which explains my problem better (I hope ).

Can anyone help?

Thank you very much,

Christian

Can't seem to get the correct format for this equation:

I would like to average numbers between a specified date range. For example, what is the proper formula if I wanted the average of Col A values between the date 3/1/2010 and 7/1/2010 in Col B

Col A Col B
1 01/01/10
2 02/01/10
3 03/01/10
4 04/01/10
5 05/01/10
6 06/01/10
7 07/01/10
8 08/01/10
9 09/01/10
10 10/01/10
11 11/01/10
12 12/01/10

Any help would be appreciated..
Thanks

=SUMPRODUCT((C8:C5000>L5),(C8:C5000<M5),AB8:AB5000)

I am trying to sum data based on a date range. I have tried SUMIF, SUMIFS, and now SUMPRODUCT. Even if I type in the dates, it doesn't seem to use them. Please advise.

I wasn't sure what to title this, so change if you need to.

I am using date ranges in a macro by putting the dates in between the pound signs. What I need to do is when the year changes, I want the year that I have in the date ranges to change also.
Example: If my range in the macro reads #6/12/12# to #7/12/12# then when the new year starts I need it to be #6/12/13# to 7/12/13#.

Thanks for any help you can provide
David

I'm trying to associate a variable to specific date ranges. For example, if A2>= 10/3/04 , then return "active", if A2=etc. This is what I have so far:

=IF(A3>=DATE(2008,12,29),Active,If(AND(A3=DATE(2008,10,3),Inactive,If(AND(A3

I wonder if anyone could help me?
In my excel file I have two workbooks. One workbook with data (production
schedule) and in the other workbook I have a pivot talbe to list jobs
delivered by customer name. What I would like to do is to report the data
between two date ranges in the pivot table. At this moment in time I only
have the option to choose a specific date or all.
Thanks
Matt Elms

Hi,

I have the following data:

A B
Owned Let Agreed
N 26/11/2010
N 30/11/2010
N 06/12/2010
N 19/12/2010
N 20/12/2010
N 15/01/2011
N 27/01/2011
N 29/01/2011
Y 04/02/2011
N 23/02/2011
Y 01/03/2011
N 11/03/2011
N 28/03/2011
N 16/03/2011
N 30/03/2011
Y 01/05/2011
Y 30/05/2011
Y 08/06/2011
Y 10/06/2011
N 10/06/2011
N 10/06/2011
Y 10/06/2011
Y 10/06/2011
Y 20/06/2011
N 27/06/2011
Y 02/07/2011
N 27/07/2011
Y 10/08/2011
N 16/08/2011
Y 16/08/2011
N 18/08/2011
N 16/08/2011
N 23/08/2011
N 23/08/2011
N 23/08/2011

What I am aiming to do is return the number of properties with lets agreed, depending on whether it is owned or not.

For example, the number of properties which are not owned with lets agreed between 01/01/2011 and 31/01/2011. The answer would be 3.

I have this formula for the date range:
=COUNTIF(B:B,"<=" & DATE(2011,1,31))-COUNTIF(B:B,"<" & DATE(2011,1,1))

I'm just not to sure how to incorporate the other criteria into this formula.

I'd rather this be in 2 formulas, one for owned properties and one for not owned properties.

Any help would be greatly appreciated

Hi,

Is there any easy way to calculate from a date range the number of days that fall in a number of sub ranges?

For example, a user selects a range of 1st Jan 07 to 1st Jan 08. From the total number of days in that range, I want to calculate how manyof those days fall from between the start date and 1st May 07, then how many between 1st May and 1st Dec, then 1st Dec 07 to 1st Feb 08 (of which my end date for the original range falls between).

Any advice on this?

Thanks in advance.

I'm trying to create a summary sheet as a second sheet in an XL workbook. I need to pull data from another sheet in the workbook, but only from a date range entered on the second sheet. Any help gratefully accepted! TIA

Is there anyway to calculate average of a range of data that will exclude N/a and null entries?

Thanks

Andy

Hi, I'm an excel beginner trying to get my feet and looking for some help and guidance. Basically what I'm trying to accomplish here is...find all the records in a date column within a pivot table that fall within two sets of dates (From & To), and return the average for a another column (Percent Accuracy) and calculate the average for that date range. I have tried several formulas with no success. As far as I got, i was only able to retrive one value matching a single date (formula shown in image for reference below), but no luck on the range.

Have included an image file for a visual of what I'm trying to accomplish, hope it's clear enough. If not, please let me know so I can provide further details. Thank you.

1. My From date will be keyed into cell B3, & my To date will be keyed into C3
2. Pivot table ($A$6) contains a "date", "total","Errors Count" & "Percent Accuracy" field.
3. My goal is to populate a cell with the overall "Average % accuracy for the date range used" keyed in to cell B3 & C3.
4. Since my pivot table will continue to grow as data is entered, I will not post the avregae results underneath as shown in image. I just put there to clearly illustarte the data column I'm trying to calcualte out.

xlshelp-1.jpg

Hi,

I need to design a spreadsheet where users can enter a date range on one sheet and display the average of figures from the source sheet in that range.

I thought that vlookup could be used, but the spreadsheet with the data is not designed to allow this (data recorded horizontally) and cannot be redisigned. I have also tried 'if' function but could not get to it that way either.

your help with this will be greatly appreciated.

...or do i need (if it's even possible) to pass a DATE RANGE value to the "lookup_value" argument of the vector method LOOKUP function?
not sure how to properly describe the problem i'm having here, and i have not found any threads addressing this particular issue. if there is, i apologize.

i'm running office 2003 pro on windows xp sp3.

it began when i made this single-sheet workbook to keep track of my car's gas mileage. pretty straightforward. i record every fill up by entering in the 5 column "fill in section":
date, mileage, trip mileage, gallons taken, and money spent.

3 more columns automatically calculate as follows:
dollars per gallon, miles per gallon, and while i was at it, dollars per mile (i was bored i guess, idk).

at this point, i felt it could do more, so i tried to figure a yearly/monthly/weekly fuel budget for miles driven, gallons used, and money spent, as well as average miles per gallon for the year, month, etc (the weekly figure can just divide the monthly figure by 4).
i've tried the manual approach with absurdly nested IF statements, i've tried LOOKUP using the vector method, but i can't properly pass the month or year of the date column to the lookup_value...

this seems to be my problem/question in general:
do i need (or is it possible) to pass a DATE RANGE value to the "lookup_value" argument?

i can imagine it's WAY more simple and basic than i'm making this out to be. and for that, i apologize. i imagine it would look like i don't have the vaguest clue what i'm doing. it's quite possible i'm simply missing something so simple and so painfully obvious.
any help, hints, or general advice here would be tremendously and thoroughly appreciated.
if there's anything i might have left out, please let me know. many thanks in advance for bearing with me. ~

I have a date range in columns A and B then a number in column C:

05-Jan-09 09-Feb-09 100.00
10-Feb-09 25-Mar-09 200.00
26-Mar-09 11-Apr-09 300.00

Then I have the 'input' section (rows 10 and 11), a further date
range:

Date From Date To
07-Feb-09 13-Feb-09
23-Mar-09 29-Mar-09

What I want to do is calculate the number of days that the date ranges
overlap, and multiply it by the value in column C.

So for example, the date range 7-Feb to 13-Feb overlaps the range 5-
Jan to 9-Feb by three days, and overlaps the range 10-Feb to 25-Mar by
four days. So the answer I'm looking for is 3x100 plus 4x200 = 1100.

There are rows and rows of this stuff, so I'm looking for a formula
that I can just copy down. I've been playing around with array
formulas but I can't get anything to work.

I've used this formula which works but obviously only for one line:

=IF(OR(($B10)$B$1),0,(MIN(($B10),$B$1)-MAX($A10,$A
$1)+1))*C1

I thought I could turn this unto an array function like this:

=IF(OR(($B10)($B$1:$B$3)),0,(MIN (($B10),($B$1:$B$3))-
MAX($A10,($A$1:$A$3))+1))*(C1:C3)

....but it doesn't seem to work

Can anyone help?

I was wondering if an array formula was best for this or hopefully there
is a shorter way?

I have a sheet (Base) which has the information for interrogation and a
summary sheet (Monthly). I wish the monthly sheet to extract data from
the base sheet according to the date range entered. There is two cells,
with a start and end date. The rest of the fields then calculate
depending on that. One of the fields needs to return an average net
commission %. So, depending on the date range entered it needs to find
(filter) within that date range in the Base sheet on Col P (which is
list of dates of exchange), then get the average of Col Y (which is a
fee amount in £) and divide it by the average of Col S (which is sale
price in £).

sorry for being long winded!

any suggestions gratefully received.

--
vgreen
------------------------------------------------------------------------
vgreen's Profile: http://www.excelforum.com/member.php...o&userid=26510
View this thread: http://www.excelforum.com/showthread...hreadid=397768

hello,
i have been searching for quite a while for a solution to what i am trying to do. Here is the low down:

on sheet 2 i have column A that i have name "time_Date". in this column i have a time and date stamp that are 5 minute apart ie A1= 7/11/11 7:20 AM A2= 7/11/11 7:15 AM and so forth desending
on sheet 2 i have column B that i have a reading, let's say air velocity readings that are read at each time stamp. ie B1= 6.0012 B2=6.1396 and so forth
on sheet 1 i have a data validation. i am using a drop down list that is pulling the "time_date" which list all time and dates from sheet 2 column A as noted above. i have this in cell A1 and then another one in cell A2. this is for me to choose a time/date range. so for instance i am choosing, from the in-cell drop down, the time in sheet 2 A1 and then in the next cell the time in sheet 2 A2. so now i have the times 7/11/11 7:20 AM in sheet 1 A1 and then 7/11/11 7:15 AM in sheet 1 A2.

what i am trying to do is in sheet 1, lets say in cell B1, I want to make a formula that puts my time and date range from sheet 1 A1 through A2 to average the data values from sheet 2 between the corsponding time and date range.

please advise. thanks in advnace
-ryan


No luck finding an answer? You could always try Google.