Conditional Columns

[bslewis]

I am looking for some help. I have 2 columns: 1 with a date, and 1 with a score. I am looking to show monthly averages automatically. Here is an example:
Date Score Month Average
20-Jan-12 91.6 January
12-Jul-12 99.6 February
23-Feb-12 88.8 March
23-Feb-12 88.0 April
23-Feb-12 92 May
24-Feb-12 87.0 June
17-May-12 98.0 July
18-May-12 92.0 August
19-May-12 90.0 September
20-May-12 93.0 October
27-Jan-12 99.2 November
28-Jan-12 85.0 December
29-Jan-12 77.0
30-Jan-12 65.0
1-Feb-12 98
2-Feb-12 97.0
4-Jan-12 98.8
12-Dec-12 100.0

Any suggestions?
Thanks

[ChemistB]

You could use Pivot tables or with January in D2, in E2

=SUMPRODUCT(--(TEXT($A$2:$A$19,"mmmm")=D2),$B$2:$B$19) dragged down
Does that work for you?

[BenMiller]

Have you looked into the AVERAGEIF function?

[bslewis]

AVERAGEIF always gives me an error!

---------- Post added at 02:29 PM ---------- Previous post was at 02:21 PM ----------

That formula is showing a "zero"[COLOR="Silver"]

[BenMiller]

You could use Pivot tables or with January in D2, in E2

=SUMPRODUCT(--(TEXT($A$2:$A$19,"mmmm")=D2),$B$2:$B$19) dragged down
Does that work for you?

That would give you the sum. To average it, try

=SUMPRODUCT(--(TEXT($A$2:$A$19,"mmmm")=D2),$B$2:$B$19)/SUMPRODUCT(--(TEXT($A$2:$A$19,"mmmm")=D2))

[bslewis]

That would give you the sum. To average it, try

=SUMPRODUCT(--(TEXT($A$2:$A$19,"mmmm")=D2),$B$2:$B$19)/SUMPRODUCT(--(TEXT($A$2:$A$19,"mmmm")=D2))

When I tried this, I got #DIV/O!

[BenMiller]

What do you have in D2?

[bslewis]

What do you have in D2?

"February" its a text cell

[bslewis]

Monthly Average.xlsxHere is an example:

[BenMiller]

Can't open that, sorry I have 2003

[ChemistB]

Ahh, it's because you don't have February, you have Feb. This modification should work

=IFERROR(SUMPRODUCT(--(TEXT($A$2:$A$19,"mmm")=D2),$B$2:$B$19)/SUMPRODUCT(--(TEXT($A$2:$A$19,"mmm")=D2)),"")

changed from "mmmm" which expects the entire month to be written out to "mmm" which expect the 3 character abbreviation. Also I added the IFERROR part to keep out the errors when you don't have a month listed at all.

[BenMiller]

Thanks ChemistB! You are the bomb

[bslewis]

Ahh, it's because you don't have February, you have Feb. This modification should work

=IFERROR(SUMPRODUCT(--(TEXT($A$2:$A$19,"mmm")=D2),$B$2:$B$19)/SUMPRODUCT(--(TEXT($A$2:$A$19,"mmm")=D2)),"")

changed from "mmmm" which expects the entire month to be written out to "mmm" which expect the 3 character abbreviation. Also I added the IFERROR part to keep out the errors when you don't have a month listed at all.

That worked, however, this was a sample and the rather large document I am working on has some blank cells (not yet entered). The formula is calculating these blank cells as "zeros" and lowering the actual average. I am in over my head!

[ChemistB]

This looks in column B and only counts it if it's > 0

=IFERROR(SUMPRODUCT(--(TEXT($A$2:$A$19,"mmm")=D2),--($B$2:$B$19>0),$B$2:$B$19)/SUMPRODUCT(--(TEXT($A$2:$A$19,"mmm")=D2),--($B$2:$B$19>0)),"")

[BenMiller]

Blank cells ... meaning they have a date in column A but no number in column B?

[Cutter]

How about

Please Login or Register  to view this content.