AVERAGEIF AND SUMIF AND COUNTIF

G

Guest

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"
 
D

Don Guillett

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
(e-mail address removed)
 
B

Bob Phillips

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)
 
G

Guest

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

:
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))
 
G

Guest

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"
 
G

Guest

=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)
I forgot to check notify me of replies so I'm just sending again for that.
Thanks.
Kathi
 
B

Bob Phillips

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)
 
G

Guest

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..........
 
B

Bob Phillips

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)
 
G

Guest

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...
 
G

Guest

THANK YOU!

Bob Phillips said:
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)
 
G

Guest

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.
 
B

Bob Phillips

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)
 
B

Bob Phillips

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)
 
D

Don Guillett

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top