Formula with Date Span Condition

[Aimee S.]

How would I write:

If value in Column D = "Base"
And if date value in Column G is greater than or equal to 2/1/10 and less than or equal to 1/31/11

Average all values in Column C

_____________________________

Conversely, how would one write the same with one difference:

If value in Column D = "Base"
And if date value in Column G is greater than or equal to 2/1/10 and less than or equal to 1/31/11

What is the quotient of:

The count of all values that = "Under" in Column B

divided by

The count of any and ALL values in Column B

 

[barry houdini]

For the first......

Which version of Excel are you using?

With the two dates in H1 and H2 respectively you can use AVERAGEIFS function in Excel 2007 and later, i.e.

=AVERAGEIFS(C:C,D:D,"Base",G:G,">="&H1,G:G,"<="&H2)

or in Excel 2003 try this "array formula"

=AVERAGE(IF(D2:D100="Base",IF(G2:G100>=H1,IF(G2:G100<=H2,C2:C100))))

confirmed with CTRL+SHIFT+ENTER

 

[Aimee S.]

GAH! Sorry. Always forget to post that. 2010 Office

 

[barry houdini]

OK, then I think you can use AVERAGEIFS as suggested. For the second I'd use an array formula, i.e.

=AVERAGE(IF(D2:D100="Base",IF(G2:G100>=H1,IF(G2:G100<=H2,IF(B2:B100="Under",1,0)))))

confirmed with CTRL+SHIFT+ENTER

adjust ranges as required but you can't use the whole column.....

 

[Aimee S.]

Genius!!!!

I added IFERROR for good measure.

=IFERROR(AVERAGEIFS(C:C,D:D,"Base",G:G,">="&$AL$2,G:G,"<="&$AW$2),"n/a")
=IFERROR(AVERAGE(IF($D$2:$D$50000="Base",IF($G$2:$G$50000>=$AL$2,IF($G$2:$G$50000<=$AW$2,IF($B$2:$B$50000="Under",1,0))))),"n/a")

One-Thousand-and-Sixty-Two-Point-Three-Repeating Thank Yous!