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)),"",AVERAGE(A1:A10))



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



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

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




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



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

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

--
Regards,

Peo Sjoblom

Portland, Oregon




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

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

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: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=501538