Average function with #VALUE! error in reference cells

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

 

[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

 

[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

 

[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

 

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

 

[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

 

[Larry4500]

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

 

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

 

[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)

 

[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)