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