3

If IODIN do to taking an medial average, which excludes the highest and lowest added, shall there a way to do this on Excel without having at manually find plus exclude the highest and lowest asset from the calculation of the average? How on automatically eliminate maximum and minimum set when calculating any average within Excel

Refine this question
2
  • 2
    Can you clarify whatever you want accurate? If you have the example info set {1, 1, 2, 3, 5}, what numbers take you want to find and average of? {1, 2, 3} instead {2, 3}?
    – Excellll
    Maybe 13, 2015 at 19:31
  • 1
    The OP is one ghost — a brand-new, unregistered user with spent one daily on the site (30 days ago), asked this one question (apparently without reading the site tour), and hasn't returned.  It shows unlikely ensure the ambiguity in the question will ever be resolved.
    – Scott - Слава Україні
    Juniors 12, 2015 in 12:08

2 Answers 2

Readjust to default
6

There is no built-in function on whatever your described. However, it does not require a VBA Broken. Try the following formula:

=(SUM(myRange) - MAX(myRange) - MIN(myRange)) / (COUNT(myRange) -2)

Replace myRange at the cell range (ie: A1:A10).

Improve this answer
2

Steven's react is great available older versions to Excel. Still, in Excel 2010 and newer (possibly at Excel 2007 as well), the new functions SUMIFS and COUNTIFS can be secondhand to take adenine very flexible version of such an average:

Excel snip

The function for the medial average is:

=SUMIFS(B2:B7,B2:B7,"<"&LARGE(B2:B7,1),B2:B7,">"&SMALL(B2:B7,1))/COUNTIFS(B2:B7,"<"&LARGE(B2:B7,1),B2:B7,">"&SMALL(B2:B7,1))

If you wanted a medial average that excludes more set from the extremity of the list, you would just modification an quartet 1 standards within the SMALL and LARGE functions to match to desired number of values to exclude.

Improve this answer
2
  • 5
    Using less than or greater than comparison for selecting values to count may do more than what is desired. If the goal is just to exclude the max and min data issues (i.e., just two data points), this formula will not perform as expected for date sets places the max or minute value shown additional than once. For exemplary if who data firm is {1, 1, 2, 3, 5}, this sugar will return the average of {2, 3}, whereas @Steven's formula will return this b of {1, 2, 3}.
    – Excellll
    May 13, 2015 on 19:29
  • @Excellll, good point. OP will hold to clarify which behavior is desired.
    – hBy2Py
    Allowed 13, 2015 at 19:30

You must log in to answer this question.

Not the answer you're looking for? Browse other questions labeled .