Getting the average from a column using date constrants
 

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

Getting the average from a column using date constrants
 

Using Column A (Date) Formatted
Monday, February 1, 2009.
In a spreadsheet with 365rows the result set
should be the average of 28days.

So, you want the average for February 2009?

=AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH (10),MainSheet!Z3:Z100)

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)

--
Biff
Microsoft Excel MVP


"Trevor." wrote in message
...
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...

Getting the average from a column using date constrants
 

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


"Trevor." wrote:

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

Getting the average from a column using date constrants
 

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.


"T. Valko" wrote:

Using Column A (Date) Formatted
Monday, February 1, 2009.
In a spreadsheet with 365rows the result set
should be the average of 28days.

So, you want the average for February 2009?

=AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH (10),MainSheet!Z3:Z100)

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)

--
Biff
Microsoft Excel MVP

Getting the average from a column using date constrants
 

Ok, what version of Excel are you using?

--
Biff
Microsoft Excel MVP


"Trevor." wrote in message
...
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.


"T. Valko" wrote:

Using Column A (Date) Formatted
Monday, February 1, 2009.
In a spreadsheet with 365rows the result set
should be the average of 28days.

So, you want the average for February 2009?

=AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH (10),MainSheet!Z3:Z100)

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)

--
Biff
Microsoft Excel MVP

Getting the average from a column using date constrants
 

NUTS!!!!

Excel 2007.

T.

"T. Valko" wrote:

Ok, what version of Excel are you using?

--
Biff
Microsoft Excel MVP


"Trevor." wrote in message
...
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.


"T. Valko" wrote:

Using Column A (Date) Formatted
Monday, February 1, 2009.
In a spreadsheet with 365rows the result set
should be the average of 28days.

So, you want the average for February 2009?

=AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH (10),MainSheet!Z3:Z100)

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)

--
Biff
Microsoft Excel MVP





.

Getting the average from a column using date constrants
 

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.


"Duke Carey" wrote:

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


"Trevor." wrote:

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

Getting the average from a column using date constrants
 

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,AD 1,MainSheet!A2:A100,"="&AB1,MainSheet!A2:A100,"<= "&AC1)

--
Biff
Microsoft Excel MVP


"Trevor." wrote in message
...
NUTS!!!!

Excel 2007.

T.

"T. Valko" wrote:

Ok, what version of Excel are you using?

--
Biff
Microsoft Excel MVP


"Trevor." wrote in message
...
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.


"T. Valko" wrote:

Using Column A (Date) Formatted
Monday, February 1, 2009.
In a spreadsheet with 365rows the result set
should be the average of 28days.

So, you want the average for February 2009?

=AVERAGEIF(MainSheet!A2:A100,MONTH(A2:A100)=MONTH (10),MainSheet!Z3:Z100)

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)

--
Biff
Microsoft Excel MVP





.

Getting the average from a column using date constrants
 

Guys,

Any help on this?

Thanks,

T.


"Trevor." wrote:

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.


"Duke Carey" wrote:

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


"Trevor." wrote:

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