Using Average

[Joe Gieder]

First, thank you for helping. I hope this makes sense.

I'm trying to come up with a formula that will display the AVERAGE amount of
days a PO is open. The data that I have is:
PO Item Date Open Date Closed
1 1 01-25-09 02-15-09
1 2 01-30-09
1 3 02-05-09
2 1 02-14-09 02-25-09
3 1 02-25-09
4 1 03-02-09
4 2 03-02-09
For the items still open (date closed is blank) I want to average those only
by using the TODAY() function in the formula where the item is open, can this
be done?
The result I'm trying to find is:
PO Avg Days Open
1 168
2 0
3 152
4 149

Thank you in advance
Joe

 

[smartin]

First, thank you for helping. I hope this makes sense.

I'm trying to come up with a formula that will display the AVERAGE amount of
days a PO is open. The data that I have is:
PO Item Date Open Date Closed
1 1 01-25-09 02-15-09
1 2 01-30-09
1 3 02-05-09
2 1 02-14-09 02-25-09
3 1 02-25-09
4 1 03-02-09
4 2 03-02-09
For the items still open (date closed is blank) I want to average those only
by using the TODAY() function in the formula where the item is open, can this
be done?
The result I'm trying to find is:
PO Avg Days Open
1 168
2 0
3 152
4 149

Thank you in advance
Joe

I can only reproduce your desired results by employing NETWORKDAYS.

Here is one approach:

Calculate NETWORKDAYS in a helper column*. I used

H2 =NETWORKDAYS(C2,IF(D2="",TODAY(),D2))

filled down through H8.

Next place 1, 2, 3, 4 in A11:A14 (your lookup values). Enter one of the
following formulas where it is convenient:

Formula A (no error handling,non-array)

=SUMPRODUCT(--($A$2:$A$8=A11),--($D$2:$D$8=""),$H$2:$H$8)/SUMPRODUCT(--($A$2:$A$8=A11),--($D$2:$D$8=""))

Formula B (no error handling, array-entered**)
=AVERAGE(IF(($A$2:$A$8=A11)*($D$2:$D$8=""),$H$2:$H$8))

Formula C (with error handling, non-array)

=IF(SUMPRODUCT(--($A$2:$A$8=A11),--($D$2:$D$8=""))=0,0,SUMPRODUCT(--($A$2:$A$8=A11),--($D$2:$D$8=""),$H$2:$H$8)/SUMPRODUCT(--($A$2:$A$8=A11),--($D$2:$D$8="")))

Formula D (with error handling, array-entered**)

=IF(ISERROR(AVERAGE(IF(($A$2:$A$8=A11)*($D$2:$D$8=""),$H$2:$H$8))),0,AVERAGE(IF(($A$2:$A$8=A11)*($D$2:$D$8=""),$H$2:$H$8)))


* AFAIK NETWORKDAYS does not play nicely with arrays as input, so it is
necessary to precalculate it.

** Commit array formulas by pressing Ctrl+Shift+Enter; do not just press
Enter or Tab.