Ignore errors when calculation average of multiple ranges

  • Thread starter Thread starter joshkraemer
  • Start date Start date
J

joshkraemer

I'll make this short and brief.

Here's what works:

{AVERAGE(IF(ISERROR(D4:P4),"",D4:P4))}

Here's what I want to do (but doesn't work):

{AVERAGE(IF(ISERROR(D4:P4,U4:AG4),"",D4:P4,U4:AG4))}

What am I doing wrong? I want to be able to ignore all errors whe
calculating an average for _multiple_(2)_ranges_ (D4:P4 and U4:AG4, no
just D4:P4).

Thanks in advance for the help
 
You should really fix the errors since if you do you can use both ranges in
an average formula

=SUM(SUMIF(D4:P4,"<>#DIV/0!"),SUMIF(U4:AG4,"<>#DIV/0!"))/MAX(1,SUM(COUNTIF(D4:P4,"<>#DIV/0!"),COUNTIF(U4:AG4,"<>#DIV/0!")))

will work (replace #DIV/0! with the error you can have) but it will fail if
you have empty cells since they will be counted


--
Regards,

Peo Sjoblom

Northwest Excel Solutions

Portland, Oregon




"joshkraemer" <[email protected]>
wrote in message
news:[email protected]...
 
Try...

=AVERAGE(IF(1-ISNUMBER(MATCH(COLUMN(D4:AG4)-COLUMN(D4)+1,{14,15,16,17},0)
),IF(ISNUMBER(D4:AG4),D4:AG4)))

....confirmed with CONTROL+SHIFT+ENTER.

Notes:

1) The array constant {14,15,16,17} determines which columns, relative
to the first column in your range (Column D), to exclude in your
average. In this case, Columns 14 through 17 are excluded.

2) Empty cells will not be counted.

Hope this helps!
 
joshkraemer said:
Here's what I want to do (but doesn't work):
{AVERAGE(IF(ISERROR(D4:P4,U4:AG4),"",D4:P4,U4:AG4))}
[....] I want to be able to ignore all errors when calculating
an average for _multiple_(2)_ranges_
Click to expand...

General form of the array formula (ctrl-shift-Enter):

=average(if(condition1,range1), if(condition2,range2), ...)

In your case:

=average(if(not(iserror(U2:P4)), U2:P4),
if(not(iserror(U4:AG4)), U4:AG4))

PS: Personally, I would avoid the errors within the ranges
in the first place. Makes for a less messy spreadsheet.
 
Nice! Definitely much simpler and more efficient. Although I would
change it slightly to the following...

=AVERAGE(IF(ISNUMBER(D4:P4),D4:P4),IF(ISNUMBER(U4:AG4),U4:AG4))

....confirmed with CONTROL+SHIFT+ENTER.

Three reasons:

1) Your formula seems to count empty cells, whereas this syntax seems to
ignore them.

2) It's a little easier to understand.

3) It looks nicer. :)

joshkraemer said:
Here's what I want to do (but doesn't work):
{AVERAGE(IF(ISERROR(D4:P4,U4:AG4),"",D4:P4,U4:AG4))}
[....] I want to be able to ignore all errors when calculating
an average for _multiple_(2)_ranges_
Click to expand...

General form of the array formula (ctrl-shift-Enter):

=average(if(condition1,range1), if(condition2,range2), ...)

In your case:

=average(if(not(iserror(U2:P4)), U2:P4),
if(not(iserror(U4:AG4)), U4:AG4))

PS: Personally, I would avoid the errors within the ranges
in the first place. Makes for a less messy spreadsheet.
Click to expand...
 
Back
Top