Average a column minus highs and lows

[Walter Steadman]

I have a spreadsheet with about 30 worksheets and each worksheet has about
200 columns of information and I need to sum each column minus the high 3
and low 3 figures. Some columns have hundreds of rows of data and I was
wondering if there was some programming or code I could put at the bottom of
each column to help me easily figure out this number. For example the
column below the high is 1000 and the low is 35 so I want to sum this column
but do not want to include the 1000 or the 35. Any assistance would be
apprecaited. This is an old form from my wifes work and she has to have the
calculations done by the end of the weekend so I want to try to make it
faster for her so we can enjoy our time off together. TIA

Wally Steadman

408

152

287

745

342

245

556

282

654

537

235

433

357

249

144

389

498

1000

350

401

516

255

345

251

320

343

570

339

35

540

189

175

119

532

515

271

350

90

426

132

227

307

 

[kkknie]

Here's the formula you need to use. I will work on creating some cod
to add it to the bottom of every column. Unless of course, you coul
add it at the top (which would make life much easier since it woul
just involve dragging it across each column).

=(SUM(A2:A11)-SMALL(A2:A11,1)-SMALL(A2:A11,2)-SMALL(A2:A11,3)-LARGE(A2:A11,1)-LARGE(A2:A11,2)-LARGE(A2:A11,3))/(COUNT(A2:A11)-6)

Note that the Count() function only counts non-blank cells.

Let me know what you think.

 

[Bob Phillips]

Hi Wally,

Here is an example on column F

=SUM(F:F)-MAX(F:F)-MIN(F:F)

what happens if the max/min is repeated.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[kkknie]

Sadly, Max/Min will not work since it will retrieve the same max and mi
each time. The large(Range, 1) is the same as Max and th
Small(Range,1) is the same as Min. I read in the initial post that th
OP wanted to ignore the highest 3 and lowest 3.

Here is my code-based solution. Put it in a module or in the workboo
section of the VB Editor. It assumes the following:

1. All ranges start on the first row.
2. All sheets are to be searched.
3. You want the formula below the last value in the range.
4. The result will be bold.


Code
-------------------
Sub DoColsAvgs()

Dim s
Dim iCols As Integer
Dim iMaxRow As Long
Dim strFirst As String
Dim strLast As String
Dim strRange As String
Dim strFormula As String
Dim j As Integer

For Each s In Sheets

s.Select
iCols = s.UsedRange.CurrentRegion.Columns.Count
For j = 1 To iCols
iMaxRow = Range(Cells(65000, j), Cells(65000, j)).End(xlUp).Row
strFirst = Replace(Range(Cells(1, j), Cells(1, j)).Address, "$", "")
strLast = Replace(Range(Cells(iMaxRow, j), Cells(iMaxRow, j)).Address, "$", "")
strRange = strFirst & ":" & strLast
Debug.Print strRange
strFormula = "=(SUM(" & strRange & ")-SMALL(" & strRange & _
",1)-SMALL(" & strRange & ",2)-SMALL(" & strRange & _
",3)-LARGE(" & strRange & ",1)-LARGE(" & strRange & _
",2)-LARGE(" & strRange & ",3))/(COUNT(" & strRange & ")-6)"
Range(Cells(iMaxRow + 1, j), Cells(iMaxRow + 1, j)).Value = strFormula
Range(Cells(iMaxRow + 1, j), Cells(iMaxRow + 1, j)).Font.Bold = True
Next
Next

End Su
-------------------

If the assumptions are not true (as they are probably not) some change
need to be made. Please try this code on a COPY of your original so a
not to screw anything up...

Some assumption fixes.
1. If the data starts on another row (say 2), change the 1 to a 2 i
this line:
strFirst = Replace(Range(Cells(*1*, j), Cells(*1*, j)).Address, "$"
"")
2. If every sheet doesn't need to be searched, you must add an if/the
in the loop through the sheets. Example:


Code
-------------------
For Each s In Sheets
If s.Name <> "SomeSheet" and s.Name <> "AnotherSheet" Then
'All other code here
End If
Nex

 

[Tom Ogilvy]

Sadly it is true that Walter did say to ignore the high 3 and low 3 (in the
subject and in the body). Sadder still is I never saw anywhere in the body
of the message where he said he wanted the average, only the sum, yet he
cites the average in the subject. Then in the example he cites, he
contradicts his request by talking only about a single high and low - so I
think Walter needs to restate exactly what he needs to do (or perhaps he can
adapt your generous code to do what he needs).

 

[Bob Phillips]

Exactly, I just worked on the example.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Sadly it is true that Walter did say to ignore the high 3 and low 3 (in the
subject and in the body). Sadder still is I never saw anywhere in the body
of the message where he said he wanted the average, only the sum, yet he
cites the average in the subject. Then in the example he cites, he
contradicts his request by talking only about a single high and low - so I
think Walter needs to restate exactly what he needs to do (or perhaps he can
adapt your generous code to do what he needs).

 

[Walter Steadman]

Tom,
You are correct in my contradiction. Not intentionally stated, There
will be a cell that will calculate (SUM as I can average based on the result
of the sum) the column, minus the high 3 and low 3 (this number will change
for some of the columns, but that is easily adaptable I think. Have looked
at the code in these posts and it definitely gives me something to work
with. Sorry for the contradiction. Also about the number 3 vs. 1 that was
again an accident and poor choice of numbering for the sake of the board.
My apologies and I do appreciate all the help on this one.

Wally

Sadly it is true that Walter did say to ignore the high 3 and low 3 (in the
subject and in the body). Sadder still is I never saw anywhere in the body
of the message where he said he wanted the average, only the sum, yet he
cites the average in the subject. Then in the example he cites, he
contradicts his request by talking only about a single high and low - so I
think Walter needs to restate exactly what he needs to do (or perhaps he can
adapt your generous code to do what he needs).

 

[Vasant Nanavati]

I'm a bit late to this but have you looked at the TRIMMEAN function?

--

Vasant

Tom,
You are correct in my contradiction. Not intentionally stated, There
will be a cell that will calculate (SUM as I can average based on the result
of the sum) the column, minus the high 3 and low 3 (this number will change
for some of the columns, but that is easily adaptable I think. Have looked
at the code in these posts and it definitely gives me something to work
with. Sorry for the contradiction. Also about the number 3 vs. 1 that was
again an accident and poor choice of numbering for the sake of the board.
My apologies and I do appreciate all the help on this one.

Wally