Thread: Find a date and average corresponding data

Ok, Here's the setup:

See the attachment

I need a formula that will find a particular month in Column A and average the corresponding numbers in column B. And yes, the #N/A will be there too.
I cannot use two cells with start and end dates.

If you can't adjust the original errors then you can look at using the AVERAGEIFS function.

Example

D1: month of interest:
1/1/2010

E1:
=AVERAGEIFS($B:$B,$B:$B,"<>#N/A",$A:$A,">"&$D1-DAY($D1),$A:$A,"<="&EOMONTH($D1,0))

above will account for D1 not nec. being 1st of month.

NOTE:
AVERAGEIFS is not backwards compatible prior to XL2007 ... you would need to use an Array if that's a concern
If you can revise the errors then a Pivot Table will do what you want for little / no effort.

If you need it to be backwards compatable, this should work with your month spelled out in F1

=SUM(IF(ISNUMBER($B$1:$B$53),IF(TEXT($A$1:$A$53,"mmmm")=$F$1,$B$1:$B$53)))

As Donkey said, this needs to be entered as an array, CNTRL SHFT Enter instead of entering. If you do it right, you'll see brackets {} around the formula.

Change SUM to AVERAGE.

(if you need to differentiate text & year then use mmyyyy as test)

If a particular month is all #N/A, and you want to return #N/A, you need to do an error check like so

=IF(ISERROR(AVERAGE(IF(ISNUMBER($B$1:$B$53),IF(TEXT($A$1:$A$53,"mmmm")=$F$1,$B$1:$B$53)))),NA(),AVER AGE(IF(ISNUMBER($B$1:$B$53),IF(TEXT($A$1:$A$53,"mmmm")=$F$1,$B$1:$B$53))))
Does that work for you?

One More Issue,

If all the data in Column B for a particular month were #N/A, I would need the formula to output #N/A, not Div/0. I am charting this data and charts do not like DIV/0

I would suggest you kill a few birds with one stone and use a construct along the lines of:

=LOOKUP(9.99E+307,CHOOSE({1,2},"",AVERAGE(IF(etc...))))
confirmed with CTRL + SHIFT + ENTER

this way where the AVERAGE fails to return a Number an #N/A will result and you need only calculate the Array once.

edit: note above modified from initial post - CHOOSE (and non-numeric first option) nec. for #N/A

See attached.

Did you forget to use CNTRL SHFT ENTER?

Just for sake of clarity should this be reviewed by others latterly - using ChemistB's example of:

=IF(ISERROR(AVERAGE(IF(ISNUMBER($B$1:$B$53),IF(TEXT($A$1:$A$53,"mmmm")=$F$2,$B$1:$B$53)))),NA(),AVERAGE(IF(ISNUMBER($B$1:$B$53),IF(TEXT($A$1:$A$53,"mmmm")=$F$2,$B$1:$B$53))))
confirmed with CTRL + SHIFT + ENTER

the alternative approach I proposed would look like:

=LOOKUP(9.99E+307,CHOOSE({1,2},"",AVERAGE(IF(ISNUMBER($B$1:$B$53),IF(TEXT($A$1:$A$53,"mmmm")=$F$2,$B$1:$B$53)))))
confirmed with CTRL + SHIFT + ENTER

the advantage is that the Array itself is performed only once - though I would concede that on such a small data set this fact is largely irrelevant