software

Does anyone know how to make the average,median,max, and/or min
functions ignore cells in the referenced range that contain the
#value! error? All four of the functions are returning #value!
because one or more in the referenced range have the error. Thank you.Larry,

You need to wrap your function in an ISERR function.

=IF(ISERR(AVERAGE(A1:A10)),quot;quot;,AVERAGE(A1:A10))
--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
quot;Larry4500quot; gt; wrote in message oups.com...
gt; Does anyone know how to make the average,median,max, and/or min
gt; functions ignore cells in the referenced range that contain the
gt; #value! error? All four of the functions are returning
gt; #value!
gt; because one or more in the referenced range have the error.
gt; Thank you.
gt;

Assuming your range is A1:A5 for average you can use

=AVERAGE(IF(ISNUMBER($A$1:$A$5),$A$1:$A$5))

confirmed with CTRL SHIFT ENTER

replace average with MIN, MAX and MEDIAN for your other functions--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=501538Or if you don't want it to be blank if there's an error

=AVERAGE(IF(ISNUMBER(A1:A10),A1:A10))

entered with ctrl shift amp; enter

--
Regards,

Peo Sjoblom

Portland, Oregon

quot;Chip Pearsonquot; gt; wrote in message
...
gt; Larry,
gt;
gt; You need to wrap your function in an ISERR function.
gt;
gt; =IF(ISERR(AVERAGE(A1:A10)),quot;quot;,AVERAGE(A1:A10))
gt;
gt;
gt;
gt; --
gt; Cordially,
gt; Chip Pearson
gt; Microsoft MVP - Excel
gt; Pearson Software Consulting, LLC
gt; www.cpearson.com
gt;
gt;
gt;
gt; quot;Larry4500quot; gt; wrote in message
gt; oups.com...
gt;gt; Does anyone know how to make the average,median,max, and/or min
gt;gt; functions ignore cells in the referenced range that contain the
gt;gt; #value! error? All four of the functions are returning #value!
gt;gt; because one or more in the referenced range have the error. Thank you.
gt;gt;
gt;
gt;Thank you all for the help. I tried all three and now I'm getting
either a blank, a -, or '. What I'm trying to do is make the function
average or find the median of all the cells in the range that do not
have an error. If anyone knows how to do this, I would appreciate the
help. Thanks.The formula I gave will work if there are nunmbers in the range
note that it needs to be entered with ctrl shift amp; enter

--
Regards,

Peo Sjoblom

Portland, Oregon

quot;Larry4500quot; gt; wrote in message ups.com...
gt; Thank you all for the help. I tried all three and now I'm getting
gt; either a blank, a -, or '. What I'm trying to do is make the function
gt; average or find the median of all the cells in the range that do not
gt; have an error. If anyone knows how to do this, I would appreciate the
gt; help. Thanks.
gt;Last question: the formula what works perfectly for all three except
the MIN function. Any idea why? Thanks again.Last last question: how do you use this function and reference two or
more groups of noncontiguous cells? So, first I'm calculating the
averages of the subgroups and then I want the average of the larger
groups as a whole. Thank you.This gives the average

=AVERAGE(N(INDIRECT({quot;A1quot;,quot;A4quot;,quot;A6quot;,quot;A8quot;})))

similar for MIN, MAX, etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)quot;Larry4500quot; gt; wrote in message oups.com...
gt; Last last question: how do you use this function and reference two or
gt; more groups of noncontiguous cells? So, first I'm calculating the
gt; averages of the subgroups and then I want the average of the larger
gt; groups as a whole. Thank you.
gt;
AN improvement

=AVERAGE(IF(N(INDIRECT({quot;A1quot;,quot;A4quot;,quot;A6quot;,quot;A8quot;}))gt;0,N (INDIRECT({quot;A1quot;,quot;A4quot;,quot;A6quot;,
quot;A8quot;}))))

BTW, these are array formulae, so commit with Ctrl-Shift-Enter

--

HTH

RP
(remove nothere from the email address if mailing direct)quot;Larry4500quot; gt; wrote in message oups.com...
gt; Last last question: how do you use this function and reference two or
gt; more groups of noncontiguous cells? So, first I'm calculating the
gt; averages of the subgroups and then I want the average of the larger
gt; groups as a whole. Thank you.
gt;