Calculating averages excluding outliers...a question

S

stew1901

Hi all

First post! I hope this is the right section!

I need to calculate a simple average (arithmetic and median) of a set
of numbers, but excluding certain outlying points. I'm not by any
means a newbie to coding so I do know how I'd do this generally in a
programming language or "pseudo code" (so no need to waste your time on
long explanations!) but I am completely new to Excel so have no idea of
the syntax and don't really have the time to mess around with the
"guess the useful phrase" help function.

Thanks in advance...I hope...
 
S

stew1901

Should add, the condition to exclude points will be a simple > and <
than condition.
 
B

Bruno Campanini

stew1901 said:
Should add, the condition to exclude points will be a simple > and <
than condition.
Click to expand...

Try this routine.
You must define in Set SourceRange your Sheet and your
range's starting cell (I have assumed there are no blank cells
in the range).
I've used as helper column the one to the right of your range.
If it is a problem an array (i.e. RAM memory) can be used instead.
List your condition to exclude in place of my example list
under If Not ( _
Let me know if it works.
===============================
Public Sub ConditionalAverage()
Dim SourceRange As Range, i, j As Long
Dim Total As Double, M1 As Double, Median As Double

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Set SourceRange = [Sheet10!BS6]
If Not IsEmpty(SourceRange(2, 1)) Then
Set SourceRange = SourceRange.Resize(SourceRange. _
End(xlDown).Row - SourceRange.Row + 1)
End If

For Each i In SourceRange
If Not ( _
(i >= 10 And i <= 22) Or _
(i >= 125 And i <= 150) Or _
(i >= 250 And i <= 300) Or _
(i > 1000) _
) Then
Total = Total + i
j = j + 1
SourceRange(j, 2) = i
End If
Next

M1 = Total / j
If j Mod 2 Then
Median = SourceRange(Int(j / 2) + 1, 2)
Else
Median = (SourceRange(j / 2, 2) + SourceRange(j / 2 + 1, 2)) / 2
End If

If j Then
MsgBox "M1 = " & Total / j & vbCrLf & "Me = " & Median
End If
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
==========================

Ciao
Bruno
 
D

DOR

=AVERAGE(IF(Data>Min,IF(Data<Max,Data,"")))

entered as an array formula with Ctl-Shift-Enter instead of Enter
alone,

calculates the average, and there may be no useful phrase that would
take you to the appropriate Help, because it is probably not there.

Data is a named range containing the values, Max and Min are your
outlier limits

Use <= and >= if your Max and Min are ceilings and should be included.

Substitute MEDIAN for AVERAGE to get median.

HTH

DOR
 
R

Ron Rosenfeld

Hi all

First post! I hope this is the right section!

I need to calculate a simple average (arithmetic and median) of a set
of numbers, but excluding certain outlying points. I'm not by any
means a newbie to coding so I do know how I'd do this generally in a
programming language or "pseudo code" (so no need to waste your time on
long explanations!) but I am completely new to Excel so have no idea of
the syntax and don't really have the time to mess around with the
"guess the useful phrase" help function.

Thanks in advance...I hope...
Click to expand...


Take a look at the TRIMMEAN worksheet function. (In the HELP bar just type
TRIMMEAN worksheet function).


--ron
 
Top