Running average results of formula

[dolphintabby]

How can I get the average of the results of a formula and have it update with every entry?
I have a column that calculates the cycle days between when I receive a report request and when I complete it. The formula is =NETWORKDAYS(M29,O29,) I want to calculate on a summary page what my average cycle time in days are and keep it a running total on a summary page. On the cells where I have a report request but haven't completed it yet so there is no complete date the results are a negative number so I want it to ignore negative numbers and blank cells or zero's.

 

[Somendra Misra]

@dolphintabby

Consider uploading a sample file.

Regards,

 

[SirJB7]

Hi, dolphintabby!

Give a look at the uploaded file.

Column A: anything
Column B: starting date, required
Column C: completion date, optional
Column D: completion days, helper column (may be hidden)
D2:D21 : =SI(C2="";0;C2-B2) -----> in english: =IF(C2="",0,C2-B2) ... formatted as: General;General;# (for not displaying zeroes)Cell F1: =PROMEDIOA(D2:D21) -----> in english: =AVERAGEA(D2:D21)

Regards!

 

[AIM]

Hi SirJB7,

The Average function in your file is calculating the average for the entire column and not just the non blank cells (the average shown is 28, I believe it should be 71).

I think a better option would be using the AVERAGEIF formula (which ignores negative numbers, blank cells or zero's) :=AVERAGEIF(D2:D21,">"&0,D2:D21)

I have uploaded the file with my formula.

Regards!

 

[oldchippy]

Hi @dolphintabby ,

To ingnore the negative numbers put this in F2 and copy down

=IF(C2="","",C2-B2)

 

[SirJB7]

@AIM
Hi!
Thanks for the catch.
Regards!

Hi, dolphintabby!
Please use AIM's formula in F1 cell.
Regards!

 

[NARAYANK991]

Hi ,

The average excluding only the blank cells in column C , works out to 63 ; the following array entered formula ( entered using CTRL SHIFT ENTER ) also gives 63 :

=SUM(IF($C$2:$C$21>0,$C$2:$C$21,$B$2:$B$21)-($B$2:$B$21))/COUNTIF($C$2:$C$21,">0")

The average excluding the blank cells and zeroes ( where the completion date is the same as the start date ) works out to 71 ; the following array entered formula ( entered using CTRL SHIFT ENTER ) also gives 71 :

=SUM(IF($C$2:$C$21>0,$C$2:$C$21,$B$2:$B$21)-($B$2:$B$21))/SUM(IF($C$2:$C$21>$B$2:$B$21,1,0))

This assumes that no cell in column B , within the range B2 through B21 , will be blank.

Of course , OP did mention that NETWORKDAYS is used , so this discussion is rather academic.

Narayan

 

[dolphintabby]

Hi, dolphintabby!

Give a look at the uploaded file.

Column A: anything
Column B: starting date, required
Column C: completion date, optional
Column D: completion days, helper column (may be hidden)
D2:D21 : =SI(C2="";0;C2-B2) -----> in english: =IF(C2="",0,C2-B2) ... formatted as: General;General;# (for not displaying zeroes)Cell F1: =PROMEDIOA(D2:D21) -----> in english: =AVERAGEA(D2:D21)

Regards!

Thank you that worked

 

[dolphintabby]

Hi SirJB7,

The Average function in your file is calculating the average for the entire column and not just the non blank cells (the average shown is 28, I believe it should be 71).

I think a better option would be using the AVERAGEIF formula (which ignores negative numbers, blank cells or zero's) :=AVERAGEIF(D2:D21,">"&0,D2:D21)

I have uploaded the file with my formula.

Regards!

Oh okay thank you

 

[SirJB7]

Hi, dolphintabby!
Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.
Regards!