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!
 

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

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top