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

### Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

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

=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
24
Views
721
Replies
3
Views
168
Replies
5
Views
156
Replies
0
Views
61
Replies
5
Views
268

1,141,923
Messages
5,709,360
Members
421,632
Latest member
BrennieB

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back