Compile average based on date range 2

[eggohead]

Hello,

Many Kudos to the folks helping in this forum. I have one more issue revolving around dates. I need to get the average of a column based on a date range.

I used the followiong formula to retrieve a row count of dates that meet date criteria in B2 and B3.

=SUMPRODUCT(--(DATES!L2:L148>=B2)*((DATES!L2:L148<=B3)))

I now need a similar formula that will average data in the next column M (conains number values) for the same date criteria in B2 and B3.

Any suggestions?

Thanks,

 

[SkipVought]

Hi,

Use the COUNT function to count numeric entries. Use COUNTA to count ANY entry.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[eggohead]

Skip,

I dont need to count. I need to average the values that fall between B2 and B3. Or I guess I could sum those values then divide by the fomula I listed.

 

[SkipVought]

{quote]I dont need to count.[/quote]

Maybe I misunderstood your question.

How will your average be calculated? Please post an example based on data from these two columns.

Please be clear, concise and complete.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

You don't just want to count the number of 'hits', you want to sum them. That can easily be achieved by adding a group to your SumProduct:

[tab]=SUMPRODUCT(--(Dates!L2:L148>=B2) * (Dates!L2:L148<=B3) * (Dates!L2:L148))
(I also removed extra parentheses around your second part of the formula)

Putting that together with the formula you were already using:

=SUMPRODUCT(--(Dates!L2:L148>=B2) * (Dates!L2:L148<=B3) * (Dates!L2:L148)) / SUMPRODUCT(--(DATES!L2:L148>=B2)*(DATES!L2:L148<=B3))

Because that will most likely return a value with decimals you might want to wrap that formula in another. Either INT(...), ROUND(...) or maybe DATEVALE(...) depending on what date you want returned.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[eggohead]

OK

I imagine the formula will be similar to the following one you provided but use another function to average or sum. All of the variables are the same the only difference is its now column M and the function is different.

=SUMPRODUCT(--(DATES!L2:L148>=B2)*((DATES!L2:L148<=B3)))

Column M contains data that needs to be averaged between the date values in B2 and B3( greather than B2 but less than B3):

6.57
6.33
0
0
0
93.97
9.57
8.62
15.92
0
9.13
etc.

Hope this makes sense.

 

[AnotherHiggins]

Ah, then change my last formula to:

[tab]=SUMPRODUCT(--(Dates!L2:L148>=B2) * (Dates!L2:L148<=B3) * (Dates!M2:M148)) / SUMPRODUCT(--(DATES!L2:L148>=B2)*(DATES!L2:L148<=B3))

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

so maybe its something like...
[tt]
=SUMPRODUCT(--(Dates!L2:L148>=B2) * (Dates!L2:L148<=B3) * (Dates!M2:M148))/WHAT
[/tt]
the WHAT could be the SUM of the values in column M

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}