Average function with #VALUE! error in reference cells

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" <jurge100@yahoo.com> wrote in message
news:1137372429.407595.187060@g44g2000cwa.googlegroups.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.
>

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

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" <chip@cpearson.com> wrote in message
news:u2yGMbjGGHA.2684@TK2MSFTNGP11.phx.gbl...
> 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" <jurge100@yahoo.com> wrote in message
> news:1137372429.407595.187060@g44g2000cwa.googlegroups.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" <jurge100@yahoo.com> wrote in message
news:1137377401.251542.25810@g49g2000cwa.googlegroups.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.

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({"A1","A4","A6","A8"})))

similar for MIN, MAX, etc.

--

HTH

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


"Larry4500" <jurge100@yahoo.com> wrote in message
news:1137386298.291840.258740@z14g2000cwz.googlegroups.com...
> 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.
>

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)


"Larry4500" <jurge100@yahoo.com> wrote in message
news:1137386298.291840.258740@z14g2000cwz.googlegroups.com...
> 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.
>