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

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

- Getting an average using lookup function?
- Sumifs Using Date Ranges
- Average Within Date Range
- COUNTIF command using date ranges
- How to sum data from another sheet using date ranges?
- How to use Date range function in Sum(if....)
- How to use Date range function in Sum(if....)
- Calculating daily averages
- Input range only and calculate Average of that range in another cell
- Averaging between date range
- Excel 2003 - sum data using date range
- Date Range Year Change
- Multiple parameters w/If,Then Function using Date Range
- Using date ranges in a pivot table
- Countif using date range
- Date ranges between date ranges
- Summary Sheet using date range
- Calculating average excluding null and n/a values
- Caculate the average from a pivot table column that fall within a selected date range
- Look up date range and calculate average
- How to calculate averages and totals based on yearly and monthly ranges?
- Number of days in overlapping date ranges (using array formula?)
- Date Range and calculation
- Average column B range based on uniquie ID of time and Date from Column A upper/lower

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

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

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?

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

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

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!

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

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

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

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

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

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

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.

Thanks

Andy

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

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.

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. ~

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?

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

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