function tweak?
On Mon, 16 Apr 2007 17:02:01 -0700, Carlee
wrote:
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
|