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.


--
joshkraemer
------------------------------------------------------------------------
joshkraemer's Profile: http://www.excelforum.com/member.php...o&userid=31508
View this thread: http://www.excelforum.com/showthread...hreadid=511845

Ignore errors when calculation average of multiple ranges
 

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"
wrote in message
...

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

Ignore errors when calculation average of multiple ranges
 

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 ,
joshkraemer
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.

Ignore errors when calculation average of multiple ranges
 

"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.

Ignore errors when calculation average of multiple ranges
 

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 ,
"
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.