Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match cells in Range 1 with cells in Range 2 | New Users to Excel | |||
Find a range of values in a range of cells | Excel Worksheet Functions | |||
how to compute a range of cells based on another range of cells? | Excel Worksheet Functions | |||
how to compute a range of cells based on another range of cells? | Excel Worksheet Functions | |||
Count cells in one range based on parameters in another range | Excel Worksheet Functions |