Using IF function to calculate average minus Max/min

Hello,

I have run up against a problem. I am trying to automate a process involving a Grubb's outlier test and calculating averages/stdev of a set of numbers. I have the data in one column and have calculated the average/stdev/%CV of that data. I have also added a formula to calculate Max/Min outliers of that column of data. Then there are cells with IF statements that display either "yes" (for an outlier present), or "no" (if no outlier is present). What I would like to do, if it is feasible, would be to set up formulas to recalculate the average/stdev of the column, taking into consideration either the presence or absence of outliers.

Here is what I invision(cells are for example purposes)

=IF((M9 = yes, Avg(H2:H40)-max(H2:H40)),M9 = no, avg(H2:H40))

So it would calculate the average without the outlier if the outlier "yes" was present, or just calculate the average if "no" is present.

I would also do this with a minimum outlier as well, but i can set that up if this first one is possible. Sorry for the lengthy post. This has befuddled me all afternoon.

Thanks for any help, Oh wise Excel Masters.

Thanks for the quick reply.

I never thought of flipping the average to the front of the formula. Might be something there. I tried the formula you posted, but for "yes" it calculated the average and then subracts the outlier from that average. I would like to calculate the average of the column excluding the outlier if one if present, if the reference cell is "yes". Then, for "no" it would just calculate the average including all of the data since no outlier is present to exclude. I am sorry if I am not explaining this well. It is one of those things where my thoughts do not necessarily translate into words.

FDibbins,

That formula works perfectly! I have a lot to learn about the true potential for Excel.
Would it be greedy to add to that formula to also take into account the min outlier at the same time? So if there is an outlier ("yes" for either min or max), then the average of the column is recalculated with that outlier excluded from the average? And if "no" outlier is present just to recalculate the average? I am leaning towards that being too much for Excel to handle in one formula, but who knows.

=IF(M9 ="yes", N9 = "yes",AVERAGEIF(H2:H41,"<"&MAX(H2:H41),averageif(H2:H41,">"&MIN(H2:H41)),AVERAGE(H2:H41))

As of now that it too many arguments for Excel...

I would just like to keep the post-Grubbs check average/stdev calculations to as few cells as possible, but i may not be able to do that If i wish to automate the process.

Thanks for the advice.

Well, I think that I will have to either take out any autocalculations, or maybe figure out a CSE format to do it. With that last formula it gives a True/False, but I put a new ending on it to get it to calculate column average. I still can not get it to check if either a max or a min outlier is present and then exclude those from calculations. It will only do either max or min, not both.

But, thanks for putting up with me.

I have added some dummy data to the file. The cells in question are L14, and P14. And ideally they would be based upon the Grubbs outlier box abovs. I have an equation in P14 that detects an outlier (the Max) and removes it from the recalculated average, but i need it to also consider the Min values. So the recalculated C/M avg would detect an outlier from the Grubbs box above (presence of "yes" in MAx/Min cells), and adjust the data average accordingly. And if no outliers were detected, then the average would be the same as L2/P2 (which is just the overall average before the Outlier check). I do not think it is feasible and may just have to manually adjust the average/stdev calculations after the outlier check.