Need help with range of cells

[Guest]

I am needing help; I use spreadsheets entering 6 scores and have to throw out
the high and low and average the remaining 4. This I can do; BUT if we add 6
more scores to the scenario making a total of 12, is there a way to throw out
3 high and 3 low and average the remaining 6?

Thank you for any suggestions!!

 

[Rick Rothstein \(MVP - VB\)]

I am needing help; I use spreadsheets entering 6 scores and have to throw

out
the high and low and average the remaining 4. This I can do; BUT if we
add 6
more scores to the scenario making a total of 12, is there a way to throw
out
3 high and 3 low and average the remaining 6?

I think this formula will work...

=SUMPRODUCT(LARGE(A1:A12,{4,5,6,7,8,9}))/6

Change the range of cells to match the 12 cells you have you data in.

Rick

 

[T. Valko]

I think this formula will work...

=SUMPRODUCT(LARGE(A1:A12,{4,5,6,7,8,9}))/6

Change the range of cells to match the 12 cells you have you data in.

Rick

Average will work:

=AVERAGE(SMALL(A1:A12,{4,5,6,7,8,9}))

Or, the less known:

=TRIMMEAN(A1:A12,6/12)

 

[Rick Rothstein \(MVP - VB\)]

I am needing help; I use spreadsheets entering 6 scores and have to

Average will work:

=AVERAGE(SMALL(A1:A12,{4,5,6,7,8,9}))

Or, the less known:

=TRIMMEAN(A1:A12,6/12)

Although the OP said he will have 12 filled-in cells, I would point out that
if he only had between 9 and 11 filled-in cells, all of the formulas differ
in their results they produce. Yes, they all agree when there are 12
filled-in cells, but it might be important for those reading this thread to
know about the differences lest they leave this thread thinking the three
formulas are equivalent to each other.

Rick

 

[MartinW]

So following on from that this should work as a generic formula in C1.

=TRIMMEAN(INDIRECT("A1:A"&COUNT(A:A)),B1/COUNT(A:A))

With any amount of values in column A
In B1 put the total number of values to be diregarded
e.g.
2 high and 2 low would be B1: 4
3 high and 3 low would be B1: 6
etc.

HTH
Martin