Averaging data if within a date range
 

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!

Averaging data if within a date range
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"KellyF" wrote:
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!

Averaging data if within a date range
 

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

"Max" wrote:

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"KellyF" wrote:
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!

Averaging data if within a date range
 

On Fri, 19 Oct 2007 06:26:00 -0700, KellyF
wrote:

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

Averaging data if within a date range
 

It should work ok. Check that your defined ranges are equal in size and that
there are no error values within the 2 ranges
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"KellyF" wrote:
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?

Averaging data if within a date range
 

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" wrote:

It should work ok. Check that your defined ranges are equal in size and that
there are no error values within the 2 ranges
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"KellyF" wrote:
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?

Averaging data if within a date range
 

"KellyF" wrote:
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 befo
=AVERAGE(IF(TEXT(Offered,"mmmyy")="Jun06",TTF))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---