Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to weave in Date Information please?
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)),"") |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to weave in Date Information please?
Rather than use countif and average use two sumproducts where the first
sumproduct is the total and the 2nd sumproduct is the quantity. the two sumproducts are identical except for the term 'Raw Data'!$C$4:$C$5000. I made column A the date which you didn't specify. =sumproduct(--('Raw Data'!$H$4:$H$5000) = "Barb B"),--(date(7,1,2009)<='Raw Data'!$A$4:$A$5000),--(date(9,30,2009)='Raw Data'!$A$4:$A$5000,'Raw Data'!$C$4:$C$5000)/sumproduct(--('Raw Data'!$H$4:$H$5000) = "Barb B"),--(date(7,1,2009)<='Raw Data'!$A$4:$A$5000),--(date(9,30,2009)='Raw Data'!$A$4:$A$5000) "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)),"") |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to weave in Date Information please?
What version of Excel are you using?
-- Biff Microsoft Excel MVP "Danny Boy" wrote in message ... 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)),"") |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to weave in Date Information please?
If you are using 2003 or earlier look at your later post. For 2007 you could
use: =AVERAGEIFS('Raw Data'!$C$4:$C$5000,'Raw Data'!$H$4:$H$5000,="Barb B",'Raw Data'!$J$4:$J$5000,"="&B1,'Raw Data'!$J$4:$J$5000,"<="&C1) This is not an array formula but the dates are still 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)),"") |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How to weave in Date Information please?
I am using Excel 2007. Again, the formula below works, however I was having
difficulty when attempting to incorporate the date parameter information. "Shane Devenshire" wrote: If you are using 2003 or earlier look at your later post. For 2007 you could use: =AVERAGEIFS('Raw Data'!$C$4:$C$5000,'Raw Data'!$H$4:$H$5000,="Barb B",'Raw Data'!$J$4:$J$5000,"="&B1,'Raw Data'!$J$4:$J$5000,"<="&C1) This is not an array formula but the dates are still 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)),"") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I weave in "date" information to my formulas? | Excel Worksheet Functions | |||
Only show information after a certain date | Excel Discussion (Misc queries) | |||
Pulling information from another wksheet by date | Excel Discussion (Misc queries) | |||
Help - Information with time and date | Excel Discussion (Misc queries) | |||
How can I sum information in a list with a date range? | Excel Worksheet Functions |