Averaging data if within a date range

[KellyF]

Hi,

I want to average data on a monthly basis. My worksheet looks like this:

Q
"Offered"
8-Jun-06
14-Jun-06
15-Jun-06
3-Jul-06

R
Time to Fill ("TTF")
45
34
51
29

So I want to calculate the average of the data in column R ("TTF") if column
Q ("Offered") is between 1-Jun-06 and 30-Jun-06, and so on for each month.

Please help! I'm only self-taught on excel and this has got me completely
stuck!

 

[Max]

One way ..

Put in say, S2, array-enter* to confirm the formula:
=AVERAGE(IF(TEXT(Q2:Q100,"mmmyy")="Jun06",R2:R100))
*Press CTRL+SHIFT+ENTER

Adapt the ranges to suit

 

[KellyF]

Hi Max,

Thanks. That works in the same worksheet.

But what if I want to enter into a different worksheet? I just tried using
the range name "Offered" in place of Q2:Q100 and "TTF" in place of R2:R100
and it gives me a NUM error.

Any advice?

Thanks

 

[Ron Rosenfeld]

Hi,

I want to average data on a monthly basis. My worksheet looks like this:

Q
"Offered"
8-Jun-06
14-Jun-06
15-Jun-06
3-Jul-06

R
Time to Fill ("TTF")
45
34
51
29

So I want to calculate the average of the data in column R ("TTF") if column
Q ("Offered") is between 1-Jun-06 and 30-Jun-06, and so on for each month.

Please help! I'm only self-taught on excel and this has got me completely
stuck!

When you write "between" 1-Jun and 30-Jun I am assuming you mean to INCLUDE
those two dates and that you do not really mean "between".

Something like:

=(SUMIF(Q:Q,">="&date(2006,6,1),R:R) -
SUMIF(SUMIF(Q:Q,">"&date(2006,6,30),R:R))
/
(COUNTIF(Q:Q,">="&date(2006,6,1))-COUNTIF(Q:Q,">"&date(2006,6,30)))


--ron

 

[Max]

It should work ok. Check that your defined ranges are equal in size and that
there are no error values within the 2 ranges

 

[KellyF]

I've just tried this:
=AVERAGE(IF(TEXT("Offered","mmmyy")="Jun06","TTF")) in the other worksheet
but it gives 0 as the result (the result is 25).

 

[Max]

I've just tried this:
=AVERAGE(IF(TEXT("Offered","mmmyy")="Jun06","TTF"))
in the other worksheet
but it gives 0 as the result (the result is 25).

In formulas, defined ranges are used "as-is" ie without double quotes

Try it like this, array-entered (CSE) as before:
=AVERAGE(IF(TEXT(Offered,"mmmyy")="Jun06",TTF))