Finding average price based on date

[Nyanko]

I hope this makes sense ...

I have a list of dates and prices sorted in catagories. I'm looking to find a way to produce the Average Price Per Month.

Example :

A B C
Type1 01/06/05 £10
Type1 01/05/05 £15
Type2 01/06/05 -£5
Type3 01/05/05 £20


I've tried the following : {=AVERAGE(IF(cellrangea="TYPE1",IF(cellrangeb="MONTH",cellrangec,0),0))}

Problem is that this only kinda works if I insert a column and type the month in manually, which is kind of a drag !!!

I would like the result to be :

May
Type1 £15
Type2
Type3 £20

June
Type1 £10
Type2 -£5
Type3

Any ideas ?

[Domenic]

How about something like this...

1) Enter the dates in the first row, starting at F1, in the following format:

May 2005
June 2005
etc.

2) Enter the'Types' in Column E, starting at E2

Type1
Type2
etc.

3) Enter the following formula F2, copy across and down:

=AVERAGE(IF((DATE(YEAR($B$1:$B$4),MONTH($B$1:$B$4),1)=F$1)*($A$1:$A$4=$E2),$C$1:$C$4))

...confirmed with CONTROL+SHIFT+ENTER.

For circumstances where no data exists for a 'Type' and 'Month', you'll receive a #DIV/0! error value. For this, you can use conditional formatting to hide them...

1) Select your data, in this example F2:G4

2) Format > Conditional Formatting > Formula Is

3) Enter the following formula:

=ISERR(F2)

4) Choose 'White' as your font

5) Click Ok

Hope this helps!