function tweak?

[Carlee]

Hi there,

I use the following SumProduct formula to search for all values in a
specified date range, in this case, for all values in january, then sum those
values which fall into that date range:

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400>=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")),'Daily Reading Master Log'!CB3:CB400)

Question: I need to be able to adapt this formula for averaging a set of
values for a specified month range.

can anyone help me on this?

 

[Vergel Adriano]

maybe like this:

I modified your original formula like this to get just the count:

SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400>=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01"))


And then, divided the sum with the count:

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400>=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")),'Daily Reading Master
Log'!CB3:CB400)/SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400>=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01"))

 

[Barb Reinhardt]

One way:

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400>=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")),'Daily Reading Master Log'!CB3:CB400)/
SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400>=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")))

 

[Vergel Adriano]

The formulas were missing one closing parenthesis. The formula to get the
count should be:

SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400>=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")))


The formula to get the average should be:

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400>=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")),'Daily Reading Master
Log'!CB3:CB400)/SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400>=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")))

 

[Ron Rosenfeld]

Hi there,

I use the following SumProduct formula to search for all values in a
specified date range, in this case, for all values in january, then sum those
values which fall into that date range:

=SUMPRODUCT(--('Daily Reading Master
Log'!B3:B400>=DATEVALUE("01/01")),--('Daily Reading Master
Log'!B3:B400<=DATEVALUE("30/01")),'Daily Reading Master Log'!CB3:CB400)

Question: I need to be able to adapt this formula for averaging a set of
values for a specified month range.

can anyone help me on this?

How about something like:


=(SUMIF(DtRng,">="&DATE(2007,1,1),ValRng)-
SUMIF(DtRng,">"&DATE(2007,2,0),ValRng))/
(COUNTIF(DtRng,">="&DATE(2007,1,1))-
COUNTIF(DtRng,">"&DATE(2007,2,0)))

where

DtRng = Log'!B3:B400
ValRng = 'Daily Reading Master Log'!CB3:CB400)


In the above formula, I used the 0th day of the month following instead of the
last day of the current month, as it's a bit simpler to compute -- you don't
have to know how many days are in the current month.


--ron

 

[Carlee]

This worked brilliantly. Many thanks to everyone...you all rock!