Help with Formula

madchemist

Board Regular
Joined
Jul 10, 2006
Messages
198
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!
 

Some videos you may like

Excel Facts

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

Marc

Active Member
Joined
Feb 21, 2002
Messages
388
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
 

madchemist

Board Regular
Joined
Jul 10, 2006
Messages
198
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
Joined
Feb 21, 2002
Messages
388
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

Board Regular
Joined
Jul 10, 2006
Messages
198
there is if you use CSE (Control + Shift + Enter) formulas, but for some reason it is not returning the proper value
 

madchemist

Board Regular
Joined
Jul 10, 2006
Messages
198
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,052
Messages
5,545,735
Members
410,703
Latest member
yaronjoseph
Top