Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AVERAGEIF | Excel Worksheet Functions | |||
Help with Averageif Formula (don't think I should use Averageif) | Excel Discussion (Misc queries) | |||
AVERAGEIF | Excel Worksheet Functions | |||
AverageIf | Excel Discussion (Misc queries) | |||
AVERAGEIF equivalent in excel 2000 | Excel Discussion (Misc queries) |