Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF AND SUMIF AND COUNTIF
I have a column with open date (B), column with closed date (C), column with
count of days opened (D). I need to calculate the average number of days each were opened in the 1st, 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal year. I have been physicallly typing in each cell reference that has an opening date int he first quarter, but would like to be able to say SUM COLUMN D ONLY IF COLUMN B "=10/01/2004" also "<=12/31/2004". THEN COUNTIF COLUMN B ONLY IF "=10/01/2004" ALSO "<=12/31/2004" |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF AND SUMIF AND COUNTIF
adapt this array formula to your criteria. Don't forget to use
ctrl+shift+enter for entering/editing. =AVERAGE(IF(ChecksA=O1,ChecksA<O2,ChecksD)) =AVERAGE(IF(a2:a200=O1,a2:a200<O2,d2:d200)) Don Guillett SalesAid Software "kathi" wrote in message ... I have a column with open date (B), column with closed date (C), column with count of days opened (D). I need to calculate the average number of days each were opened in the 1st, 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal year. I have been physicallly typing in each cell reference that has an opening date int he first quarter, but would like to be able to say SUM COLUMN D ONLY IF COLUMN B "=10/01/2004" also "<=12/31/2004". THEN COUNTIF COLUMN B ONLY IF "=10/01/2004" ALSO "<=12/31/2004" |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF AND SUMIF AND COUNTIF
Kathi,
I am not sure what to do when the start date is in one quarter and the end date is in another, but assuming you count from the start date quarter, try this =AVERAGE(IF(INT((MONTH(B2:B20)+2)/3)=4,C2:C20-B2:B20)) which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kathi" wrote in message ... I have a column with open date (B), column with closed date (C), column with count of days opened (D). I need to calculate the average number of days each were opened in the 1st, 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal year. I have been physicallly typing in each cell reference that has an opening date int he first quarter, but would like to be able to say SUM COLUMN D ONLY IF COLUMN B "=10/01/2004" also "<=12/31/2004". THEN COUNTIF COLUMN B ONLY IF "=10/01/2004" ALSO "<=12/31/2004" |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF AND SUMIF AND COUNTIF
Thank you so much for the response. I think this is the type of formula I
was looking for but if you could just clarify in my mind, please. I understand =AVERAGE(IF but am not clear on why finding the MONTH then rounding down (INT than adding +2) and dividing by /3) and this part really confuses me =4, I understand that finding the difference between relates to the average number of days open but am not clear how ??? If were more clear then I could definitely adapte this for y use. So for my use I understand =AVERAGE(C2:C20-B2:B20) BUT ONLY IF B2:B20=10/01/2003 AND ALSO IF B2:B20<=12/31/2003 so if there is a way to define the 1st or 2nd etc. quarter other than this I could really use that. THANKS AGAIN FOR YOUR TIME "Bob Phillips" wrote: Kathi, I am not sure what to do when the start date is in one quarter and the end date is in another, but assuming you count from the start date quarter, try this =AVERAGE(IF(INT((MONTH(B2:B20)+2)/3)=4,C2:C20-B2:B20)) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF AND SUMIF AND COUNTIF
Thank you for your time. I am not sure I completely understand...the D2:D200
reference is what is being averaged and the C cell references are the criteria, correct? I adapted it to this ........... {=AVERAGE(IF('Sheet 1'!$B$1:$B$500="10/01/2004",'Sheet 1'!$B$1:$B$500<="12/31/2004",'Sheet 1'!$D$1:$D$500))} but am getting the same result regardless of the dates I put in. Can you help with that? I have also tried =AVERAGE(IF('Sheet 1'!$B$1:$B$500="10/01/2004",'Sheet 1'!$B$1:$B$500<="12/31/2004"),'Sheet 1'!$D$1:$D$500) but it also gives me the same result no matter what dates are entered. And thanks for the reminder about the "control+ shift+enter" "Don Guillett" wrote: adapt this array formula to your criteria. Don't forget to use ctrl+shift+enter for entering/editing. =AVERAGE(IF(ChecksA=O1,ChecksA<O2,ChecksD)) =AVERAGE(IF(a2:a200=O1,a2:a200<O2,d2:d200)) Don Guillett SalesAid Software "kathi" wrote in message ... I have a column with open date (B), column with closed date (C), column with count of days opened (D). I need to calculate the average number of days each were opened in the 1st, 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal year. I have been physicallly typing in each cell reference that has an opening date int he first quarter, but would like to be able to say SUM COLUMN D ONLY IF COLUMN B "=10/01/2004" also "<=12/31/2004". THEN COUNTIF COLUMN B ONLY IF "=10/01/2004" ALSO "<=12/31/2004" |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF AND SUMIF AND COUNTIF
Also, the checksA is completely foreign to me if you could explain. Thanks
again. "Don Guillett" wrote: adapt this array formula to your criteria. Don't forget to use ctrl+shift+enter for entering/editing. =AVERAGE(IF(ChecksA=O1,ChecksA<O2,ChecksD)) =AVERAGE(IF(a2:a200=O1,a2:a200<O2,d2:d200)) Don Guillett SalesAid Software "kathi" wrote in message ... I have a column with open date (B), column with closed date (C), column with count of days opened (D). I need to calculate the average number of days each were opened in the 1st, 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal year. I have been physicallly typing in each cell reference that has an opening date int he first quarter, but would like to be able to say SUM COLUMN D ONLY IF COLUMN B "=10/01/2004" also "<=12/31/2004". THEN COUNTIF COLUMN B ONLY IF "=10/01/2004" ALSO "<=12/31/2004" |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF AND SUMIF AND COUNTIF
Kathi,
You mentioned that you wanted to average by quarter, so the INT((MONTH(B2:B20)+2)/3) works out the quarter for each date in B2:B20. The =4 is just an example testing for quarter 4, Oct, Nov, Dec. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kathi" wrote in message ... Thank you so much for the response. I think this is the type of formula I was looking for but if you could just clarify in my mind, please. I understand =AVERAGE(IF but am not clear on why finding the MONTH then rounding down (INT than adding +2) and dividing by /3) and this part really confuses me =4, I understand that finding the difference between relates to the average number of days open but am not clear how ??? If were more clear then I could definitely adapte this for y use. So for my use I understand =AVERAGE(C2:C20-B2:B20) BUT ONLY IF B2:B20=10/01/2003 AND ALSO IF B2:B20<=12/31/2003 so if there is a way to define the 1st or 2nd etc. quarter other than this I could really use that. THANKS AGAIN FOR YOUR TIME "Bob Phillips" wrote: Kathi, I am not sure what to do when the start date is in one quarter and the end date is in another, but assuming you count from the start date quarter, try this =AVERAGE(IF(INT((MONTH(B2:B20)+2)/3)=4,C2:C20-B2:B20)) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF AND SUMIF AND COUNTIF
Forgive me if I'm being dense. I'm trying to comprehend. Okay, so when I
want the average of the days open for a single fiscal year? How do I get the answer for 1st quarter Fiscal Year 2004, 2nd Quarter FY04, 3rd QTR FY04, 4th QTR FY04, 1st QTR FY05, 2nd QTR FY05, 3rd QTR FY05, 4th QTR FY05, 1st QTR FY06, 2nd QTR FY06, etc.......... "Bob Phillips" wrote: Kathi, You mentioned that you wanted to average by quarter, so the INT((MONTH(B2:B20)+2)/3) works out the quarter for each date in B2:B20. The =4 is just an example testing for quarter 4, Oct, Nov, Dec. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kathi" wrote in message ... Thank you so much for the response. I think this is the type of formula I was looking for but if you could just clarify in my mind, please. I understand =AVERAGE(IF but am not clear on why finding the MONTH then rounding down (INT than adding +2) and dividing by /3) and this part really confuses me =4, I understand that finding the difference between relates to the average number of days open but am not clear how ??? If were more clear then I could definitely adapte this for y use. So for my use I understand =AVERAGE(C2:C20-B2:B20) BUT ONLY IF B2:B20=10/01/2003 AND ALSO IF B2:B20<=12/31/2003 so if there is a way to define the 1st or 2nd etc. quarter other than this I could really use that. THANKS AGAIN FOR YOUR TIME "Bob Phillips" wrote: Kathi, I am not sure what to do when the start date is in one quarter and the end date is in another, but assuming you count from the start date quarter, try this =AVERAGE(IF(INT((MONTH(B2:B20)+2)/3)=4,C2:C20-B2:B20)) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF AND SUMIF AND COUNTIF
If you want year as well
=AVERAGE(IF((INT((MONTH(B2:B20)+2)/3)=4)*(YEAR(B2:B20=2004)),C2:C20-B2:B20)) for 4th QTR FY04 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kathi" wrote in message ... Forgive me if I'm being dense. I'm trying to comprehend. Okay, so when I want the average of the days open for a single fiscal year? How do I get the answer for 1st quarter Fiscal Year 2004, 2nd Quarter FY04, 3rd QTR FY04, 4th QTR FY04, 1st QTR FY05, 2nd QTR FY05, 3rd QTR FY05, 4th QTR FY05, 1st QTR FY06, 2nd QTR FY06, etc.......... "Bob Phillips" wrote: Kathi, You mentioned that you wanted to average by quarter, so the INT((MONTH(B2:B20)+2)/3) works out the quarter for each date in B2:B20. The =4 is just an example testing for quarter 4, Oct, Nov, Dec. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kathi" wrote in message ... Thank you so much for the response. I think this is the type of formula I was looking for but if you could just clarify in my mind, please. I understand =AVERAGE(IF but am not clear on why finding the MONTH then rounding down (INT than adding +2) and dividing by /3) and this part really confuses me =4, I understand that finding the difference between relates to the average number of days open but am not clear how ??? If were more clear then I could definitely adapte this for y use. So for my use I understand =AVERAGE(C2:C20-B2:B20) BUT ONLY IF B2:B20=10/01/2003 AND ALSO IF B2:B20<=12/31/2003 so if there is a way to define the 1st or 2nd etc. quarter other than this I could really use that. THANKS AGAIN FOR YOUR TIME "Bob Phillips" wrote: Kathi, I am not sure what to do when the start date is in one quarter and the end date is in another, but assuming you count from the start date quarter, try this =AVERAGE(IF(INT((MONTH(B2:B20)+2)/3)=4,C2:C20-B2:B20)) |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF AND SUMIF AND COUNTIF
C D E
1 August 2, 2004 August 6, 2004 4 2 August 6, 2004 November 9, 2005 460 3 August 6, 2004 565 4 August 20, 2004 June 13, 2005 297 5 August 30, 2004 October 19, 2004 50 6 September 7, 2004 September 27, 2004 20 I have open dates in C and close dates in D and in E I have the formula {=MAX(IF(ISBLANK(D52),TODAY(),D52)-C52,1)} to give me the number of days the file was open. These dates begin in 2003 until present day and are added onto daily. I need to AVERAGE the days open (D-C) for each quarter of each fiscal year. =AVERAGE(IF(C1:C500="10/01/2003"<="12/31/2003"),D1:D500-C1:C500) but it would definitely be easier if I could instead use {=AVERAGE(IF(INT((MONTH(C1:C500)+2)/3)=4,D1:D500-C1:C500))} however, I don't understand how to get 1st QTR FY04, 2nd QTR FY04, 3rd QTR FY04, 4th QTR FY04, 1st QTR FY05, 2nd QTR FY05, 3rd QTR FY05, 4th QTR FY05, 1st QTR FY06, 2nd QTR FY06, etc... "Bob Phillips" wrote: Kathi, You mentioned that you wanted to average by quarter, so the INT((MONTH(B2:B20)+2)/3) works out the quarter for each date in B2:B20. The =4 is just an example testing for quarter 4, Oct, Nov, Dec. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kathi" wrote in message ... Thank you so much for the response. I think this is the type of formula I was looking for but if you could just clarify in my mind, please. I understand =AVERAGE(IF but am not clear on why finding the MONTH then rounding down (INT than adding +2) and dividing by /3) and this part really confuses me =4, I understand that finding the difference between relates to the average number of days open but am not clear how ??? If were more clear then I could definitely adapte this for y use. So for my use I understand =AVERAGE(C2:C20-B2:B20) BUT ONLY IF B2:B20=10/01/2003 AND ALSO IF B2:B20<=12/31/2003 so if there is a way to define the 1st or 2nd etc. quarter other than this I could really use that. THANKS AGAIN FOR YOUR TIME "Bob Phillips" wrote: Kathi, I am not sure what to do when the start date is in one quarter and the end date is in another, but assuming you count from the start date quarter, try this =AVERAGE(IF(INT((MONTH(B2:B20)+2)/3)=4,C2:C20-B2:B20)) |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF AND SUMIF AND COUNTIF
THANK YOU!
"Bob Phillips" wrote: If you want year as well =AVERAGE(IF((INT((MONTH(B2:B20)+2)/3)=4)*(YEAR(B2:B20=2004)),C2:C20-B2:B20)) for 4th QTR FY04 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kathi" wrote in message ... Forgive me if I'm being dense. I'm trying to comprehend. Okay, so when I want the average of the days open for a single fiscal year? How do I get the answer for 1st quarter Fiscal Year 2004, 2nd Quarter FY04, 3rd QTR FY04, 4th QTR FY04, 1st QTR FY05, 2nd QTR FY05, 3rd QTR FY05, 4th QTR FY05, 1st QTR FY06, 2nd QTR FY06, etc.......... "Bob Phillips" wrote: Kathi, You mentioned that you wanted to average by quarter, so the INT((MONTH(B2:B20)+2)/3) works out the quarter for each date in B2:B20. The =4 is just an example testing for quarter 4, Oct, Nov, Dec. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kathi" wrote in message ... Thank you so much for the response. I think this is the type of formula I was looking for but if you could just clarify in my mind, please. I understand =AVERAGE(IF but am not clear on why finding the MONTH then rounding down (INT than adding +2) and dividing by /3) and this part really confuses me =4, I understand that finding the difference between relates to the average number of days open but am not clear how ??? If were more clear then I could definitely adapte this for y use. So for my use I understand =AVERAGE(C2:C20-B2:B20) BUT ONLY IF B2:B20=10/01/2003 AND ALSO IF B2:B20<=12/31/2003 so if there is a way to define the 1st or 2nd etc. quarter other than this I could really use that. THANKS AGAIN FOR YOUR TIME "Bob Phillips" wrote: Kathi, I am not sure what to do when the start date is in one quarter and the end date is in another, but assuming you count from the start date quarter, try this =AVERAGE(IF(INT((MONTH(B2:B20)+2)/3)=4,C2:C20-B2:B20)) |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF AND SUMIF AND COUNTIF
This appears to work but it gives me a negative nubmer?????? Do you know why?
And if you could be so kind to explain I still don't understand the MONTH()+2/3? I like to know what it is I'm doing/asking for. THANKS AGAIN. "Bob Phillips" wrote: If you want year as well =AVERAGE(IF((INT((MONTH(B2:B20)+2)/3)=4)*(YEAR(B2:B20=2004)),C2:C20-B2:B20)) for 4th QTR FY04 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kathi" wrote in message ... Forgive me if I'm being dense. I'm trying to comprehend. Okay, so when I want the average of the days open for a single fiscal year? How do I get the answer for 1st quarter Fiscal Year 2004, 2nd Quarter FY04, 3rd QTR FY04, 4th QTR FY04, 1st QTR FY05, 2nd QTR FY05, 3rd QTR FY05, 4th QTR FY05, 1st QTR FY06, 2nd QTR FY06, etc.......... "Bob Phillips" wrote: Kathi, You mentioned that you wanted to average by quarter, so the INT((MONTH(B2:B20)+2)/3) works out the quarter for each date in B2:B20. The =4 is just an example testing for quarter 4, Oct, Nov, Dec. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kathi" wrote in message ... Thank you so much for the response. I think this is the type of formula I was looking for but if you could just clarify in my mind, please. I understand =AVERAGE(IF but am not clear on why finding the MONTH then rounding down (INT than adding +2) and dividing by /3) and this part really confuses me =4, I understand that finding the difference between relates to the average number of days open but am not clear how ??? If were more clear then I could definitely adapte this for y use. So for my use I understand =AVERAGE(C2:C20-B2:B20) BUT ONLY IF B2:B20=10/01/2003 AND ALSO IF B2:B20<=12/31/2003 so if there is a way to define the 1st or 2nd etc. quarter other than this I could really use that. THANKS AGAIN FOR YOUR TIME "Bob Phillips" wrote: Kathi, I am not sure what to do when the start date is in one quarter and the end date is in another, but assuming you count from the start date quarter, try this =AVERAGE(IF(INT((MONTH(B2:B20)+2)/3)=4,C2:C20-B2:B20)) |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF AND SUMIF AND COUNTIF
Kathi,
Enter 2004 in G1, 2004 in H1, 2004 in I1, 2004 in J1, 2005 in K1, 2005 in L1, etc., then 1 in G2, 2 in H2, 3 in I2, 4 in J2, 1 in K2, 2 in L2 etc. Then in G3 add =AVERAGE(IF((YEAR($C$1:$C$500=G$1))*(INT((MONTH($C $1:$C$500)+2)/3)=G$2),$E$1 :$E$500)) and copy down and across -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kathi" wrote in message ... C D E 1 August 2, 2004 August 6, 2004 4 2 August 6, 2004 November 9, 2005 460 3 August 6, 2004 565 4 August 20, 2004 June 13, 2005 297 5 August 30, 2004 October 19, 2004 50 6 September 7, 2004 September 27, 2004 20 I have open dates in C and close dates in D and in E I have the formula {=MAX(IF(ISBLANK(D52),TODAY(),D52)-C52,1)} to give me the number of days the file was open. These dates begin in 2003 until present day and are added onto daily. I need to AVERAGE the days open (D-C) for each quarter of each fiscal year. =AVERAGE(IF(C1:C500="10/01/2003"<="12/31/2003"),D1:D500-C1:C500) but it would definitely be easier if I could instead use {=AVERAGE(IF(INT((MONTH(C1:C500)+2)/3)=4,D1:D500-C1:C500))} however, I don't understand how to get 1st QTR FY04, 2nd QTR FY04, 3rd QTR FY04, 4th QTR FY04, 1st QTR FY05, 2nd QTR FY05, 3rd QTR FY05, 4th QTR FY05, 1st QTR FY06, 2nd QTR FY06, etc... "Bob Phillips" wrote: Kathi, You mentioned that you wanted to average by quarter, so the INT((MONTH(B2:B20)+2)/3) works out the quarter for each date in B2:B20. The =4 is just an example testing for quarter 4, Oct, Nov, Dec. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kathi" wrote in message ... Thank you so much for the response. I think this is the type of formula I was looking for but if you could just clarify in my mind, please. I understand =AVERAGE(IF but am not clear on why finding the MONTH then rounding down (INT than adding +2) and dividing by /3) and this part really confuses me =4, I understand that finding the difference between relates to the average number of days open but am not clear how ??? If were more clear then I could definitely adapte this for y use. So for my use I understand =AVERAGE(C2:C20-B2:B20) BUT ONLY IF B2:B20=10/01/2003 AND ALSO IF B2:B20<=12/31/2003 so if there is a way to define the 1st or 2nd etc. quarter other than this I could really use that. THANKS AGAIN FOR YOUR TIME "Bob Phillips" wrote: Kathi, I am not sure what to do when the start date is in one quarter and the end date is in another, but assuming you count from the start date quarter, try this =AVERAGE(IF(INT((MONTH(B2:B20)+2)/3)=4,C2:C20-B2:B20)) |
#15
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF AND SUMIF AND COUNTIF
I have no idea on the negative number Kathi, maybe it is because some of the
dates in D are blank (in the other line I suggest using E1:E500 instead of D1:D500-C1:C500). The other thing is trying to get the quarter. Each quarter is 3 months, so the first thought is to divide the month number by 3 to identify the quarter. However, Jan divided by gives 1/3, Feb gives 2/3 etc. So we use INT to get a whole number, but that gives 0 for Jan, 0 for Feb, 1 for Mar. By adding 2 to the month number before doing an INT, we are getting 1 for Jan, Feb and Mar, 2 for Apr, May, Jun, etc. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kathi" wrote in message ... This appears to work but it gives me a negative nubmer?????? Do you know why? And if you could be so kind to explain I still don't understand the MONTH()+2/3? I like to know what it is I'm doing/asking for. THANKS AGAIN. "Bob Phillips" wrote: If you want year as well =AVERAGE(IF((INT((MONTH(B2:B20)+2)/3)=4)*(YEAR(B2:B20=2004)),C2:C20-B2:B20)) for 4th QTR FY04 -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kathi" wrote in message ... Forgive me if I'm being dense. I'm trying to comprehend. Okay, so when I want the average of the days open for a single fiscal year? How do I get the answer for 1st quarter Fiscal Year 2004, 2nd Quarter FY04, 3rd QTR FY04, 4th QTR FY04, 1st QTR FY05, 2nd QTR FY05, 3rd QTR FY05, 4th QTR FY05, 1st QTR FY06, 2nd QTR FY06, etc.......... "Bob Phillips" wrote: Kathi, You mentioned that you wanted to average by quarter, so the INT((MONTH(B2:B20)+2)/3) works out the quarter for each date in B2:B20. The =4 is just an example testing for quarter 4, Oct, Nov, Dec. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "kathi" wrote in message ... Thank you so much for the response. I think this is the type of formula I was looking for but if you could just clarify in my mind, please. I understand =AVERAGE(IF but am not clear on why finding the MONTH then rounding down (INT than adding +2) and dividing by /3) and this part really confuses me =4, I understand that finding the difference between relates to the average number of days open but am not clear how ??? If were more clear then I could definitely adapte this for y use. So for my use I understand =AVERAGE(C2:C20-B2:B20) BUT ONLY IF B2:B20=10/01/2003 AND ALSO IF B2:B20<=12/31/2003 so if there is a way to define the 1st or 2nd etc. quarter other than this I could really use that. THANKS AGAIN FOR YOUR TIME "Bob Phillips" wrote: Kathi, I am not sure what to do when the start date is in one quarter and the end date is in another, but assuming you count from the start date quarter, try this =AVERAGE(IF(INT((MONTH(B2:B20)+2)/3)=4,C2:C20-B2:B20)) |
#16
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
AVERAGEIF AND SUMIF AND COUNTIF
Should be where e7 and e8 have your desired dates.
=AVERAGE(IF((CHECKS!$A$7:$A$291=E7)*(CHECKS!$A$7: $A$291<=E8),CHECKS!$D$7:$D$291)) or AVERAGE(IF((CHECKS!$A$7:$A$291=DATEVALUE("10/1/2005"))*(CHECKS!$A$7:$A$291<=DATEVALUE ("10/5/2005")),CHECKS!$D$7:$D$291)) The checksA was a defined name for the range. -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... adapt this array formula to your criteria. Don't forget to use ctrl+shift+enter for entering/editing. =AVERAGE(IF(ChecksA=O1,ChecksA<O2,ChecksD)) =AVERAGE(IF(a2:a200=O1,a2:a200<O2,d2:d200)) Don Guillett SalesAid Software "kathi" wrote in message ... I have a column with open date (B), column with closed date (C), column with count of days opened (D). I need to calculate the average number of days each were opened in the 1st, 2nd, 3rd and 4th quarter of the fiscal year as well as the entire fiscal year. I have been physicallly typing in each cell reference that has an opening date int he first quarter, but would like to be able to say SUM COLUMN D ONLY IF COLUMN B "=10/01/2004" also "<=12/31/2004". THEN COUNTIF COLUMN B ONLY IF "=10/01/2004" ALSO "<=12/31/2004" |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create an AVERAGEIF function. | Excel Worksheet Functions |