Help with Averaging Numbers within Standard Deviation

L

lawdoggy

I'm trying to average only the numbers that fall within the standard
deviation. Say you have 10 numbers listed and only 7 fall in the range
of the standard deviation of the list. I need the average of only
those number that fit. I've tried several different ways but nothing
is working. Any help would be greatly appreciated!!

Thanks...mitch
 
P

p45cal

lawdoggy;526336 said:
I'm trying to average only the numbers that fall within the standard
deviation. Say you have 10 numbers listed and only 7 fall in the range
of the standard deviation of the list. I need the average of only
those number that fit. I've tried several different ways but nothing
is working. Any help would be greatly appreciated!!

Thanks...mitch

This is for 1 SD either side of the mean, where K7 contained the mean,
K8 the SD, and the sample data was in range F8:F31 :

Code:
 
L

lawdoggy

This is for 1 SD either side of the mean, where K7 contained the mean,
K8 the SD, and the sample data was in range F8:F31 :

Code:
--------------------
    =(SUM(--(IF(F8:F31>(K7-K8),1,0)*IF(F8:F31<(K7+K8),1,0)*F8:F31)))/(COUNTIF(F­8:F31,">=" & K7-K8)-COUNTIF(F8:F31,">" & K7+K8))
--------------------

alternatively, this can all be put in one cell:

Code:

Wow!! no wonder i never figured it out. Thank you. I will mess with it
and see if I can get it to work. Thank you!

mitch
 
P

p45cal

lawdoggy;526880 said:
Deviation - The Code Cage Forums
(http://www.thecodecage.com/forumz/showthread.php?t=144559)

Wow!! no wonder i never figured it out. Thank you. I will mess with it
and see if I can get it to work. Thank you!

mitch

I notice there's no user defined function in the original message abov
which I added to the post later. Go to thecodecage.com thread to see i
- hang on a mo, Ill paste here too:
--- Automerged consecutive post before response ---
or a user defined function which you can use in vba or call from th
worksheet thus:
=SDAverage(F8:F31,1)
where the second parameter is the number of standard deviations eithe
side of the mean to include in the calculation. Happily, it gives th
same result as the maga-formula given earlier:

Function SDAverage(TheRange, NoOfSDs)
TheMean = Application.Average(TheRange.Value)
SD = Application.WorksheetFunction.StDev(TheRange.Value)
For Each cll In TheRange
If cll.Value > TheMean - (SD * NoOfSDs) And cll.Value < TheMean + (S
* NoOfSDs) Then
mySum = mySum + cll.Value
divisor = divisor + 1
End If
Next cll
If divisor > 0 Then SDAverage = mySum / divisor Else SDAverage
"Divide by 0"
End Functio
 
P

Peggy Shepard

Hi Mitch,

Another option -

=(SUM(--(IF(H2:H11>=(AVERAGE(H2:H11)-STDEV(H2:H11)),1,0)*IF(H2:H11<=(AVERAGE(H2:H11)+STDEV(H2:H11)),1,0)*H2:H11)))/(COUNTIF(H2:H11,">="&AVERAGE(H2:H11)-STDEV(H2:H11))-COUNTIF(H2:H11,">="&AVERAGE(H2:H11)+STDEV(H2:H11)))

data is in H2:H11

enter the formula as an array formula - CTRL+SHIFT+ENTER

Peggy

This is for 1 SD either side of the mean, where K7 contained the mean,
K8 the SD, and the sample data was in range F8:F31 :

Code:
--------------------
=(SUM(--(IF(F8:F31>(K7-K8),1,0)*IF(F8:F31<(K7+K8),1,0)*F8:F31)))/(COUNTIF(F­8:F31,">="
& K7-K8)-COUNTIF(F8:F31,">" & K7+K8))
--------------------

alternatively, this can all be put in one cell:

Code:

Wow!! no wonder i never figured it out. Thank you. I will mess with it
and see if I can get it to work. Thank you!

mitch
 
L

lawdoggy

Hi Mitch,

Another option -

=(SUM(--(IF(H2:H11>=(AVERAGE(H2:H11)-STDEV(H2:H11)),1,0)*IF(H2:H11<=(AVERAG­E(H2:H11)+STDEV(H2:H11)),1,0)*H2:H11)))/(COUNTIF(H2:H11,">="&AVERAGE(H2:H11­)-STDEV(H2:H11))-COUNTIF(H2:H11,">="&AVERAGE(H2:H11)+STDEV(H2:H11)))

data is in H2:H11

enter the formula as an array formula - CTRL+SHIFT+ENTER

Peggy








Wow!! no wonder i never figured it out. Thank you. I will mess with it
and see if I can get it to work. Thank you!

mitch- Hide quoted text -

- Show quoted text -

The code works great Thank you!!! You guys rock!

mitch
 
B

Bernd P

Hello,

Array-enter:
=AVERAGE(IF(ABS(AVERAGE(A1:A10)-A1:A10)<=ABS(ABS(AVERAGE(A1:A10))-STDEV
(A1:A10)),A1:A10))

Regards,
Bernd
 
B

barry houdini

Wouldn't this be sufficient?

=AVERAGE(IF(ABS(A1:A10-AVERAGE(A1:A10))<=STDEV(A1:A10),A1:A10))

confirmed with CTRL+SHIFT+ENTE
 
T

Tushar Mehta

You can do this w/o VBA and with a (relatively) simpler formula.

Suppose your data are in A1:A13, the average is in D1 and the std.
dev. in E1. Then, use the array formula
=AVERAGE(IF(ABS(A1:A13-$D$1)<=$E$1,A1:A13))

To enter an array formula complete data entry not with the combination
of SHIFT+CTRL+ENTER keys and not just the ENTER or TAB key. If done
correctly, *Excel* will show the formula enclosed in curly brackets {
and }

I'm trying to average only the numbers that fall within the standard
deviation. Say you have 10 numbers listed and only 7 fall in the range
of the standard deviation of the list. I need the average of only
those number that fit. I've tried several different ways but nothing
is working. Any help would be greatly appreciated!!

Thanks...mitch
Regards,

Tushar Mehta
Microsoft MVP Excel 2000-present
www.tushar-mehta.com
Excel and PowerPoint tutorials and add-ins
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top