Average If year in excel 2007

Hi,

I'm trying to get the average if the year = 2009 or 2010 etc ...

I tried to use this this formula but it gives me 0 :

``=AVERAGE(IF(YEAR(Table3[Date])=2009,Table3[Pricing]))``

Thank you so much for your time.
barry houdini

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OblivionSY

I would move the IF statement to the outter clause.

=IF(YEAR(Table3[Date])=2009, AVERAGE(Table3[Pricing]), "Whatever to do if not 2009")

Have not tested that code - but if you supply an excel doc. would be able to have a look in detail.

I am not sure about your YEAR(Table3[Date]) part... would need to see the excel document to commment further
I think that the problem with your approach is the cells that are not from 2009 are still included in the AVG calculation.

You may have better luck using a combination of SUMIF and COUNTIF functions instead of AVERAGE.

You'll need to replace date-column-range and pricing-column-range with the proper range expression.

``=( SUMIF(date-column-range, 2009, pricing-column-range) / COUNTIF(date-column-range, 2009) )``
SOLUTION

membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
...on second thoughts....perhaps you are correct sjklein42, apologies. If there is just a year in the date column then that would be consistent with the formula producing a zero result. If that is the case then you can use sjklein's SUMIF/COUNTIF suggestion or AVERAGEIF like

=AVERAGEIF(Table3[Date],2009,Table3[Pricing])

regards, barry