average formula without getting div/0 error...????

[Escher]

i have a column of numbers aht (average handle time) in seconds, and it is listed per
days of the week... for each of my agents, is there a formula to tkae the average and leave out the zero, if someone is absent..

412
300
356
435
0

=#div/0!.. i want to be able to use one formula and get the average no matter if a zero is present or not.. is it possible..??

 

[Mark W.]

You can't use a discontiguous cell selection with COUNTIF. Instead, use...

{=AVERAGE(IF(ROW($H$1:$H$330)={72,149,222,294,330},IF($H$1:$H$330,$H$1:$H$330)))}

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula.
This message was edited by Mark W. on 2002-04-09 12:53

 

[Dave Patton]

1. ensure none of the cells contain Div/0 or other error messages; revise them to show show blank or 0.

2. Name the relevant cells (my example uses rN)

3. Array enter

=SUM(rN)/SUM(N(LARGE(rN,ROW(INDIRECT("1:"&COUNT(rN))))<>0))

( I believe David Hager developed this solution)
This message was edited by Dave Patton on 2002-04-09 12:51

 

[Mark W.]

On 2002-04-09 12:48, Dave Patton wrote:
1. ensure none of the cells contain Div/0 or other error messages; revise them to show show blank or 0.

2. Name the relevant cells (my example uses rN)

3. Array enter

=SUM(rN)/SUM(N(LARGE(rN,ROW(INDIRECT("1:"&COUNT(rN))))<>0))

( I believe David Hager developed this solution)
This message was edited by Dave Patton on 2002-04-09 12:51

However, be forewarned that there's a limit to the length of a defined name reference (256 characters?), and each cell reference is prefixed by its sheet name (e.g., =Sheet2!$H$72,Sheet2!$H$149,Sheet2!$H$222,Sheet2!$H$294,Sheet2!$H$330...) which imposes a "ceiling" on the number of discontiguous cells than can be listed (< 25 depending on the length of the sheet name and the magnitude of the row numbers involved).
This message was edited by Mark W. on 2002-04-09 13:14

 

[Aladin Akyurek]

On 2002-04-09 11:26, Escher wrote:
=SUM(H72,H149,H222,H294,H330)/MAX(1,COUNT(H72,H149,H222,H294,H330)-COUNTIF(H72,H149,H222,H294,H330,0))

is this right?????

Not quite... COUNTIF requires a contiguous range...

Try:

=SUM(H72,H149,H222,H294,H330)/MAX(1,COUNT(H72,H149,H222,H294,H330)-SUMPRODUCT((H72=0)+(H149=0)+(H222=0)+(H294=0)+(H330=0)))

 

[Mark W.]

=SUM(H72,H149,H222,H294,H330)/MAX(1,COUNT(H72,H149,H222,H294,H330)-SUMPRODUCT((H72=0)+(H149=0)+(H222=0)+(H294=0)+(H330=0)))

Aladin, better take another look at this formula. The divisor is 1 (if H294 and H330 are empty) and so it's just summing.
This message was edited by Mark W. on 2002-04-09 13:50

 

[lars]

sure use this


{=AVERAGE(IF(A1:A6<>0,A1:A6))}

Array formula so hit control/shift enter at the same time

 

[Mark W.]

On 2002-04-09 13:53, lars wrote:
sure use this


{=AVERAGE(IF(A1:A6<>0,A1:A6))}

Array formula so hit control/shift enter at the same time

lars, been there done that... the range is discontiguous requiring this array formula...

{=AVERAGE(IF(ROW($H$1:$H$330)={72,149,222,294,330},IF($H$1:$H$330,$H$1:$H$330)))}

 

[lars]

my bad