Average function with #VALUE! error in reference cells

L

Larry4500

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

Chip Pearson

Larry,

You need to wrap your function in an ISERR function.

=IF(ISERR(AVERAGE(A1:A10)),"",AVERAGE(A1:A10))



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
D

daddylonglegs

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
 
P

Peo Sjoblom

Or 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 & enter

--
Regards,

Peo Sjoblom

Portland, Oregon
 
L

Larry4500

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

Peo Sjoblom

The formula I gave will work if there are nunmbers in the range
note that it needs to be entered with ctrl + shift & enter

--
Regards,

Peo Sjoblom

Portland, Oregon
 
L

Larry4500

Last question: the formula what works perfectly for all three except
the MIN function. Any idea why? Thanks again.
 
L

Larry4500

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

Bob Phillips

This gives the average

=AVERAGE(N(INDIRECT({"A1","A4","A6","A8"})))

similar for MIN, MAX, etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
B

Bob Phillips

AN improvement

=AVERAGE(IF(N(INDIRECT({"A1","A4","A6","A8"}))>0,N(INDIRECT({"A1","A4","A6",
"A8"}))))

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

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
Top