Help with Formula

[madchemist]

I have a spreadsheet that has a list of items. Some of them have been completed and some need to be completed.

In column A is the event. Column B is the date it was assigned. Column C is the date it was completed (Blank if not complete). Column D has the word Open or Closed written in it to correspond to Item Status.

I want to calculate an average of the number or workdays all open items have been open. Is there a way to use the =Networkdays, =Today() and the =average formula to figure this out. Keep in mind the formula has to be written to exclude any rows that have the word "Closed" written in column D.

Thanks for your help!

 

[Marc]

I'm sure you'll get smoother solutions, but here's what I came up with:

I added column E to the sheet housing this formula: IF(D2="Open",NETWORKDAYS(B2,NOW()),""). This calculates the workdays between today and the date assigned IF the formula in column D reads 'Open'. (You can always HIDE this column.)

The formula in column D: IF(ISBLANK(C2),"Open","Closed") determines if the cell in column C is blank or not.

Lastly, I just set a reference name to column E (AVG) and then you can place the formula AVERAGE(AVG) anywhere to get the average days your open items have been open.

HTH

Book1001.xls
	A	B	C	D	E	F
1						16.33333
2	EventA	09/16/06	10/17/06	Closed
3	EventB	10/16/06		Open	1
4	EventC	09/14/06		Open	23
5	EventD	09/13/06	11/01/06	Closed
6	EventE	09/12/06		Open	25
7	EventF	09/11/06	11/11/06	Closed
8	EventG	09/10/06	11/16/06	Closed
Sheet1

 

[madchemist]

I thank you for your suggestion. Here is what I have figured out so far....

=IF(R2="Open",NETWORKDAYS(A2,TODAY()),NETWORKDAYS(A2,P2)) will return the correct number of days an item has been open or closed.

I am trying to use the formula: =AVERAGE(IF(R2:R82="Open",S2:S82))

but for some reason it is averaging the items that are closed to...

any suggestions on how I can average a group of numbers including only those in which column R is "Open"

Thanks

 

[Marc]

How about trying this formula:

=SUMIF(R2:R82,"Open",S2:S82)/COUNTIF(R2:R82,"Open")

It sums only those items in range S2:S82 that have 'Open' in the range R2:R82. Then it divides it by the count of 'Open' items in range R2:R82.


I don't think there's an AVERAGEIF function.

 

[madchemist]

there is if you use CSE (Control + Shift + Enter) formulas, but for some reason it is not returning the proper value

 

[madchemist]

awww....they both work. One of the rows was missing an "Open" so thats why it was coming up with a defunct value. Thanks for your help Marc.