Need help with range of cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

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!!
 
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?
Click to expand...

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
 
Rick Rothstein (MVP - VB) said:
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
Click to expand...

Average will work:

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

Or, the less known:

=TRIMMEAN(A1:A12,6/12)
 
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)
Click to expand...

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
 
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
 
Back
Top