Getting the average from a column using date constrants

  • Thread starter Thread starter Trevor.
  • Start date Start date
T

Trevor.

Using Column A (Date) Formatted Monday, February 1, 2009.
I want to grab the Average of column Z (percentage) where the Month of the
row is February.
In a spreadsheet with 365rows the result set should be the average of 28days.

I have read many posts, tried many examples,
Below is the current formula I am working with
=AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH(10),MainSheet!Z3:Z100)

I am getting a #DIV/0!
I am only testing this on 100 rows as the year isn't complete, as such there
is 365 days of data.

Any help would be greatly appreciated...
 
Using Column A (Date) Formatted
Monday, February 1, 2009.
In a spreadsheet with 365rows the result set
should be the average of 28days.
Click to expand...

So, you want the average for February 2009?
=AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH(10),MainSheet!Z3:Z100)
Click to expand...

Are you using Excel 2007?

Try this (Excel 2007)...

Use cells to hold the date boundaries.

AB1 = 2/1/2009
AC1 = 2/28/2009

=AVERAGEIFS(MainSheet!Z2:Z100,MainSheet!A2:A100,">="&AB1,MainSheet!A2:A100,"<="&AC1)
 
First, did you intend to use MONTH(10) in your formula? That's going to get
you the month of 1/10/1900, or 1. Also, you have a different sized range for
the Z column than for the A column

You can use an SUMPRODUCT()s to get there - this one asks for month 2

=SUMPRODUCT(--(MONTH(A2:A100)=2),Z2:Z100)/SUMPRODUCT(--(MONTH(A2:A100)=2))
 
I guess I wasn't clear...
I have a excel document that contains 13 sheets, 1 Main Data sheet, then a
sheet for each month. In the main sheet I track input data for every day of
the year, that shows among other things what percentage of the days orders
were pickup, walking or delivery. I am trying to create a formula that will
populate a cell in each of the month's sheet, that shows what the average
daily percentage was for delivery. To achieve this I need to grab the
appropriate Monthly data from Column Z (Delivery %). To ensure I only grab
the values that relate to said month I need to constrain the subset of column
Z against column A (which stores a long date) to ensure the appropriate Z
column records are for the month at hand.

While I think this is clear, I also accept that it might just sound like
junk...Any help is greatly appreciated.

Thanks,

Trevor.
 
This still gives me Div/0.

I added the Mainsheet! before the cell names as it is across worksheets.
As for the variance in the row definitions, that was a typo when I rewrote
my current formula, as was the Month value.

Thanks,

T.
 
Ok, then you need to add another condition to the formula that looks for the
specific category that you want averaged. Is there a column range that
specifies the category?

Let's assume column B is the category.

AB1 = 2/1/2009
AC1 = 2/28/2009
AD1 = some category like Delivery

=AVERAGEIFS(MainSheet!Z2:Z100,MainSheet!B2:B100,AD1,MainSheet!A2:A100,">="&AB1,MainSheet!A2:A100,"<="&AC1)
 
Guys,

Any help on this?

Thanks,

T.


Trevor. said:
This still gives me Div/0.

I added the Mainsheet! before the cell names as it is across worksheets.
As for the variance in the row definitions, that was a typo when I rewrote
my current formula, as was the Month value.

Thanks,

T.
Click to expand...
 
Back
Top