Ignore errors when calculation average of multiple ranges

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 when calculating an average for multiple (2) ranges (D4:P4 and U4:AG4, not 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]...
>
> 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 when
> calculating an average for _multiple_(2)_ranges_ (D4:P4 and U4:AG4, not
> just D4:P4).
>
> Thanks in advance for the help.
>
>
> --
> joshkraemer
> ------------------------------------------------------------------------
> joshkraemer's Profile:
> http://www.excelforum.com/member.php...o&userid=31508
> View this thread: http://www.excelforum.com/showthread...hreadid=511845
>

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!

In article <[email protected]>,
joshkraemer <[email protected]>
wrote:

> 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 when
> calculating an average for _multiple_(2)_ranges_ (D4:P4 and U4:AG4, not
> just D4:P4).
>
> Thanks in advance for the help.

"joshkraemer" wrote:
> 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_

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.

In article <[email protected]>,
"[email protected]" <[email protected]>
wrote:

> "joshkraemer" wrote:
> > 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_
>
> 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.