Thanks:  0
Likes:  0

# Thread: average formula without getting div/0 error...????

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

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

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

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

5. =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 ]

6. sure use this

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

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

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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•