software

I'll make this short and brief.

Here's what works:

{AVERAGE(IF(ISERROR(D4:P4),quot;quot;,D4:P4))}

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

{AVERAGE(IF(ISERROR(D4:P4,U4:AG4),quot;quot;,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: www.excelforum.com/member.php...oamp;userid=31508
View this thread: www.excelforum.com/showthread...hreadid=511845You should really fix the errors since if you do you can use both ranges in
an average formula

=SUM(SUMIF(D4:P4,quot;lt;gt;#DIV/0!quot;),SUMIF(U4:AG4,quot;lt;gt;#DIV/0!quot;))/MAX(1,SUM(COUNTIF(D4:P4,quot;lt;gt;#DIV/0!quot;),COUNTIF(U4:AG4,quot;lt;gt;#DIV/0!quot;)))

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

quot;joshkraemerquot; gt;
wrote in message
...
gt;
gt; I'll make this short and brief.
gt;
gt; Here's what works:
gt;
gt; {AVERAGE(IF(ISERROR(D4:P4),quot;quot;,D4:P4))}
gt;
gt; Here's what I want to do (but doesn't work):
gt;
gt; {AVERAGE(IF(ISERROR(D4:P4,U4:AG4),quot;quot;,D4:P4,U4:AG4) )}
gt;
gt; What am I doing wrong? I want to be able to ignore all errors when
gt; calculating an average for _multiple_(2)_ranges_ (D4:P4 and U4:AG4, not
gt; just D4:P4).
gt;
gt; Thanks in advance for the help.
gt;
gt;
gt; --
gt; joshkraemer
gt; ------------------------------------------------------------------------
gt; joshkraemer's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31508
gt; View this thread: www.excelforum.com/showthread...hreadid=511845
gt;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 gt;,
joshkraemer gt;
wrote:

gt; I'll make this short and brief.
gt;
gt; Here's what works:
gt;
gt; {AVERAGE(IF(ISERROR(D4:P4),quot;quot;,D4:P4))}
gt;
gt; Here's what I want to do (but doesn't work):
gt;
gt; {AVERAGE(IF(ISERROR(D4:P4,U4:AG4),quot;quot;,D4:P4,U4:AG4) )}
gt;
gt; What am I doing wrong? I want to be able to ignore all errors when
gt; calculating an average for _multiple_(2)_ranges_ (D4:P4 and U4:AG4, not
gt; just D4:P4).
gt;
gt; Thanks in advance for the help.

quot;joshkraemerquot; wrote:
gt; Here's what I want to do (but doesn't work):
gt; {AVERAGE(IF(ISERROR(D4:P4,U4:AG4),quot;quot;,D4:P4,U4:AG4) )}
gt; [....] I want to be able to ignore all errors when calculating
gt; 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 gt;,
quot; gt;
wrote:

gt; quot;joshkraemerquot; wrote:
gt; gt; Here's what I want to do (but doesn't work):
gt; gt; {AVERAGE(IF(ISERROR(D4:P4,U4:AG4),quot;quot;,D4:P4,U4:AG4) )}
gt; gt; [....] I want to be able to ignore all errors when calculating
gt; gt; an average for _multiple_(2)_ranges_
gt;
gt; General form of the array formula (ctrl-shift-Enter):
gt;
gt; =average(if(condition1,range1), if(condition2,range2), ...)
gt;
gt; In your case:
gt;
gt; =average(if(not(iserror(U2:P4)), U2:P4),
gt; if(not(iserror(U4:AG4)), U4:AG4))
gt;
gt; PS: Personally, I would avoid the errors within the ranges
gt; in the first place. Makes for a less messy spreadsheet.