Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating averages excluding outliers...a question
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... -- stew1901 ------------------------------------------------------------------------ stew1901's Profile: http://www.excelforum.com/member.php...o&userid=29164 View this thread: http://www.excelforum.com/showthread...hreadid=488881 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating averages excluding outliers...a question
Should add, the condition to exclude points will be a simple and < than condition. -- stew1901 ------------------------------------------------------------------------ stew1901's Profile: http://www.excelforum.com/member.php...o&userid=29164 View this thread: http://www.excelforum.com/showthread...hreadid=488881 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating averages excluding outliers...a question
"stew1901" wrote in
message ... Should add, the condition to exclude points will be a simple and < than condition. 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating averages excluding outliers...a question
=AVERAGE(IF(DataMin,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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Calculating averages excluding outliers...a question
On Mon, 28 Nov 2005 16:08:11 -0600, stew1901
wrote: 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... Take a look at the TRIMMEAN worksheet function. (In the HELP bar just type TRIMMEAN worksheet function). --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating basic averages...a question | Excel Worksheet Functions | |||
Averages excluding certian data | Excel Discussion (Misc queries) | |||
calculating averages | Excel Discussion (Misc queries) | |||
creating intervals and calculating averages | Excel Discussion (Misc queries) | |||
calculating averages | Excel Worksheet Functions |