# Help with Formula

##### Board Regular
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!

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.

#### Marc

##### Active Member
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
ABCDEF
116.33333
2EventA09/16/0610/17/06Closed
3EventB10/16/06Open1
4EventC09/14/06Open23
5EventD09/13/0611/01/06Closed
6EventE09/12/06Open25
7EventF09/11/0611/11/06Closed
8EventG09/10/0611/16/06Closed
Sheet1

##### Board Regular
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

##### Active Member
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.

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

##### Board Regular
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.

Replies
8
Views
143
Replies
7
Views
49
Replies
3
Views
50
Replies
6
Views
138
Replies
1
Views
98