excluding data from a formula

[bonnaru]

I have a set of data containing outliers based on a median and mean. The data is all in one column and the median and mean are each calculated in one cell via the average and mean formulas. in another column, if a value is an outlier i have it marked as "high" "low" or "" depending on whether its high, low, or not an outlier. What i would like to do is have the mean and median functions which currently look like =AVERAGE(A4:A32) exclude data that has "high" or "low" in the same row B column.

Thanks!

 

[Rijnsent]

Hi,
if you have an Excel version 2007 or higher, you could use AVERAGEIF for the average, otherwise you'll have to use SUMIF / COUNTIF (old fashioned calculation ) or an array formula.
For the MEDIAN, the only easy solution is an array formula, see e.g. http://www.mrexcel.com/archive/Formulas/21143.html

Cheers,
Koen