How do I weave in "date" information to my formulas?
 

The Formula below works, however I'm having difficulty integrating "date"
parameters into them.

In Column A of the Raw Data spreadsheet I have "Discharge Dates". I want the
two formulas below to work on all data inpuut between July 1, 2009 and up
until Sept 30, 2009. Any data input before or after the date parameters
identified would not register in the calculations. Could someone assist me in
incorporating the appropriate "date" parameters into the formulas below.
Thank you! Dan

=IF(COUNTIF('Raw Data'!$H$4:$H$5000,"Barb B"),AVERAGE(IF('Raw
Data'!$H$4:$H$5000="Barb B",'Raw Data'!$C$4:$C$5000)),"")


=IF(COUNTIF('Raw Data'!$F$4:$F$5000,"OP"),AVERAGE(IF('Raw
Data'!$F$4:$F$5000="OP",'Raw Data'!$C$4:$C$5000)),"")

How do I weave in "date" information to my formulas?
 

Hi,

try something like this array formula:

=AVERAGE(IF(('Raw Data'!$H$4:$H$5000="Barb B")*('Raw
Data'!$J$4:$J$5000=B1)*('Raw Data'!$J$4:$J$5000<=C1),'Raw
Data'!$C$4:$C$5000,""))

array - press Shift+Ctrl+Enter

In this case the two dates are entered in B1 and C1.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Danny Boy" wrote:

The Formula below works, however I'm having difficulty integrating "date"
parameters into them.

In Column A of the Raw Data spreadsheet I have "Discharge Dates". I want the
two formulas below to work on all data inpuut between July 1, 2009 and up
until Sept 30, 2009. Any data input before or after the date parameters
identified would not register in the calculations. Could someone assist me in
incorporating the appropriate "date" parameters into the formulas below.
Thank you! Dan

=IF(COUNTIF('Raw Data'!$H$4:$H$5000,"Barb B"),AVERAGE(IF('Raw
Data'!$H$4:$H$5000="Barb B",'Raw Data'!$C$4:$C$5000)),"")


=IF(COUNTIF('Raw Data'!$F$4:$F$5000,"OP"),AVERAGE(IF('Raw
Data'!$F$4:$F$5000="OP",'Raw Data'!$C$4:$C$5000)),"")

How do I weave in "date" information to my formulas?
 

Just for clarification, here is an example of where I was able to incorporate
"date parameters" and the formula worked just as I wanted it to. And yes I am
using Cell A4 to reflect the date, and I am using Excel 2007. The formula
below works perfectly:

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*(('Raw Data'!$H$4:$H$5000="Barb
B")*(('Raw Data'!$D$4:$D$5000="LATE")))))

However, when I tried to do the exact same thing in my other two formulas
(see example below), I kept getting error messages:

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*('Raw Data'!$H$4:$H$5000,"Barb
B"),AVERAGE(IF('Raw Data'!$H$4:$H$5000="Barb B",'Raw Data'!$C$4:$C$5000)),"")

=SUMPRODUCT(('Raw Data'!$A$4:$A$5000=DATE(2009,7,1))*('Raw
Data'!$A$4:$A$5000<=DATE(2009,9,30)*('Raw
Data'!$F$4:$F$5000,"OP"),AVERAGE(IF('Raw Data'!$F$4:$F$5000="OP",'Raw
Data'!$C$4:$C$5000)),"")

Again thank you for any help.....So far the suggestions don't seem to be
generating outcomes without errors.

Dan

"Shane Devenshire" wrote:

Hi,

try something like this array formula:

=AVERAGE(IF(('Raw Data'!$H$4:$H$5000="Barb B")*('Raw
Data'!$J$4:$J$5000=B1)*('Raw Data'!$J$4:$J$5000<=C1),'Raw
Data'!$C$4:$C$5000,""))

array - press Shift+Ctrl+Enter

In this case the two dates are entered in B1 and C1.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"Danny Boy" wrote:

The Formula below works, however I'm having difficulty integrating "date"
parameters into them.

In Column A of the Raw Data spreadsheet I have "Discharge Dates". I want the
two formulas below to work on all data inpuut between July 1, 2009 and up
until Sept 30, 2009. Any data input before or after the date parameters
identified would not register in the calculations. Could someone assist me in
incorporating the appropriate "date" parameters into the formulas below.
Thank you! Dan

=IF(COUNTIF('Raw Data'!$H$4:$H$5000,"Barb B"),AVERAGE(IF('Raw
Data'!$H$4:$H$5000="Barb B",'Raw Data'!$C$4:$C$5000)),"")


=IF(COUNTIF('Raw Data'!$F$4:$F$5000,"OP"),AVERAGE(IF('Raw
Data'!$F$4:$F$5000="OP",'Raw Data'!$C$4:$C$5000)),"")