Need help with range of cells
 

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!!

Need help with range of cells
 

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

Need help with range of cells
 

"Rick Rothstein (MVP - VB)" wrote in
message ...
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

Average will work:

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

Or, the less known:

=TRIMMEAN(A1:A12,6/12)

--
Biff
Microsoft Excel MVP

Need help with range of cells
 

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

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

Need help with range of cells
 

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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

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