AVERAGE with flexible date ranges

**Hoops61176** · 09-30-2010, 03:59 PM

Column A contains DATES
Column B contains a number
Example:
01/02/1997 308

Start Date (cell C1)
End Date (cell C2)
Average (cell C3)

Based on this information, how do I write a formula that will give me the AVERAGE in C3 based on the date ranges I enter into C1 and C2?

**DonkeyOte** · 09-30-2010, 04:41 PM

Assuming XL2003 (ie pre XL2007 and thus pre-AVERAGEIFS) - you can use:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

Both of the above assume that where a date value exists in A a numeric value will exist in B.

**mgaworecki** · 10-01-2010, 02:11 PM

To Do it without using an array:

Please Login or Register  to view this content.

**DonkeyOte** · 10-01-2010, 04:27 PM

mgaworecki - a SUMPRODUCT is really no more efficient than an Array - they are processed in the same way and are subject to similar (though not identical) constraints.

Moreover in this instance I would say the benefit of "standard" entry is offset by the fact you're using two SUMPRODUCTs as opposed to a single array as per the AVERAGE.

If you're using SUMPRODUCT in preference to AVERAGEIFS then the implication is that you're assuming a version pre XL2007 in which case entire column references are not permitted.

If AVERAGEIFS is not viable my first preference would the first option outlined in post # 2 as it is the most efficient - esp. if there is a need to work with large data sets.

**mgaworecki** · 10-05-2010, 01:57 PM

Thanks DO,

I was not aware of that. I appreciate the lesson.

My problem with excel is that although I can usually find a way to do what I want, it may not be the best way. I am always looking for input.