Averageif year=2000
 

In column A i have settledate "01/02/2000", thru todays date. In column B, I
have the price "195600", etc.

A B
settledate price
3/1/2001 240000
6/30/2003 50000
11/9/2000 132700
6/30/2000 125000
6/30/2003 49700
5/12/2003 47000
6/12/2000 125000
12/18/2000 73200
4/24/2001 57000
3/2/2004 75500
2/28/2003 47000
3/1/2002 40000

I would like to use =averageif(a2:a900, year="2000",b2:b900)...however, it
is not working. Would someone be kind enought to put me on the right path.

Your help will be greatly appricated.

Averageif year=2000
 

This will work
=SUMPRODUCT(--(YEAR(A2:A900)=2000),B2:B900)/SUMPRODUCT(--(YEAR(A2:A900)=2000))

For more details on SUMPRODUCT
Bob Phillips
http://www.xldynamic.com/source/xld.SUMPRODUCT.html
J.E McGimpsey
http://mcgimpsey.com/excel/formulae/doubleneg.html
Debra Dalgleish
http://www.contextures.com/xlFunctio...tml#SumProduct

best wishes
--
Bernard Liengme
http://people.stfx.ca/bliengme
Microsoft Excel MVP

"MitzDriver" wrote in message
...
In column A i have settledate "01/02/2000", thru todays date. In column B,
I
have the price "195600", etc.

A B
settledate price
3/1/2001 240000
6/30/2003 50000
11/9/2000 132700
6/30/2000 125000
6/30/2003 49700
5/12/2003 47000
6/12/2000 125000
12/18/2000 73200
4/24/2001 57000
3/2/2004 75500
2/28/2003 47000
3/1/2002 40000

I would like to use =averageif(a2:a900, year="2000",b2:b900)...however, it
is not working. Would someone be kind enought to put me on the right path.

Your help will be greatly appricated.

Averageif year=2000
 

Hi,

Try this ARRAY formula. See below

=AVERAGE(IF(YEAR(A1:A100)=2000,B1:B100))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
Mike

"MitzDriver" wrote:

In column A i have settledate "01/02/2000", thru todays date. In column B, I
have the price "195600", etc.

A B
settledate price
3/1/2001 240000
6/30/2003 50000
11/9/2000 132700
6/30/2000 125000
6/30/2003 49700
5/12/2003 47000
6/12/2000 125000
12/18/2000 73200
4/24/2001 57000
3/2/2004 75500
2/28/2003 47000
3/1/2002 40000

I would like to use =averageif(a2:a900, year="2000",b2:b900)...however, it
is not working. Would someone be kind enought to put me on the right path.

Your help will be greatly appricated.

Averageif year=2000
 

Worked great....thanks.
However, i'm trying to pull info from a table. I thought I could just filter
the table and get my info. That does not work.

So, along with settledate and price there is a column "subdivision". How can
i add two criteria to your formula. i.e. if year settledate = 2000 and
subdivision = "Hunters Ridge".

Thanks again for your help!!

"Mike H" wrote:

Hi,

Try this ARRAY formula. See below

=AVERAGE(IF(YEAR(A1:A100)=2000,B1:B100))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
Mike

"MitzDriver" wrote:

In column A i have settledate "01/02/2000", thru todays date. In column B, I
have the price "195600", etc.

A B
settledate price
3/1/2001 240000
6/30/2003 50000
11/9/2000 132700
6/30/2000 125000
6/30/2003 49700
5/12/2003 47000
6/12/2000 125000
12/18/2000 73200
4/24/2001 57000
3/2/2004 75500
2/28/2003 47000
3/1/2002 40000

I would like to use =averageif(a2:a900, year="2000",b2:b900)...however, it
is not working. Would someone be kind enought to put me on the right path.

Your help will be greatly appricated.

Averageif year=2000
 

=AVERAGE(IF((YEAR(A1:A100)=2000)*(C1:C100="Hunters Ridge"),B1:B100))

MitzDriver wrote:
Worked great....thanks.
However, i'm trying to pull info from a table. I thought I could just filter
the table and get my info. That does not work.

So, along with settledate and price there is a column "subdivision". How can
i add two criteria to your formula. i.e. if year settledate = 2000 and
subdivision = "Hunters Ridge".

Thanks again for your help!!

"Mike H" wrote:

Hi,

Try this ARRAY formula. See below

=AVERAGE(IF(YEAR(A1:A100)=2000,B1:B100))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.
Mike

"MitzDriver" wrote:

In column A i have settledate "01/02/2000", thru todays date. In column B, I
have the price "195600", etc.

A B
settledate price
3/1/2001 240000
6/30/2003 50000
11/9/2000 132700
6/30/2000 125000
6/30/2003 49700
5/12/2003 47000
6/12/2000 125000
12/18/2000 73200
4/24/2001 57000
3/2/2004 75500
2/28/2003 47000
3/1/2002 40000

I would like to use =averageif(a2:a900, year="2000",b2:b900)...however, it
is not working. Would someone be kind enought to put me on the right path.

Your help will be greatly appricated.

Averageif year=2000
 

XL-2007 & above

=AVERAGEIFS(B2:B13,A2:A13,"=1/1/2000",A2:A13,"<=12/31/2000",C2:C13,"Hunter
Ridge")


"MitzDriver" wrote:

In column A i have settledate "01/02/2000", thru todays date. In column B, I
have the price "195600", etc.

A B
settledate price
3/1/2001 240000
6/30/2003 50000
11/9/2000 132700
6/30/2000 125000
6/30/2003 49700
5/12/2003 47000
6/12/2000 125000
12/18/2000 73200
4/24/2001 57000
3/2/2004 75500
2/28/2003 47000
3/1/2002 40000

I would like to use =averageif(a2:a900, year="2000",b2:b900)...however, it
is not working. Would someone be kind enought to put me on the right path.

Your help will be greatly appricated.